Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP in VBA code
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP in VBA code
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code not unique find latest date | Excel Discussion (Misc queries) | |||
VLOOKUP for Zip Code Ranges | Excel Worksheet Functions | |||
Help with VLookup function | Excel Worksheet Functions | |||
Conform a total to a list of results? | Excel Discussion (Misc queries) | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions |