lifeson
07-07-2008, 02:50 AM
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?
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?