Using "Like"

  • Thread starter Thread starter Ben Martens
  • Start date Start date
B

Ben Martens

I have a bunch of spreadsheets with expense data on it and
wrote some code to go through cells and find expenses that
are salary related. The code worked fine in the original
module I created it in but when I copy/paste the code into
another module in another file and run it, it doesn't work
anymore. I can not figure out why the same code won't work
in any other files/modules, it just doesn't make any sense
to me. I know I can simply just run the code in the
original file on all the spreadsheets I need to do this on
but, for convience sake I would like to just use the same
code in other modules/files. Not to mention that I simply
want to understand why this won't work when I copy/paste
the code into another module/file.

Below is the code I wrote:

Private Sub AddCode()
Dim liRow As Integer, myTest

liRow = 9
Do
If Range("CA" & liRow).Value <> Empty Then
myTest = Range("CB" & liRow).Value
Like "*salaries*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CB" & liRow).Value Like "*salary*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CD" & liRow).Value
Like "*salaries*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CD" & liRow).Value Like "*salary*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CF" & liRow).Value
Like "*salaries*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CF" & liRow).Value Like "*salary*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CH" & liRow).Value
Like "*salaries*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CH" & liRow).Value Like "*salary*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CJ" & liRow).Value
Like "*salaries*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CJ" & liRow).Value Like "*salary*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CL" & liRow).Value
Like "*salaries*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CL" & liRow).Value Like "*salary*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
End If
liRow = liRow + 1
Loop
End Sub

Does anybody have any ideas why what I am doing won't
work, I am completely dumbfounded..
 
It would be helpful if you told us what "doesn't work" means.

Frankly this code could never have worked as it is. Since there is nothing
to stop it, it goes on until an overflow error occurs when it reaches the
capacity of integers at row 32767.
 
-----Original Message-----
It would be helpful if you told us what "doesn't work" means.

Frankly this code could never have worked as it is. Since there is nothing
to stop it, it goes on until an overflow error occurs when it reaches the
capacity of integers at row 32767.

--
Jim Rech
Excel MVP


.
Hi Jim,
Click to expand...
First off all, thanks for assuming I'm a total moron, I
appreciate that. I do have a break point in there when it
reaches a certain identifying cell. I purposely excluded
it because I work in a high security environment and
couldn't include some pieces of code that identifies who I
work for and what I do. What I pasted into the posting was
the portion of code that wasn’t working working. By
examining the code you should be able to tell that when
the code finds the string "salaries" or "salary" it is to
put an "S" in the corresponding cell in column CM.

Again my question is why won’t this code work when I paste
it into another file’s code module. It does work in the
original module I created it in.

Thanks..
 
Ben Martens postulated on 2/25/2004 11:38 AM:
I have a bunch of spreadsheets with expense data on it and
wrote some code to go through cells and find expenses that
are salary related. The code worked fine in the original
module I created it in but when I copy/paste the code into
another module in another file and run it, it doesn't work
anymore. I can not figure out why the same code won't work
in any other files/modules, it just doesn't make any sense
to me. I know I can simply just run the code in the
original file on all the spreadsheets I need to do this on
but, for convience sake I would like to just use the same
code in other modules/files. Not to mention that I simply
want to understand why this won't work when I copy/paste
the code into another module/file.

Below is the code I wrote:

Private Sub AddCode()
Dim liRow As Integer, myTest

liRow = 9
Do
If Range("CA" & liRow).Value <> Empty Then
myTest = Range("CB" & liRow).Value
Like "*salaries*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CB" & liRow).Value Like "*salary*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CD" & liRow).Value
Like "*salaries*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CD" & liRow).Value Like "*salary*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CF" & liRow).Value
Like "*salaries*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CF" & liRow).Value Like "*salary*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CH" & liRow).Value
Like "*salaries*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CH" & liRow).Value Like "*salary*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CJ" & liRow).Value
Like "*salaries*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CJ" & liRow).Value Like "*salary*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CL" & liRow).Value
Like "*salaries*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
myTest = Range("CL" & liRow).Value Like "*salary*"
If myTest = True Then
Range("CM" & liRow).Value = "S"
End If
End If
liRow = liRow + 1
Loop
End Sub

Does anybody have any ideas why what I am doing won't
work, I am completely dumbfounded..
Click to expand...

The Range().Value returns a Variant. The Like function is expecting a
String. My best guess is that the data is converted to something other
than a String. Try CStr(Range(...).Value) thus forcing it to a String.


" Every little BYTE helps "
 
...
...
. . . What I pasted into the posting was
the portion of code that wasn't working working. By
examining the code you should be able to tell that when
the code finds the string "salaries" or "salary" it is to
put an "S" in the corresponding cell in column CM.
Click to expand...

OK, so what do you mean by 'this code doesn't work'? Does it do nothing when it
should do something? Does it throw runtime errors? Does it open Internet
Explorer to the Dancing Hamsters web site? Details help.

Given the code you provided in your original posting, it strikes me that what
you're doing could be done more economically as


Dim liRow As Long 'it *SHOULD* be a long integer breakpoints or no
Dim liCol As Long
Dim strFoo As String

For liRow = 9 To ActiveSheet.Rows.Count 'restrict as needed

