PDA

View Full Version : Solved: "Evaluate" or "Worksheetfunction.Vlookup" help



n8Mills
11-21-2008, 12:33 PM
Hi again fellers,

I thought that if I could figure out the simpler ways to use "Evaluate" I could extend that into my more complex problems, but it seems that I have quite a lot to learn.

I am attempting several Vlookups that look similar to this:

With Range(Range("G2"), Range("G2").End(xlDown))
.NumberFormat = "0"
.FormulaR1C1 = "=VLOOKUP(RC[-6],'[" & DBname & "]Slate'!C3:C4,2,FALSE)"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

And I am refactoring the script to write values instead of formulas. I have tried it this way (which writes #N/A or #Value errors to the range):

With Range("I" & rowFirst & ":I" & rowLast)
.value = Evaluate("=VLOOKUP(" & .Offset(0, -7).Address & ",'[" & CSRWIPfile & "]Sheet1'!C2:C12,11,FALSE)")
End With
And this way (which throws a "cannot access WorksheetFunction.VLookup" error):

Set rngCSRWIP = Workbooks(CSRWIPfile).Sheets("Sheet1").Columns("B:U")

With Range("I" & rowFirst & ":I" & rowLast)
.value = Application.WorksheetFunction.VLookup(.Offset(0, -7).Address, rngCSRWIP, 11, False)
End With
So, neither works but I might be close. Anyone care to fine-tune me?

georgiboy
11-21-2008, 12:39 PM
Have you tried any of these methods with a single cell before you went for the range.

Bob Phillips
11-21-2008, 12:57 PM
Evaluate is not the panacea for all ails



With Range("I" & rowFirst & ":I" & rowLast)
.Formula = "=VLOOKUP(B" & rowFirst & ",'[" & CSRWIPfile & "]Sheet1'!$C$2:$U$12,11,FALSE)"
.Value = .Value
End With

n8Mills
11-21-2008, 01:25 PM
To georgiboy: Yes, it's the range that gives me trouble.

To xId: You just like talkin' smart. Thanks for the help, it's perfect!

n8Mills
11-23-2008, 07:36 PM
I'm sorry to report that after testing xId's solution does not work. All cells after the first filtered selection are filled with the first cell's value, not their own true values.

:(