Match 2 columns

[lifeson]

I ahve a routine that does what I want but is getting verry messay with loads of "if then else" ststements

Which is the best way to achieve the following:dunno

I want to find a value in column "F" (which may be numeric or alphanumeric) and then check that value in the same row on column "E" = 2

This confirms if the item exists in column "F"

Set rFound = wsTree.Columns(6).Find(packID, lookat:=xlWhole)

Then I use this lot to confirm which row the packID is and then check value in column "E"


On Error Resume Next
f = Application.Match(CStr(packID), .Columns(6), 0)
If Err = 0 Then
MsgBox "Alpha, Check if column E row " & f & " = 2"
If .Cells(f, "E").Value = 2 Then
MsgBox "item is a pack"
Qty = .Cells(f, "D").Value
Else
'item is not a pack, do nothing
End If
Else 'pack ID Is numeric
f = Application.Match(CLng(packID), wsTree.Columns(6), 0)
MsgBox "Numeric Check if column E row " & f & " = 2"
If .Cells(f, "E").Value = 2 Then
MsgBox "item is a pack"
Qty = .Cells(f, "D").Value
Else
'item is not a pack, do nothing
End If
End If

Rather messy is there a better way?

[Bob Phillips]

Public Sub Test()
On Error Resume Next
f = Application.Match(CStr(packID), .Columns(6), 0)
On Error GoTo 0
If f > 0 Then

Qty = FindValue(f, "Alpha")
Else

Qty = FindValue(f, "Numeric")
End If
End Sub

Private Function FindValue(lookup As Variant, DataType As String)

MsgBox DataType & ", Check if column E row " & f & " = 2"
If .Cells(f, "E").Value = 2 Then
MsgBox "item is a pack"
Qty = .Cells(f, "D").Value
End If
End Function