need help with VBA on opening a form if Vlookup is false.

[GregJG]

I am using this code right now to insert data from a form to
spreadsheet.

If chkMProFCB.Value = True Then
Range("C15").Select
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Range("c65536").End(xlUp).Offset(1, 0).Value
Application.WorksheetFunction.VLookup(chkMProFCB.Caption
Workbooks("bidditdb.xls").Sheets("wa").Range("a1:b200"), 2, False)
Range("J65536").End(xlUp).Offset(1, 0).Value
Application.WorksheetFunction.VLookup(txtMproFCB.Text
Workbooks("bidditdb.xls").Sheets("mat").Range("a1:c200"), 3, False)
End If

I have tried changing the vlookup to Form1.show instead of "false" bu
can't seem to get the form to open if the vlookup was not found.

I've also tried adding

If vlookup is nothing then
form1.show
end if


can anyone help with getting this form to open if the Vlookup is no
found

 

[Rollin_Again]

Why not just add an *IF* Statement after the VLOOKUP is performed? IF
value of "False" is returned to the cell then clear the cell and sho
the form.



Rolli

 

[Dave Peterson]

I'm not quite sure what you're doing, but you can look at the results of your
=vlookup() formula like:

Option Explicit
Sub testme()
Dim res As Variant

res = Application.VLookup(chkMProFCB.Caption, _
Workbooks("bidditdb.xls").Sheets("wa").Range("a1:b200"), 2, False)

If IsError(res) Then
'it's the same as #n/a
form1.Show
Else
'don't show it
End If

End Sub

(Untested!)

(Note that I used application.vlookup and not
application.worksheetfunction.vlookup. They handle errors differently. I find
using application.vlookup easier.)

 

[GregJG]

Thanks for the reply.

I ended up using this.

If chkMProFCB.Value = True Then
Range("C15").Select
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Range("c65536").End(xlUp).Offset(1, 0).Value
Application.WorksheetFunction.VLookup(chkMProFCB.Caption
Workbooks("bidditdb.xls").Sheets("wa").Range("a1:b200"), 2, False)

'^ I've tried removing the worksheetfunction from this, but won't wor
without it here. The caption will never change so no big deal.

' the next section is a text box that I am having problems with.
incorporated Rollins suggestion and the code works great. I ended u
adding a messagebox instead of automatically opening the form, incas
of a typo.

If Application.IsNA(Application.VLookup(txtMproFCB.Text
Workbooks("bidditdb.xls").Sheets("mat").Range("a1:b200"), 2, False))
False Then
Range("J65536").End(xlUp).Offset(1, 0).Value
Application.VLookup(txtMproFCB.Text
Workbooks("bidditdb.xls").Sheets("mat").Range("a1:c200"), 3, False)

Else

If MsgBox("Do you want to add a new product for FCB", vbYesNo) = vbYe
Then
frmNewMat.Show
End If
End If
End I

 

[Rollin_Again]

*the next section is a text box that I am having problems with. *

So what is the problem you are having with the textbox?



Rolli