Forum Discussion

Lorenzo Kim's avatar
Lorenzo Kim
Bronze Contributor
Jul 14, 2018
Solved

runtime error 1004 : Method range of object - 'Global' failed

How do I correct this? (kindly see images) many many thanks    
excel
Macros and VBA
  • Matt Mickle's avatar
    Matt Mickle
    Jul 17, 2018

    You can drop the WorksheetFunction portion for easier readability:

    New_text = Application.Substitute(Cell_reference,"TOTAL","")
Man Fai Chan's avatar
Man Fai Chan
Iron Contributor
Jul 15, 2018

Dear Kim,

 

The statement is as follows:

 

New_text = Application.WorksheetFunction.Substitute(Cell_reference,"TOTAL","")

 

Cheers,

Chan M F

Lorenzo Kim's avatar
Lorenzo Kim
Bronze Contributor
Jul 15, 2018

Mr. Chan

Thank you for your reply.

I think I have solved the problem with the SUB below.

I will still try your suggestion though.

Thank you very very for your time and unwavering assistance.

more power to you and GOOD HEALTH!

 

Sub RemoveWordTotal()
 
 Application.ScreenUpdating = False
 Dim i As Long
 For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
 mcel = Cells(i, "B")
 Cells(i, "B") = Left(Cells(i, "B"), Len(mcel) - 5)
 Next i
 Application.ScreenUpdating = True

End Sub

 

Resources