Help with instring code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a line of code that sees if a subtotal contains the text "00 total".
However, some of the totals can be "01 total", "04 total". All totals have 0
as their second last number but not as their last number. Is there an easy
way to correct the following code to take care of this discrepancy?

If InStr(1, LCase(rngCell.Value), "00 total") > 0 And _
InStr(1, LCase(rngCell.Offset(1, -2).Value), "total") = 0 Then
rngCell.Offset(2, 0).FormulaR1C1 = "=R[3]C[1]"

--Elaine
 
Elaine,

The Like operator can do what you ask, so just changing the first line...

'--------------------------
If rngCell.Value Like "*0# total*" And _
InStr(1, LCase(rngCell.Offset(1, -2).Value), "total") = 0 Then
rngCell.Offset(2, 0).FormulaR1C1 = "=R[3]C[1]"
End If
'--------------------------

Regards,
Jim Cone
San Francisco, USA
 
Jim:

I tried it but it did not work. I am not sure what it is that I am doing
incorrectly. I replaced your line with the one that I had and now it looks
just the way you have it. However, nothing happens -- the code doesn't seem
to find anything -- including the 00 totals! Could you please help again?


Jim Cone said:
Elaine,

The Like operator can do what you ask, so just changing the first line...

'--------------------------
If rngCell.Value Like "*0# total*" And _
InStr(1, LCase(rngCell.Offset(1, -2).Value), "total") = 0 Then
rngCell.Offset(2, 0).FormulaR1C1 = "=R[3]C[1]"
End If
'--------------------------

Regards,
Jim Cone
San Francisco, USA

Elaine said:
I have a line of code that sees if a subtotal contains the text "00 total".
However, some of the totals can be "01 total", "04 total". All totals have 0
as their second last number but not as their last number. Is there an easy
way to correct the following code to take care of this discrepancy?

If InStr(1, LCase(rngCell.Value), "00 total") > 0 And _
InStr(1, LCase(rngCell.Offset(1, -2).Value), "total") = 0 Then
rngCell.Offset(2, 0).FormulaR1C1 = "=R[3]C[1]"

--Elaine
Click to expand...
Click to expand...
 
Elaine,

First thing is to do is remove the "End If", line 4 of my response.
It should not be there. Only the first line should have
been changed.

If that wasn't the problem then have you changed anything else...

Does the code have " _" at the end of the first line?
Is the code still in the same module?
Do you have an "Option Compare Text" statement at the top of the module?
Are you testing the same data?
Can you determine if it is the "Like" operator or is it the "Instr" function that
is failing? ( by stepping thru the code)

Regards,
Jim Cone


Elaine said:
Jim:

I tried it but it did not work. I am not sure what it is that I am doing
incorrectly. I replaced your line with the one that I had and now it looks
just the way you have it. However, nothing happens -- the code doesn't seem
to find anything -- including the 00 totals! Could you please help again?
Click to expand...
Jim Cone said:
Elaine,
The Like operator can do what you ask, so just changing the first line...
'--------------------------
If rngCell.Value Like "*0# total*" And _
InStr(1, LCase(rngCell.Offset(1, -2).Value), "total") = 0 Then
rngCell.Offset(2, 0).FormulaR1C1 = "=R[3]C[1]"
End If
'--------------------------
Regards,
Jim Cone
San Francisco, USA
Click to expand...

I have a line of code that sees if a subtotal contains the text "00 total".
However, some of the totals can be "01 total", "04 total". All totals have 0
as their second last number but not as their last number. Is there an easy
way to correct the following code to take care of this discrepancy?
If InStr(1, LCase(rngCell.Value), "00 total") > 0 And _
InStr(1, LCase(rngCell.Offset(1, -2).Value), "total") = 0 Then
rngCell.Offset(2, 0).FormulaR1C1 = "=R[3]C[1]"
--Elaine
Click to expand...
Click to expand...
Click to expand...
 
If InStr(1, Application.Trim(rngCell.Value), "00 total",vbTextCompare) >
0 And _
InStr(1, rngCell.Offset(1, -2).Value, "total",vbTextCompare) = 0
Then
rngCell.Offset(2, 0).FormulaR1C1 = "=R[3]C[1]"


Also, if this data is brought down from the web, the space may not be a
space. It may be a non-breaking space. Is this data brought down from the
web?
 
Tom and Jim thank you very much for your replies. I did not declare Option
Compare Text in the top of my module. Thank you for your very specific and
generous help.
 
Declaring Option Compare Text
shouldn't make a difference in your original code since you converted it to
lowercase in passing it to instr. and total was expressed as lowercase. In
my example, I did away with the Lcase and use vbCompareText as an argument
to instr, so it isn't required there either.

--
Regards,
Tom Ogilvy





Elaine said:
Tom and Jim thank you very much for your replies. I did not declare Option
Compare Text in the top of my module. Thank you for your very specific and
generous help.

Elaine said:
I have a line of code that sees if a subtotal contains the text "00 total".
However, some of the totals can be "01 total", "04 total". All totals have 0
as their second last number but not as their last number. Is there an easy
way to correct the following code to take care of this discrepancy?

If InStr(1, LCase(rngCell.Value), "00 total") > 0 And _
InStr(1, LCase(rngCell.Offset(1, -2).Value), "total") = 0 Then
rngCell.Offset(2, 0).FormulaR1C1 = "=R[3]C[1]"

--Elaine
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loop thru xx No. of sheets until the four strValue combo's are found 6
Find Total but not Grand Total 2
Insert Rows after word total 5
Modify The Code For Importing Text File into an Excel File 2
Shade rows A:J 8
Subtotal for same range of multiple worksheets 5
Clear corresponding cells in other worksheet 4
Easier way? 4

Back
Top