#1   Report Post  
Jeff
 
Posts: n/a
Default 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   Report Post  
Alan Beban
 
Posts: n/a
Default

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



  #3   Report Post  
Jeff
 
Posts: n/a
Default

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




  #4   Report Post  
Alan Beban
 
Posts: n/a
Default

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




  #5   Report Post  
Jeff
 
Posts: n/a
Default

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







  #6   Report Post  
Alan Beban
 
Posts: n/a
Default

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
  #7   Report Post  
Jeff
 
Posts: n/a
Default

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

  #8   Report Post  
Alan Beban
 
Posts: n/a
Default

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

  #9   Report Post  
Jeff
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup w/ VB Jeff Excel Discussion (Misc queries) 1 November 26th 04 09:39 PM
Problem with vlookup Jeff Excel Discussion (Misc queries) 2 November 26th 04 06:29 PM


All times are GMT +1. The time now is 01:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"