vba function Replace function not working

user_15965407 0
Viewed 40

i have problem with .Replace() function in VBA, it doesnt give me any error but it doesnt do the job. Here is the code:

MsgBox Wb3.Name    
Wb3.Worksheets(1).Columns("B").Replace _
            What:="-", Replacement:="", _
            SearchOrder:=xlByColumns, MatchCase:=True

Here is table in which function needs to remove "-" before values

enter image description here

Am i doing something wrong?

WORKING SOLUTION:

I was missing "LookAt:=xlPart"

Wb3.Worksheets(1).Columns("B").Replace _
            What:="-", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByColumns, MatchCase:=True
vba excel
2 Answers

Abs a Range

' Reference the column.
With Wb3.Worksheets(1).Columns("B")
    ' Reference the range (exclude header) using 'Resize', 'End' and 'Offset'.
    With .Resize(.Cells(.Cells.Count).End(xlUp).Row - 1).Offset(1)
        ' 'Abs' the range using 'Worksheet.Evaluate'
        .Value = .Worksheet.Evaluate("ABS(" & .Address & ")")
    End With
End With

Observations

  • The execution time for a sample of 100k rows was 0.35s while for a sample of 500k rows it was 1.2s not dependent on how many conversions took place, making it largely dependent on the number of processed cells.
  • On the other hand, the replace solution (.Replace What:="-", Replacement:="", LookAt:=xlPart (the remaining parameters are irrelevant) instead of .Value = ...) took 4.7s for 100k and 28s for 500k rows when all cells needed to be replaced and only 0.17s and 0,87s respectively when no replacement took place, additionally making the execution time largely dependent on the number of replacements.
user_98140690

After adding LookAt:=xlPart it worked, so this is final code:

Wb3.Worksheets(1).Columns("B").Replace _
            What:="-", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByColumns, MatchCase:=True
user_159654070
Related