VLOOKUP in VBA code

[Mark]

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

 

[Guest]

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

 

[Dave Peterson]

You have a reply in .worksheet.functions.

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

 

[Mark]

Hello Tom.

I had to change a couple aspects of the code so the results showed in the
Row (1) instead of the Column (A) and it works great! However...I would
like the code to read the first 80 rows of Column A and activate the VLOOKUP
accordingly. I've tried using the Range ("A1:A" & lastrow) as the target
(and a couple other variations), but I keep coming up with an error.

Suggestions, if you don't mind??