CC_Mu Posted May 7, 2018 Posted May 7, 2018 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!
water Posted May 7, 2018 Posted May 7, 2018 A range is an object that can not be displayed using ConsoleWrite. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.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 PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
water Posted May 7, 2018 Posted May 7, 2018 What is the value of @error and @extended after you called _Excel_RangeReplace? My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.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 PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
CC_Mu Posted May 7, 2018 Author Posted May 7, 2018 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.")
water Posted May 8, 2018 Posted May 8, 2018 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.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 PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
CC_Mu Posted May 8, 2018 Author Posted May 8, 2018 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
junkew Posted May 8, 2018 Posted May 8, 2018 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 FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets
Juvigy Posted May 9, 2018 Posted May 9, 2018 You can use: $oExcel.Application.ActiveWorkbook.Sheets(1).Range("E5:J20").Select $oExcel.Application.Selection.Replace("", 0)
CC_Mu Posted May 9, 2018 Author Posted May 9, 2018 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!
CC_Mu Posted May 9, 2018 Author Posted May 9, 2018 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!
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now