Vlookup FALSE Value in VBA

  • Thread starter Thread starter donh
  • Start date Start date
D

donh

Hi Group,

I'm using a form to enter addresses into a worksheet and have created
a lookup which is fed by another form, "postcode search"

Private Sub CommandButton1_Click()
Sheet1.Range("C2") = HouseNo.Text
Sheet1.Range("C3") = HouseName.Text
Sheet1.Range("C4") = Postcode.Text
Unload Me
addressbook.Show
End Sub


It works fine until it encounters an incorrect or unknown postcode,
where the "address form" displays the lookups FALSE value. What I
guess I need is a message box that states not found and a form for
manual entry without postcode..

At present I have a form that asks if they know the postcode, if they
do it opens the postcode search, if not the addressform without the
lookup links. And the completed postcode search opening the
addressform with which has the lookup linked fields.

Addressform VBA

Private Sub OKButton_Click()
' Make sure Address Book is active
Sheets("Data").Activate

Call Unprotect

'Make sure a name is entered
If Surname.Text = "" Then
MsgBox "You must enter a Surname. Please enter Unknown if not
known"
Exit Sub
End If

'Make sure a watch is assigned
If AssignedTo.Text = "" Then
MsgBox "You must assign this"
Exit Sub
End If

'Make sure a postcode is entered
If Postcode.Text = "" Then
MsgBox "You must enter a postcode. Please enter Unknown if not
known"
Exit Sub
End If




' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("D:D")) + 1

' Transfer the title
If OptionMr Then Cells(NextRow, 10) = "Mr"
If OptionMrs Then Cells(NextRow, 10) = "Mrs"
If OptionMiss Then Cells(NextRow, 10) = "Miss"
If OptionMs Then Cells(NextRow, 10) = "Ms"


' Transfer the name
Cells(NextRow, 11) = Firstname.Text
Cells(NextRow, 12) = Surname.Text
Cells(NextRow, 13) = TelHome.Text
Cells(NextRow, 14) = Mobile.Text
Cells(NextRow, 15) = TelOther.Text
Cells(NextRow, 16) = HouseName.Text
Cells(NextRow, 17) = HouseNo.Text
Cells(NextRow, 18) = Road.Text
'Cells(NextRow, 19) = RoadOff.Text
Cells(NextRow, 20) = Area.Text
Cells(NextRow, 21) = Town.Text
Cells(NextRow, 22) = County.Text
Cells(NextRow, 23) = Postcode.Text

Cells(NextRow, 4) = AssignedTo.Text
Cells(NextRow, 5) = InfoFrom.Text
Cells(NextRow, 6) = FurtherInfo.Text



' Clear the controls for the next entry
Firstname.Text = ""
Surname.Text = ""

HouseNo.Text = ""
HouseName.Text = ""
Road.Text = ""
'RoadOff.Text = ""
Area.Text = ""
Town.Text = ""
County.Text = ""
Postcode.Text = ""
TelHome.Text = ""
TelOther.Text = ""
Mobile.Text = ""
AssignedTo.Text = ""
InfoFrom.Text = ""
FurtherInfo.Text = ""


OptionMr = False
OptionMrs = False
OptionMiss = False
OptionMs = False

Firstname.SetFocus
EnableEvents = True


Unload Me

End Sub




Any help or suggestions would be most welcome


DonH
 
I don't see the lookup, but you could use something like

=IF(ISNA(lookup_formula),"PostCode Invalid",lookup_formula)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
I don't see the lookup, but you could use something like

=IF(ISNA(lookup_formula),"PostCode Invalid",lookup_formula)

--
Click to expand...


Thank you Bob.

The lookup is'nt part of the VBA but it is given its argument from
the

Private Sub CommandButton1_Click()
Sheet1.Range("C4") = Postcode.Text

section which is on the postcode search form. The lookup results are
named ranges which the

Cells(NextRow, 18) = Road.Text
Cells(NextRow, 20) = Area.Text
Cells(NextRow, 21) = Town.Text
Cells(NextRow, 22) = County.Text
Cells(NextRow, 23) = Postcode.Text


gets their values from


I can add your suggestion to the lookup but is this something that
can, or should be handled by VBA? So a new entry/manual entry address
form is opened automatically if a postcode isn't found.

Many thanks

DonH
 
Back
Top