Jump to content

How to replace all blank cells to zero


Recommended Posts

Hey All,

 

I have an excel worksheet, about A1:BD1500, and trying to replace all blank cells to zero, but following code doesn't work. What did i do wrong?

Local $oRange = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeBlanks)
_Excel_RangeReplace($oWorkbook, $oWorkbook.ActiveSheet, $oRange, "", "0")
ConsoleWrite($oRange & @CRLF)

the console writes nothing about $oRange, which I believe the first line code doesn't find the cells. Any ideas? Thank you for your help!

Link to post
Share on other sites

A range is an object that can not be displayed using ConsoleWrite.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2021-07-20 - Version 1.0.0.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites

What is the value of @error and @extended after you called _Excel_RangeReplace?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2021-07-20 - Version 1.0.0.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites
12 minutes ago, water said:

What is the value of @error and @extended after you called _Excel_RangeReplace?

it actually has no error, its says successfully replace. but actually nothing is replaced for those blank cells.

and this is my error code

If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace", "Error replacing data the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace", "Data successfully replaced in cells.")

 

Link to post
Share on other sites

I assume that the UsedRange property returns an area, not a range. An area is a collection of ranges.
Could you try:

Local $oArea = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeBlanks)
For $oRange in $oArea
    _Excel_RangeReplace($oWorkbook, $oWorkbook.ActiveSheet, $oRange, "", "0")
Next

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2021-07-20 - Version 1.0.0.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites
1 hour ago, water said:

I assume that the UsedRange property returns an area, not a range. An area is a collection of ranges.
Could you try:

Local $oArea = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeBlanks)
For $oRange in $oArea
    _Excel_RangeReplace($oWorkbook, $oWorkbook.ActiveSheet, $oRange, "", "0")
Next

 

Hey Water, thank you so much. I tried this code, and add the error script just before Next

If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace", "Error replacing data the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace", "Data successfully replaced in cells.")

the error msg is 3: $sSearch is empty. I am not sure what should I do about this. Thank you so much for helping me

Link to post
Share on other sites
23 hours ago, junkew said:

In excel VBA it would be like this
Range("A1:BD1500").SpecialCells(xlCellTypeBlanks).value=0

This would then become in AutoIt with excel udf

$oWorkbook.ActiveSheet.Range("A1:BD1500").SpecialCells($xlCellTypeBlanks).value=0

 

This code works! thank you so much Junkew!

Link to post
Share on other sites
13 hours ago, Juvigy said:

You can use:

$oExcel.Application.ActiveWorkbook.Sheets(1).Range("E5:J20").Select
$oExcel.Application.Selection.Replace("", 0)

Your codes works too! Thank you so much Juvigy!

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...