|
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 |
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 multi-celled 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 |
Hi Allan,
I'm getting an error mesage VLookups("paid",A1:B3,2) - (User-defined 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 multi-celled 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 |
Have you array-entered (i.e., entered with Ctrl+Shift+Enter instead of
just Enter) into a 2-cell column? Alan Beban Jeff wrote: Hi Allan, I'm getting an error mesage VLookups("paid",A1:B3,2) - (User-defined 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 multi-celled 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 |
Good morning,
Thank you for your help. With User-defined 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 array-entered (i.e., entered with Ctrl+Shift+Enter instead of just Enter) into a 2-cell column? Alan Beban Jeff wrote: Hi Allan, I'm getting an error mesage VLookups("paid",A1:B3,2) - (User-defined 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 multi-celled 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 |
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 two-dimensional 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 |
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 OCT-04.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 OCT-04.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 two-dimensional 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 |
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 OCT-04.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 OCT-04.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 two-dimensional 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 |
Hi,
I have error "Run-Time Error '1004' with the corrected macro: Dim myrng As Range Dim myvar As Variant Set myrng = Workbooks("Invoices OCT-04.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 OCT-04.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 OCT-04.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 two-dimensional 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 |
| All times are GMT +1. The time now is 09:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com