Instead of typing a bunch of "Or" statements, how can I implement a function in this code?
Sub test()
Dim DataRange As Range
Dim LastRow As Integer
Dim i As Integer
Dim SplitVal() As String
Dim OutputOffset As Long
OutputOffset = 0
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
For i = 2 To LastRow
If InStr(1, Cells(i, 10).Value, "Test1", vbTextCompare) <> 0 Or
InStr(1, Cells(i, 10).Value, "Test2", vbTextCompare) <> 0 Or
InStr(1, Cells(i, 10).Value, "Test3", vbTextCompare) <> 0 Then
SplitVal = Split(Cells(i - 2, 10).Value, " ", 2)
Cells(i + OutputOffset, 13).Value = SplitVal(0)
Cells(i + OutputOffset, 14).Value = SplitVal(1)
Cells(i + OutputOffset, 15).Value = Cells(i + 1, 10).Value
End If
Next i
End Sub
Hello to all. So you can see my code goes through and validates Test1, Test2 or Test3. The problem is I have 50 accounts, I need to check not 3!
How do I create and populate a list, create a function that replicates what I have above, and iterate over the list using the function?
Thanks everyone!
source to share
Build an array of 50 possible choices. Exit the loop as soon as it is found.
Option Explicit
Sub test()
Dim DataRange As Range
Dim lastRow As Long
Dim i As Integer
Dim SplitVal() As String
Dim OutputOffset As Long
Dim v As Long, tests As Variant
OutputOffset = 0
tests = Array("Test1", "Test2", "Test3", "Test4", "Test5", "Test6", "Test7", "Test8", "Test9", _
"Test10", "Test11", "Test12", "Test13", "Test14", "Test15", "Test16", "Test17", "Test18", _
"Test19", "Test20", "Test21", "Test22", "Test23", "Test24", "Test25", "Test26", "Test27")
With Worksheets("Sheet1")
lastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
For i = 2 To lastRow
For v = LBound(tests) To UBound(tests)
If CBool(InStr(1, .Cells(i, 10).Value2, tests(v), vbTextCompare)) Then Exit For
Next v
If v <= UBound(tests) Then
SplitVal = Split(.Cells(i - 2, 10).Value2, " ", 2)
.Cells(i + OutputOffset, 13).Value = SplitVal(0)
.Cells(i + OutputOffset, 14).Value = SplitVal(1)
.Cells(i + OutputOffset, 15).Value2 = .Cells(i + 1, 10).Value2
End If
Next i
End With
End Sub
I added in some links to parent sheets.
This is a problem in itself; he belongs in his field. I use a function like this to short-circuit an otherwise redundant condition - which ParamArray
is the secret sauce here:
Public Function MatchesAny(ByVal needle As String, ParamArray haystack() As Variant) As Boolean
Dim i As Integer
Dim found As Boolean
For i = LBound(haystack) To UBound(haystack)
found = (needle = CStr(haystack(i)))
If found Then Exit For
Next
MatchesAny = found
End Function
It will be used like this:
If MatchesAny(CStr(ActiveSheet.Cells(i, 10).Value), _
"Test1", "Test2", "Test3", "Test4", "Test5", _
"Test6", "Test7", "Test8", "Test9", "Test10", _
"Test11", "Test12", "Test13", ..., "Test50") _
Then
'match was found
End If
You can tweak quite easily haystack
to support passing a one-dimensional array of values like @ Jeeped's answer ; the principle is the same: help out as soon as you know your result; Your current code will execute every single statement InStr
, even if the first boolean expression to be evaluated is True
.
This function returns True
if any element matches the specified string. Sometimes you may need a function that returns True
if any element contains the specified string. This is a different function:
Public Function ContainsAny(ByVal needle As String, ByVal caseSensitive As Boolean, ParamArray haystack() As Variant) As Boolean
Dim i As Integer
Dim found As Boolean
For i = LBound(haystack) To UBound(haystack)
found = Contains(needle, CStr(haystack(i)), caseSensitive)
If found Then Exit For
Next
ContainsAny = found
End Function
This function calls a simple wrapper function to InStr
help improve readability InStr() <> 0
:
Public Function Contains(ByVal needle As String, ByVal haystack As String, Optional ByVal caseSensitive As Boolean = False) As Boolean
Dim compareMethod As VbCompareMethod
If caseSensitive Then
compareMethod = vbBinaryCompare
Else
compareMethod = vbTextCompare
End If
Contains = (InStr(1, haystack, needle, compareMethod) <> 0)
End Function
The usage of this is similar, except that we have a parameter caseSensitive
that must be specified (you can configure it MatchesAny
to have the same signature) before the argument list. Again the same principle: help out as soon as you know what to return.
source to share
Your 50 accounts are probably on the list that is available on your sheet. You can create a strong account and use the function instr
to find a match.
Sub test()
Dim DataRange As Range
Dim LastRow As Integer
Dim i As Long
Dim SplitVal() As String
Dim OutputOffset As Long
OutputOffset = 0
Dim Spike As String
For i = 3 To 11
Spike = Spike & Cells(i, 1).Value & "|"
Next i
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
For i = 2 To LastRow
If InStr(Spike, Cells(i, 10).Value) Then
' If InStr(1, Cells(i, 10).Value, "Test1", vbTextCompare) <> 0 Or
' InStr(1, Cells(i, 10).Value, "Test2", vbTextCompare) <> 0 Or
' InStr(1, Cells(i, 10).Value, "Test3", vbTextCompare) <> 0 Then
SplitVal = Split(Cells(i - 2, 10).Value, " ", 2)
Cells(i + OutputOffset, 13).Value = SplitVal(0)
Cells(i + OutputOffset, 14).Value = SplitVal(1)
Cells(i + OutputOffset, 15).Value = Cells(i + 1, 10).Value
End If
Next i
End Sub
In my example, the list is in A3: A11 in the ActiveSheet. If that doesn't work for you, then put the list on a different sheet and change the above code as follows.
Dim WsList As Worksheet
Dim Spike As String
Set WsList = Worksheets("AccountList")
For i = 3 To 11
Spike = Spike & WsList.Cells(i, 1).Value & "|"
Next i
source to share