Home 
Search 
Today's Posts 
#1




Vlookup with VBA
Hello,
I need to do a vlookup in a VBA Macro for the values in column "B." However the values in the Table Array of column "A" are the same. Can I combine a Vlookup and an Offset ? Ex: Column A Column B Paid Vendor 1 paid Vendor 2 Etc 
#2




You might want to consider making the functions in the freely
downloadable file at http://home.pacbell.net/beban available to your workbook. Then you could array enter into a multicelled column =VLookups("paid",a1:c3,2) Alan Beban Jeff wrote: Hello, I need to do a vlookup in a VBA Macro for the values in column "B." However the values in the Table Array of column "A" are the same. Can I combine a Vlookup and an Offset ? Ex: Column A Column B Paid Vendor 1 paid Vendor 2 Etc 
#3




Hi Allan,
I'm getting an error mesage VLookups("paid",A1:B3,2)  (Userdefined function) paid vendor # 1 paid Vendor # 2 #VALUE Can you help me? "Alan Beban" wrote: You might want to consider making the functions in the freely downloadable file at http://home.pacbell.net/beban available to your workbook. Then you could array enter into a multicelled column =VLookups("paid",a1:c3,2) Alan Beban Jeff wrote: Hello, I need to do a vlookup in a VBA Macro for the values in column "B." However the values in the Table Array of column "A" are the same. Can I combine a Vlookup and an Offset ? Ex: Column A Column B Paid Vendor 1 paid Vendor 2 Etc 
#4




Have you arrayentered (i.e., entered with Ctrl+Shift+Enter instead of
just Enter) into a 2cell column? Alan Beban Jeff wrote: Hi Allan, I'm getting an error mesage VLookups("paid",A1:B3,2)  (Userdefined function) paid vendor # 1 paid Vendor # 2 #VALUE Can you help me? "Alan Beban" wrote: You might want to consider making the functions in the freely downloadable file at http://home.pacbell.net/beban available to your workbook. Then you could array enter into a multicelled column =VLookups("paid",a1:c3,2) Alan Beban Jeff wrote: Hello, I need to do a vlookup in a VBA Macro for the values in column "B." However the values in the Table Array of column "A" are the same. Can I combine a Vlookup and an Offset ? Ex: Column A Column B Paid Vendor 1 paid Vendor 2 Etc 
#5




Good morning,
Thank you for your help. With Userdefined function "vlookups" Is it possible to write this VBA macro? Set myrng = ("Workbook").worksheet("A").range("a1:b393") = VLookups("paid",myrng,2) Thanks, "Alan Beban" wrote: Have you arrayentered (i.e., entered with Ctrl+Shift+Enter instead of just Enter) into a 2cell column? Alan Beban Jeff wrote: Hi Allan, I'm getting an error mesage VLookups("paid",A1:B3,2)  (Userdefined function) paid vendor # 1 paid Vendor # 2 #VALUE Can you help me? "Alan Beban" wrote: You might want to consider making the functions in the freely downloadable file at http://home.pacbell.net/beban available to your workbook. Then you could array enter into a multicelled column =VLookups("paid",a1:c3,2) Alan Beban Jeff wrote: Hello, I need to do a vlookup in a VBA Macro for the values in column "B." However the values in the Table Array of column "A" are the same. Can I combine a Vlookup and an Offset ? Ex: Column A Column B Paid Vendor 1 paid Vendor 2 Etc 
#6




Jeff wrote:
Is it possible to write this VBA macro? Set myrng = ("Workbook").worksheet("A").range("a1:b393") = VLookups("paid",myrng,2) No. If "Workbook" is the name of a workbook, and "A" is the name of a worksheet in Workbooks, then the following is valid: Set myrng = Workbooks("Workbook").Worksheets("A").Range("a1:b3 93") myVar = VLookups("paid",myrng,2) myVar will be a twodimensional vertical array of the values from B1:B393 that correspond to the occurrences in A1:A393 of paid (and Paid and PAID, etc.) Alan Beban 
#7




