Find Total but not Grand Total

[Indu Aronson]

I have the following code that people in this newsgroup
have helped me out with:

Dim rngCell As Range
Dim lngCounter As Long
For lngCounter = 1337 To 1 Step -1
Set rngCell = ActiveSheet.Cells(lngCounter, 7)
If InStr(1, LCase(rngCell.Value), "total") > 0 And _
InStr(1, LCase(rngCell.Offset(1, -2).Value), "total") = 0
Then
rngCell.FormulaR1C1 = "=R[-1]C[1]&"" Total"""
End If
Next

I was wondering if someone could please help me by telling
how I could NOT apply the above if the cell is grand
total. If Tom Total insert the formula. If Phil Total
insert the formula but if the words "Grand Total" are
found in the cell don't do anything.

Thank you very much!

--Indu

 

[Frank Kabel]

Hi
change the line
If InStr(1, LCase(rngCell.Value), "total") > 0 And _
InStr(1, LCase(rngCell.Offset(1, -2).Value), "total") = 0 then

to
If InStr(1, LCase(rngCell.Value), "total") > 0 And _
InStr(1, LCase(rngCell.Offset(1, -2).Value), "total") = 0 and _
InStr(1, LCase(rngCell.Value), "grand total") = 0 then

 

[Indu]

Thanks for your help. However, I get a 0 (due to the
formula) two rows below grand total. Is there any way to
get rid of this?

-----Original Message-----
Hi
change the line
If InStr(1, LCase(rngCell.Value), "total") > 0 And _
InStr(1, LCase(rngCell.Offset(1, -2).Value), "total") = 0 then

to
If InStr(1, LCase(rngCell.Value), "total") > 0 And _
InStr(1, LCase(rngCell.Offset(1, -2).Value), "total") = 0 and _
InStr(1, LCase(rngCell.Value), "grand total") = 0 then


--
Regards
Frank Kabel
Frankfurt, Germany

I have the following code that people in this newsgroup
have helped me out with:

Dim rngCell As Range
Dim lngCounter As Long
For lngCounter = 1337 To 1 Step -1
Set rngCell = ActiveSheet.Cells(lngCounter, 7)
If InStr(1, LCase(rngCell.Value), "total") > 0 And _
InStr(1, LCase(rngCell.Offset(1, -2).Value), "total") = 0
Then
rngCell.FormulaR1C1 = "=R[-1]C[1]&"" Total"""
End If
Next

I was wondering if someone could please help me by telling
how I could NOT apply the above if the cell is grand
total. If Tom Total insert the formula. If Phil Total
insert the formula but if the words "Grand Total" are
found in the cell don't do anything.

Thank you very much!

--Indu

.