strFoo = Cells(liRow, 2).Value

For liCol = 4 To 12 Step 2
strFoo = strFoo & Cells(liRow, liCol).Value
Next liCol

If InStr(1, strFoo, "salary", 1) * InStr(1, strFoo, "salaries", 1) > 0 Then
Cells(liRow, 13).Value = "S"
End If

Next liRow


One possible reason your Like expressions don't work the same in different
modules is that these different modules could have different Option Compare
settings. If Option Compare Binary, "DODA" Like "*od*" is FALSE, but with Option
Compare Text, "DODA" Like "*od*" is TRUE. The Like operator only functions in
the context of the containing module's settings, so there's no way for Like to
work case-insensitive if the module is set for binary text comparison. When
you're only looking for literal substrings, it's better to use InStr which can
be set for case-sensitive or case-insensitive operation on a call by call basis.

So, what are your respective module settings? Is all your data in lower case or
does the it vary from file to file?
 
...
...
If InStr(1, strFoo, "salary", 1) * InStr(1, strFoo, "salaries", 1) > 0 Then
Click to expand...
...

@#$%! Change the * to +, so this line should be

If InStr(1, strFoo, "salary", 1) + InStr(1, strFoo, "salaries", 1) > 0 Then
 
1. I am surprised it works at all because you have, in effect, a
endless loop - no method of exit from
'-------------------------------
Do

Loop
'-------------------------------

You probably get an Overflow error when Excel runs out of memory (i
you are lucky).

2. The use of Empty is not really correct because it usually refers t
a defined object variable. I don't know if this makes any differenc
though. More usually we use "" or 0. eg.
If Range("CA" & liRow).Value <> "" Then .............
 
-----Original Message-----
...
...

OK, so what do you mean by 'this code doesn't work'? Does it do nothing when it
should do something? Does it throw runtime errors? Does it open Internet
Explorer to the Dancing Hamsters web site? Details help.

Given the code you provided in your original posting, it strikes me that what
you're doing could be done more economically as


Dim liRow As Long 'it *SHOULD* be a long integer breakpoints or no
Dim liCol As Long
Dim strFoo As String

For liRow = 9 To ActiveSheet.Rows.Count 'restrict as needed

strFoo = Cells(liRow, 2).Value

For liCol = 4 To 12 Step 2
strFoo = strFoo & Cells(liRow, liCol).Value
Next liCol

If InStr(1, strFoo, "salary", 1) * InStr(1,
Click to expand...
strFoo, "salaries", 1) > 0 Then
Cells(liRow, 13).Value = "S"
End If

Next liRow


One possible reason your Like expressions don't work the same in different
modules is that these different modules could have different Option Compare
settings. If Option Compare Binary, "DODA" Like "*od*" is FALSE, but with Option
Compare Text, "DODA" Like "*od*" is TRUE. The Like operator only functions in
the context of the containing module's settings, so there's no way for Like to
work case-insensitive if the module is set for binary text comparison. When
you're only looking for literal substrings, it's better to use InStr which can
be set for case-sensitive or case-insensitive operation on a call by call basis.

So, what are your respective module settings? Is all your data in lower case or
does the it vary from file to file?
Click to expand...

Yes, perhaps I could have explained it better but I
figured by examing the code one could figure that it was
not returning a true in the "Like" test when it should
have.

I know what you are saying about using a long but the
reason I use an integer is because the multiple files that
I deal with are sometimes not consistent because sometimes
different users do different things and I can not be
guaranteed that I will be dealing with a consistent format
from spreadsheet to spreadsheet. Sometimes there are
blank rows, sometimes not and I can't be sure the end of
sheet indicator is where it is supposed to be or that some
user didn't accidentally enter something in the very last
cell in the spreadsheet area some 64000 rows down. It has
happened before so when I write code for Excel I have to
write it in a way that assumes chaos will happen. So I use
an integer because I would rather have the code crap out
at the upper limit of an integer rather than continue on
to the very last available row, it just saves time and
aggravation.

Regardless, I like the efficiency of your code and I have
incorporated into my sub-procedure. I don't write code
for Excel that often so I'll be the first to admit I don't
always know the best way to do things. So again, thanks
for your help I appreciate it.
 
-----Original Message-----
1. I am surprised it works at all because you have, in effect, an
endless loop - no method of exit from
'-------------------------------
Do

Loop
'-------------------------------

You probably get an Overflow error when Excel runs out of memory (if
you are lucky).

2. The use of Empty is not really correct because it usually refers to
a defined object variable. I don't know if this makes any difference
though. More usually we use "" or 0. eg.
If Range("CA" & liRow).Value <> "" Then ..............


---
Message posted

.
If you would have looked at the all the postings for this
Click to expand...
question you would have found out that I took some code
out of there for security reasons. The loop had a break
point I just included the part of the code that wasn't
working. The "Like" test wasn't returning true when it
should have, that was the problem. I appologize for not
being specific enough.

As far as "Empty" goes, I got used to using it because I
have come across situations where <> "" didn't work. I
can't remeber specific examples but generally it was
because of something the user did. As a result I found
that "Empty" was a good catch all.
 
Back
Top