Using VLOOKUP in VBA code

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

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.

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

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

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44E20B6D.E364B9CF@verizonXSPAM.net...
> 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.
>
> 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

Hi,

I have a similar problem, but one which I have been unable to get answered specifically. What I am trying to do is pull information from my CM Update sheet to another sheet named the PSR. The PSR is the master sheet and much larger than the CM update sheet (many more columns, much more coplicated--> Therefore a "merge" would not work). I have been able to run a simple Vlookup which, if the project numbers are the same for the CM Update sheet and the PSR, to update the PSR accordingly. However, there are two problems with this.

1) Sometimes the CM Update sheet is left blank due to accidental deletion or no updating. If it is left blank, and I have information stored in the PSR, I want the Macro to skip that cell in the PSR and leave the pre-existing information which was originally in the PSR. i.e., I want to retain outdated info rather than wipe out the information completely.
2) Sometimes I add new projects before the CM Update file gets back to me. I want that information to remain untouched by the Macro (i.e., if the Macro does not find a match between the project ID's in the PSR and the CM Update file, I want the PSR file data to remain rather than have the data be wiped out and have a 0 return due to no corresponding information in the CM Update file).

I have incorporated this into a simple point-and-click Macro, for which each column of information I pull has a vlookup function that looks like this:

=IF(ISNA(VLOOKUP(B3,'[CM Upload 03.04.2009.xlsx]Query - Excel Extract Pipeline '!$1:$1048576,9,FALSE)), 0, VLOOKUP(B3,'[CM Upload 03.04.2009.xlsx]Query - Excel Extract Pipeline '!$1:$1048576,9,FALSE))

I know the above function is not good enough. I have tried using IS(NOTBLANK as well, but this attempt I made misses the fundamental point. I need the cell in question in the PSR to remain untouched by the function and remain as it was before the code was executed should the cell be blank in the CM Update file.

Can anyone help me with this? I think this requires VBA code, and I have never done this before. Does anyone have any suggestions, ideas, ect? Should I post the entire macro I had been using in VBA code?

Thank you!

Thank you. I will do so immediately.

I have read the Forum Rules and placed a seperate post at this location regarding my question:

http://www.excelforum.com/excel-prog...ml#post2090808

I apologize for breaking forum rules as I am not a normal online poster and thus unaccustomed to the culture. Thank you!