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

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

You have a reply in .worksheet.functions.

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

--

Dave Peterson

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??


"Tom Ogilvy" wrote in message
...
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