Forum Discussion
Solved
runtime error 1004 : Method range of object - 'Global' failed
How do I correct this? (kindly see images) many many thanks
You can drop the WorksheetFunction portion for easier readability:
New_text = Application.Substitute(Cell_reference,"TOTAL","")
Mr. Chan
Here I am again, pardon for the bother..
kindly help with the small snippet below
I wish to erase in a range of column the word "Total" in a cell.
in EXCEL it is =left(cellText, len(cellText)-5)
How do I write it in vba?
many many many thanks
Sub RemoveWordTotal()
Application.ScreenUpdating = False
Dim i As Long
For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
' remove the word total inside a column range cell
Next i
Application.ScreenUpdating = True
End Sub
Dear Kim,
I heard a worksheet function: "SUBSTITUTE" which can perform your task:
SUBSTITUE(A1,"TOTAL","")
Cheers
Chan M F
- yes, this is what Mr. Mickle just suggested!
Mr. Chan
thank you for your prompt reply.
how do I write in the SUB the formula below?
many many thanks
Sub RemoveWordTotal()
Application.ScreenUpdating = False
Dim i As Long
For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
' remove the word total inside a column range cell
substitute(cell reference,"Total","") *** How do I write this code
Next i
Application.ScreenUpdating = True
End Sub
Dear Kim,
The statement is as follows:
New_text = Application.WorksheetFunction.Substitute(Cell_reference,"TOTAL","")
Cheers,
Chan M F
You can drop the WorksheetFunction portion for easier readability:
New_text = Application.Substitute(Cell_reference,"TOTAL","")
