Using VLOOKUP in VBA code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 15 Aug 2006 18:29 GMT

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

Reply to this Message

Dave Peterson - 15 Aug 2006 18:59 GMT

How about just dropping the formulas back into A2 and A3 each time A1 changes.

If you want to try, rightclick on the worksheet tab that should have this
behavior and select view code.

Paste this into that new code window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Me.Range("a2").Formula = "=VLOOKUP(A1,Sheet2!A1:C1800,2,FALSE)"
Me.Range("a3").Formula = "=VLOOKUP(A1,Sheet2!A1:C1800,3,FALSE)"
Application.EnableEvents = True

End Sub

And then back to excel to test it.

> 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
[quoted text clipped - 12 lines]
>
> Mark

Signature

Dave Peterson

Reply to this Message

Mark - 18 Aug 2006 16:41 GMT

Thanks, Dave.

I can see how this code will work, but for what I need it to do is not quite
what it's written for.

Thank you very much for your time. I truly appreciate it and didn't want
you to think I ignored your help. I'm sure I'll be writing again....... ;)

Mark

> How about just dropping the formulas back into A2 and A3 each time A1 changes.
>
[quoted text clipped - 33 lines]
> >
> > Mark

Reply to this Message