Basically, I'm matching 4 strings currently, and have a bunch of extra code that I am trying to clean up. These are just defined by me within the code, not pulled from any Excel Worksheet.
Is there a way to create the list in VBA and then just do a loop through the list?
So I have something similar to this:
Dim lMatch As Long
lMatch = Application.Match("AString", ws.Range("A1:A20"), 0)
If Not IsError(lMatch) Then
*some code*
End If
lMatch = Application.Match("BString", ws.Range("A1:A20"), 0)
If Not IsError(lMatch) Then
*some code*
End If
lMatch = Application.Match("CString", ws.Range("A1:A20"), 0)
If Not IsError(lMatch) Then
*some code*
End If
lMatch = Application.Match("DString", ws.Range("A1:A20"), 0)
If Not IsError(lMatch) Then
*some code*
End If
Which you can see it's very redundant.
I wanted to just make a loop where I store my search terms in some type of array in VBA and then loop through it, but I'm not sure on the exact way to do this.
So something like:
Dim iRow as Integer
???("AString", BString", CString", DString")
For iRow = 1 to 4
lMatch = Application.Match(???(iRow), ws.Range("A1:A20"), 0))
If Not IsError(lMatch) Then
*some code*
End If
Next iRow
Can I just return the position of the string to search from some type of array? I'm not really familiar with using this type of stuff, thanks.
Bookmarks