PDA

View Full Version : Wildcard in loop 'until' condition



k13r4n
12-03-2008, 08:32 PM
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 :banghead:

Simplyfied code snippet

Dim rowNum
rowNum = 1

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

rowNum = rowNum + 1

Loop

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. :help

cheers


Kieran

MaximS
12-03-2008, 09:22 PM
try to change it to:


Dim rowNum
rowNum = 1

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

rowNum = rowNum + 1

Loop

GTO
12-03-2008, 10:55 PM
...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.

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

@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