Hi,
Macro works with case #1, but does not work with case # 2. Would have any ideas as to why case # 2 doesn't work ? In case #2 the errror is #value Case #1 Dim myrng As Range Dim myvar As Variant Set myrng = Workbooks("Invoices OCT04.xls").Worksheets("b").Range("a1:b5") myvar = "=PERSONAL.XLS!Module112.VLookups(""paid"",B!A1:B5 ,2)" Sheets("A").Activate Range("B1").Select ActiveCell.Formula = myvar Case # 2 Dim myrng As Range Dim myvar As Variant Set myrng = Workbooks("Invoices OCT04.xls").Worksheets("b").Range("a1:b5") myvar = "=PERSONAL.XLS!Module112.VLookups(""paid"",myrng,2 )" Sheets("A").Activate Range("B1").Select ActiveCell.Formula = myvar Thanks, "Alan Beban" wrote: Jeff wrote: Is it possible to write this VBA macro? Set myrng = ("Workbook").worksheet("A").range("a1:b393") = VLookups("paid",myrng,2) No. If "Workbook" is the name of a workbook, and "A" is the name of a worksheet in Workbooks, then the following is valid: Set myrng = Workbooks("Workbook").Worksheets("A").Range("a1:b3 93") myVar = VLookups("paid",myrng,2) myVar will be a twodimensional vertical array of the values from B1:B393 that correspond to the occurrences in A1:A393 of paid (and Paid and PAID, etc.) Alan Beban 
#8




In Case #2, instead of [",myrng] use ["", & myrng & "]
Alan Beban Jeff wrote: Hi, Macro works with case #1, but does not work with case # 2. Would have any ideas as to why case # 2 doesn't work ? In case #2 the errror is #value Case #1 Dim myrng As Range Dim myvar As Variant Set myrng = Workbooks("Invoices OCT04.xls").Worksheets("b").Range("a1:b5") myvar = "=PERSONAL.XLS!Module112.VLookups(""paid"",B!A1:B5 ,2)" Sheets("A").Activate Range("B1").Select ActiveCell.Formula = myvar Case # 2 Dim myrng As Range Dim myvar As Variant Set myrng = Workbooks("Invoices OCT04.xls").Worksheets("b").Range("a1:b5") myvar = "=PERSONAL.XLS!Module112.VLookups(""paid"",myrng,2 )" Sheets("A").Activate Range("B1").Select ActiveCell.Formula = myvar Thanks, "Alan Beban" wrote: Jeff wrote: Is it possible to write this VBA macro? Set myrng = ("Workbook").worksheet("A").range("a1:b393") = VLookups("paid",myrng,2) No. If "Workbook" is the name of a workbook, and "A" is the name of a worksheet in Workbooks, then the following is valid: Set myrng = Workbooks("Workbook").Worksheets("A").Range("a1:b3 93") myVar = VLookups("paid",myrng,2) myVar will be a twodimensional vertical array of the values from B1:B393 that correspond to the occurrences in A1:A393 of paid (and Paid and PAID, etc.) Alan Beban 
#9




Hi,
I have error "RunTime Error '1004' with the corrected macro: Dim myrng As Range Dim myvar As Variant Set myrng = Workbooks("Invoices OCT04.xls").Worksheets("b").Range("a1:b5") myvar = "=PERSONAL.XLS!Module112.VLookups(""paid"",& myrng &,2)" Sheets("A").Activate Range("B1").Select ActiveCell.Formula = myvar Error on this line. Thanks, "Alan Beban" wrote: In Case #2, instead of [",myrng] use ["", & myrng & "] Alan Beban Jeff wrote: Hi, Macro works with case #1, but does not work with case # 2. Would have any ideas as to why case # 2 doesn't work ? In case #2 the errror is #value Case #1 Dim myrng As Range Dim myvar As Variant Set myrng = Workbooks("Invoices OCT04.xls").Worksheets("b").Range("a1:b5") myvar = "=PERSONAL.XLS!Module112.VLookups(""paid"",B!A1:B5 ,2)" Sheets("A").Activate Range("B1").Select ActiveCell.Formula = myvar Case # 2 Dim myrng As Range Dim myvar As Variant Set myrng = Workbooks("Invoices OCT04.xls").Worksheets("b").Range("a1:b5") myvar = "=PERSONAL.XLS!Module112.VLookups(""paid"",myrng,2 )" Sheets("A").Activate Range("B1").Select ActiveCell.Formula = myvar Thanks, "Alan Beban" wrote: Jeff wrote: Is it possible to write this VBA macro? Set myrng = ("Workbook").worksheet("A").range("a1:b393") = VLookups("paid",myrng,2) No. If "Workbook" is the name of a workbook, and "A" is the name of a worksheet in Workbooks, then the following is valid: Set myrng = Workbooks("Workbook").Worksheets("A").Range("a1:b3 93") myVar = VLookups("paid",myrng,2) myVar will be a twodimensional vertical array of the values from B1:B393 that correspond to the occurrences in A1:A393 of paid (and Paid and PAID, etc.) Alan Beban 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Vlookup w/ VB  Excel Discussion (Misc queries)  
Problem with vlookup  Excel Discussion (Misc queries) 