SOLVED

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

Bronze Contributor

How do I correct this?

(kindly see images)

many many thanks

 

 

global error.PNGglobal-error.PNG

17 Replies

Hi Lorenzo,

 

The Cells function requires two arguments: RowIndex & ColumnIndex

Both must be a number, not a text!

You put the text "C" in the ColumnIndex, so try to replace it with 3 instead.

 

VBA Cells method.png

 

Regards

I suspect the LastRow - 1 gives zero or negative number.

Mr Chan

I think it is -- the lastrow was 0!

Thank you for your reply...

Mr. Amairah
Thank you for your time...

This is a good example of when it pays to debug the code using F8.  You can step through the code one line at a time and then hover over the variable in order to get it's value.....or you van use a watch which you can see in the immediate window or perhaps even type in a statement into the immediate window....

 

This will save you a ton of time in the long run....

 

Watch Window

https://www.youtube.com/watch?v=19JTjCtFPeI

 

Immediate Window

https://www.excelcampus.com/vba/vba-immediate-window-excel/

Mr. Mickle
thank you for the tip..

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

the word "Total" is in the rightmost part of the cell
ex: "Customer ABC Total"; "Item 123 Total" etc..

Dear Kim,

 

I heard a worksheet function: "SUBSTITUTE" which can perform your task:
          SUBSTITUE(A1,"TOTAL","")

 

Cheers 

Chan M F

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

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

 

best response confirmed by Lorenzo Kim (Bronze Contributor)
Solution

You can drop the WorksheetFunction portion for easier readability:

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

Mr. Mickle

Thank you for the new tip.  A new horizon....

the final SUB would be below. ( It worked nicely )

(it is case sensitive)

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
Cells(i, "B") = Application.Substitute(Cells(i, "B"),"Total","")
Next i
Application.ScreenUpdating = True
End Sub

The above can also be used to replace a text instead of just blank.
instead of "" - replace it with "New Text" !!
VERY VERSATILE CODE, Mr. Mickle..


FOR OTHERS WHO MIGHT FIND THIS USEFUL:
maybe called as function (BELOW) > Call ReplaceSomeText("Total", "B", "")
"Total" (mWRD) = the word within the cell to be replaced
"B" (mcol) = the column referenced
"" (newTXT) = blank or any text to replace mWRD

Sub ReplaceSomeText(mWRD As String, mcol As String, newTXT As String)
Application.ScreenUpdating = False
Dim i As Long
For i = Cells(Rows.Count, mcol).End(xlUp).Row To 2 Step -1
Cells(i, mcol) = Application.Substitute(Cells(i, mcol),mWRD, newTXT)
Next i
Application.ScreenUpdating = True
End Sub

 

yes, this is what Mr. Mickle just suggested!

Mr. Chan

All along you are suggesting what Mr. Mickle had just recommended..

sorry If I didn't "see" it earlier.

many many thanks

for me the msg error appeas at the time I am calling F10 key

' RUN_FORREST_RUN Macro
'

'
ChDir "Y:\Luciano"
ActiveWorkbook.SaveAs Filename:= _
"Y:\Luciano\Segment Report Conso Excel Link.xlsb", FileFormat:=xlExcel12, _
CreateBackup:=False


Sheets("CoA").Select
Range("CoA[[#Headers],[ACCOUNT NUMBER]]").Select
Application.Run Range("cc.F10")
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
1 best response

Accepted Solutions
best response confirmed by Lorenzo Kim (Bronze Contributor)
Solution

You can drop the WorksheetFunction portion for easier readability:

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

View solution in original post