Excel Replace not working

[stanl]

Wanted to quickly replace all cells in activesheet that contain negatives with 0.  This macro works:

Code Select Expand


Sub minus()
Set oWS = ActiveSheet
oWS.UsedRange.Select
Selection.Replace What:="-*", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub



when modified for WB I get OLE error

Code (WINBATCH) Select Expand


xlPart = 2
xlByRows = 1

...open any workbook with negative numbers, set oWS to a worksheet
oWS.UsedRange.Select()
oXL.Selection.Replace(::What="-*",Replacement="0",LookAt=xlPart,SearchOrder=xlByRows,MatchCase=@False,SearchFormat=@False,ReplaceFormat=@False)   


What am I missing? I purposely set the macro to be as close to possible as the WB code.

[DAG_P6]

What's with the double colon after the word Replace?

[stanl]

What's with the double colon after the word Replace?

WB's way of handling positional parameters. Start with :: and remove the single : in each parm. At least that is how I have used positional parms in the past.

[stanl]

....really needed this for work, WTF: this worked
oWS.Cells.Replace(::What="-*",Replacement="0")

seems WB had issues with extended parameters (in this case) while the macro did not.

[Deana]

Stan,

In your last post you mentioned that this worked:

oWS.Cells.Replace(::What="-*",Replacement="0")

. That code seems to use the Cells object as opposed to the Selection object. WinBatch should be able to specify all of the other positional parameters. Maybe give this a try:

Code (winbatch) Select Expand


xlPart = 2
xlByRows = 1

...open any workbook with negative numbers, set oWS to a worksheet
oWS.Cells.Replace(::What="-*",Replacement="0", LookAt=xlPart, SearchOrder=xlByRows, MatchCase=@False, SearchFormat=@False, ReplaceFormat=@False)


If you still get an error. Simply add DebugTrace(@on,"trace.txt") to the beginning of the script and inside any UDF, run it until the error or completion, then inspect the resulting trace file for clues as to the problem. Feel free to post the trace file here ( removing any private info) if you need further assistance.

[stanl]

Deana;

I posted that with about 5 minutes left to head out to work. Either Cells or Used Range work, neither work with extended parms. DebugTrace doesn't help, it is a generic OLE error variable/assignment.

In the past I have successfully called multiple parms with Excel's add(), move() and pivot functions. This is work-related and I cannot share the data, but I also tested on a quick worksheet I put together with some negative numbers. The code failed with multiple parms, but succeeded with my fix. I also tried using numerics for xlbyrows,xlpart and @False - same outcome.

[Deana]

Stan,

There is a code sample posted in the tech database, that uses those named parameters :
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel+Excel~Replace~String.txt

Apparently the SearchFormat and ReplaceFormat that are passed to the Replace method, want object handles returned from the FindFormat and ReplaceFormat properties.

[stanl]

Stan,

There is a code sample posted in the tech database, that uses those named parameters :

I missed that, I searched for the work 'Replace'... should have searched for the keyword. Otherwise, using the default as I did works fine as the Excel data is merely an ASCII import with no special formatting, so I assume the defaults are fine.

Thanks for citing the Tech DB reference.