right click on the sheet tab where you would enter a value in A1 and select
view code. Put in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, res As Variant
If Target.Address = "$A$1" Then
Set rng = Worksheets("Sheet2").Range("A1:C1800")
res = Application.VLookup(Target, rng, 2, False)
If IsError(res) Then
Range("A2:A3").Value = "Not in Database, " & _
"manual entry required"
Else
Range("A2").Value = res
Range("A3").Value = Application.VLookup(Target, _
rng, 3, False)
End If
End Sub

--
Regards,
Tom Ogilvy

"Mark" wrote:

I need to find a way to use VLOOKUP in VBA. Using the formula in my
spreadsheet won't work for me. Users will enter data in cell A1. If it
matches data in another list elsewhere, VLOOKUP will autofill A2 and A3.
However, if it doesn't match any other data, users must manually fill in A2
and A3. This causes a problem with the formula as the manual entries will
erase the formula.

The VLOOKUP formula I'm using is:

=VLOOKUP(A1,Sheet2!A1:C1800,2,FALSE)
and
=VLOOKUP(B2,Sheet2!A1:C1800,3,FALSE)

Can anyone offer me some helpful suggestions?

Mark