IF statement for lookup matchup

[Andyjim]

I have the following situation:
User types a currency identifier (text) into a cell in column B, and types
values into two other cells as well, then hits Control-P to run the
GetPipValues macro. We then need to test three conditions before letting the
GetPipValues macro run.
1. Is the user-selected cell in Column B? Our existing code checks that.
2. Is there data in the other two appropriate cells? Existing code and
formula checks that.
3. Does the value the user entered match with a value in a lookup table
range? I need code for this. The lookup table already exists. If no match,
show error message and Exit Sub.

For inserting the Condition 3 check, I have two questions. First, how to
code the Condition 3 check - Does the active cell value match a value in the
lookup table range? Second, how to insert the Condition 3 IF into the
existing IF structure.

Here's the code, with note where I need to insert the Condition 3 test. The
code does work as is, just need to add Condition 3:

Sub OptimumPosition()
'Control-P activates this macro

Dim pcell As String
pcell = ActiveCell.Address

ActiveCell.Select
'CHECK CONDITION 1
If Not Intersect(ActiveCell, Columns("B:B")) Is Nothing Then
'CHECK CONDITION 2, which is determined by a formula in Column 45
ActiveCell.Offset(rowoffset:=0, columnoffset:=45).Select
If ActiveCell = "False" Then 'If Condition 2 NOT met, error
message, and Exit Sub
MsgBox ("Enter data in required fields and re-run Control-p")
Exit Sub
Else 'If Condition 2 IS met,
'NEED TO CHECK CONDITION 3 HERE, before GetPipValues macro runs
GetPipValues 'Run GetPipValues macro
End If

'Additional code here (Working OK, removed for clarity)

'If selected cell IS NOT in Column B, error message & Exit Sub
Else
MsgBox ("Please launch Control-P Hot Key from Column B")
Exit Sub
End If


End Sub


Thanks,
Andy

 

[Tim Williams]

Lightly tested

Tim

'*****************************************
Sub OptimumPosition()
'Control-P activates this macro

Dim pcell As Range, rTable as Range
set pcell = ActiveCell
set rTable=Thisworkbook.sheets("sheetname").Range("A1:A10")

If Not Intersect(ActiveCell, Columns("B:B")) Is Nothing Then
'CHECK CONDITION 2, which is determined by a formula in Column 45
If pcell.offset(0,45.value) = "False" Then 'If Condition 2 NOT met,
error message, and Exit Sub
MsgBox ("Enter data in required fields and re-run Control-p")
Exit Sub
Else
If IsError(Application.Match(pcell.value,rTable,0)) then
Msgbox "Invalid entry"
exit sub
end if
GetPipValues 'Run GetPipValues macro
End If

'Additional code here (Working OK, removed for clarity)

'If selected cell IS NOT in Column B, error message & Exit Sub
Else
MsgBox ("Please launch Control-P Hot Key from Column B")
Exit Sub
End If

End Sub
'********************************************8

 

[Andyjim]

That works, with a little tweaking in the code I didn't show you. Thanks Tim.