I have VBA code iterating through Excel data that boils down to
Sub Simple()
For Each cell In Range("E3:E2000")
Select Case cell.Value
Case "AA"
'Unique1
Case "BB"
'Unique2
Case "CC", "DD", "FF", "JJ", "MM"
'Standard Set 1
Case "EE", "HH", "II", "NN"
'Standard Set 2
Case Else
End Select
Next cell
End Sub
Standard set 1 & 2 have at least a dozen situations in each and has potential in the future to have items added, removed, or moved to the other set. I would like to create a range of cells that contains all of the conditions for Set 1 and a separate range for Set 2.
How do I write a Case statement that checks that range?
Per other posts I tried to create a test program using VLookup:
Sub Test2()
Data = Range("F2").Value
'On Error Resume Next
Select Case Data
Case Application.WorksheetFunction.VLookup(Data, Sheet13.Range("A:A"), 1, False)
MsgBox ("Std1")
Case Application.WorksheetFunction.VLookup(Data, Sheet13.Range("B:B"), 1, False)
MsgBox ("Std2")
Case Else
MsgBox ("Not Found")
End Select
End Sub
If the Data is in A:A it returns "Std1", if its in B:B (or not at all); I get
Run-time error '1004' Unable to get the VLookup property of the WorksheetFunction class
I believe is because the VLookup would return a #N/A in a cell. I tried On Error Resume Next but that results in it always returning "Std1".
I'm not set on using VLookup. It is what looked the most promising. Everything else wants another for loop which would be extremely clunky since it would be nested inside a for loop iterating through a huge amount of data.
I have VBA code iterating through Excel data that boils down to
Sub Simple()
For Each cell In Range("E3:E2000")
Select Case cell.Value
Case "AA"
'Unique1
Case "BB"
'Unique2
Case "CC", "DD", "FF", "JJ", "MM"
'Standard Set 1
Case "EE", "HH", "II", "NN"
'Standard Set 2
Case Else
End Select
Next cell
End Sub
Standard set 1 & 2 have at least a dozen situations in each and has potential in the future to have items added, removed, or moved to the other set. I would like to create a range of cells that contains all of the conditions for Set 1 and a separate range for Set 2.
How do I write a Case statement that checks that range?
Per other posts I tried to create a test program using VLookup:
Sub Test2()
Data = Range("F2").Value
'On Error Resume Next
Select Case Data
Case Application.WorksheetFunction.VLookup(Data, Sheet13.Range("A:A"), 1, False)
MsgBox ("Std1")
Case Application.WorksheetFunction.VLookup(Data, Sheet13.Range("B:B"), 1, False)
MsgBox ("Std2")
Case Else
MsgBox ("Not Found")
End Select
End Sub
If the Data is in A:A it returns "Std1", if its in B:B (or not at all); I get
Run-time error '1004' Unable to get the VLookup property of the WorksheetFunction class
I believe is because the VLookup would return a #N/A in a cell. I tried On Error Resume Next but that results in it always returning "Std1".
I'm not set on using VLookup. It is what looked the most promising. Everything else wants another for loop which would be extremely clunky since it would be nested inside a for loop iterating through a huge amount of data.
Working from your original code:
Sub Simple()
Dim v, cell As Range
For Each cell In Range("E3:E20")
v = cell.Value
If Len(v) > 0 Then
Select Case v
Case "AA"
Debug.Print v, "Unique 1"
Case "BB"
Debug.Print v, "Unique 2"
Case MatchedValue(v, sheet13.Columns("A"))
Debug.Print v, "Std A"
Case MatchedValue(v, sheet13.Columns("B"))
Debug.Print v, "Std B"
Case Else
Debug.Print v, "Unknown mapping"
End Select
End If
Next cell
End Sub
'Is there an exact match for `v` in the (single-column) range `rng`?
'If Yes return `v` else return empty string
Function MatchedValue(v, rng As Range)
Dim m
m = Application.Match(v, rng, 0)
MatchedValue = IIf(IsError(m), "", v)
End Function
You can try this code :
Sub Test3()
Dim Data, Found As Range
Data = Range("F2").Value
Set Found = Sheet13.Range("A:B").Find(What:=Data, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Found Is Nothing Then
MsgBox "Not Found"
Else
Select Case Found.Column
Case 1: MsgBox "Std1"
Case 2: MsgBox "Std2"
End Select
End If
End Sub
I can propose you one more solution:
Option Explicit
Function GetGroup(v)
Dim i&
i = Application.VLookup(v, Sheet1.[A:B], 2, False)
Select Case i
Case 1
GetGroup = "Unique 1"
Case 2
GetGroup = "Unique 2"
Case 3
GetGroup = "Std A"
Case 4
GetGroup = "Std B"
Case Else
GetGroup = "Unknown mapping"
End Select
End Function
Sub Test()
Debug.Print GetGroup("JJ")
End Sub
Just add additional values and related cases at the end of "Sheet1.[A:B]".