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