Jul 13 2018 09:15 PM
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.
Regards
Jul 13 2018 09:30 PM
I suspect the LastRow - 1 gives zero or negative number.
Jul 13 2018 09:43 PM
Mr Chan
I think it is -- the lastrow was 0!
Thank you for your reply...
Jul 13 2018 09:44 PM
Jul 13 2018 11:02 PM
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
Jul 13 2018 11:23 PM
Jul 14 2018 04:33 PM
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
Jul 14 2018 05:10 PM
Jul 14 2018 05:11 PM
Dear Kim,
I heard a worksheet function: "SUBSTITUTE" which can perform your task:
SUBSTITUE(A1,"TOTAL","")
Cheers
Chan M F
Jul 14 2018 05:17 PM
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
Jul 14 2018 05:21 PM
Dear Kim,
The statement is as follows:
New_text = Application.WorksheetFunction.Substitute(Cell_reference,"TOTAL","")
Cheers,
Chan M F
Jul 14 2018 05:26 PM
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
Jul 17 2018 08:44 AM - edited Jul 17 2018 08:44 AM
SolutionYou can drop the WorksheetFunction portion for easier readability:
New_text = Application.Substitute(Cell_reference,"TOTAL","")
Jul 17 2018 04:35 PM
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
Jul 17 2018 04:39 PM
Jul 17 2018 04:41 PM
Mr. Chan
All along you are suggesting what Mr. Mickle had just recommended..
sorry If I didn't "see" it earlier.
many many thanks
Aug 07 2022 08:09 AM
Jul 17 2018 08:44 AM - edited Jul 17 2018 08:44 AM
SolutionYou can drop the WorksheetFunction portion for easier readability:
New_text = Application.Substitute(Cell_reference,"TOTAL","")