Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with instring code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with instring code
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with instring code
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" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with instring code
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with instring code
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? -- Regards, Tom Ogilvy "Elaine" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with instring code
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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with instring code
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" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
option buttons run Click code when value is changed via VBA code | Excel Programming | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |