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?
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?