Wildcard in loop 'until' condition

[k13r4n]

hi all

This one is giving me some trouble, i have a loop that looks down column one of a spreadsheet untill it finds a row that contains L## (## could be any 1 or 2 digit number) but i cant seem to get the wildcard right and it keeps dropping an error on that line

Simplyfied code snippet

[VBA]Dim rowNum
rowNum = 1

Do Until Cells(rowNum, 1).Value = "L#"

rowNum = rowNum + 1

Loop[/VBA]

how do i use a wild card in this situation?? i cant for life of me get it to work and ive looked online and through the help files and cant find anything that solves it.

cheers


Kieran

[MaximS]

try to change it to:

[VBA]
Dim rowNum
rowNum = 1

Do Until Cells(rowNum, 1).Value Like "L*"

rowNum = rowNum + 1

Loop
[/VBA]

[GTO]

...untill it finds a row that contains L## (## could be any 1 or 2 digit number)...

Greetings Kieran,

I wasn't sure if you wanted to find the first entry with only a single/double digit number behind it, such as: L9 or L98, OR, whether the entries you are looking to find would be like part numbers, such as: L9Y002 or L98Y002.

Anyways, the below should do the trick.

[vba]Sub Test()
Dim rowNum As Long
rowNum = 1

Do Until Cells(rowNum, 1).Value Like "L#" _
Or Cells(rowNum, 1).Value Like "L##"

'// For finding "L" with a single or double digit number appended, //
'// and allowing for further (such as: L21JR38-001) tack the //
'// below onto the test. //
'Or Cells(rowNum, 1).Value Like "L##*" _
'Or Cells(rowNum, 1).Value Like "L##*"

rowNum = rowNum + 1

'// just a loose example, but you'll want some way to bail if no matching val is found...//
If rowNum > 65000 Then Exit Sub
Loop
MsgBox "I found: " & Cells(rowNum, 1).Value & ", at: " & _
Cells(rowNum, 1).Address(False, False)
End Sub[/vba]

@MaximS:

With respects, yours would exit the loop upon values such as "L", "Libra", etc..., as the wildcard "*" can stand for any character, characters, or nothing at all.

Hope this helps,

Mark