Hello,
Trying to use result of a "Set" statement as part of
array in a VLookup. Set statement returns the correct
value, but I get a 'type mismatch' error in the VLookup.
See below. Why doesn't this work and how do I fix?
Thx!,LJ
PS:sorry for long text below; it's the only way I can
keep it straight.
----------------------

Sub GetStrategy()

'Sets # of loops based on # of data items--works OK
For x = 1 To Range("Data_NumStrategy")
Range("Data_DebtIndex").Value = x

'Sets Loan as the name of each data item--works OK
Dim Loan As Object
Set Loan = Application.WorksheetFunction.Index(Range
("Data_SheetListCopy"), x, 1)

'Set Value of "Analysis_Balance" by looking up the
'corresponding value on Sheet(Loan), where Loan is
'the name returned in the step above--this gives me
'the Type Mismatch error.
Range("Analysis_Balance").Value =
Application.WorksheetFunction.VLookup("Analysis_Da te",
Sheets(Loan).Range("A1:G500"), 3, False)

Hi
Variable Loan is probably the cause since you define it as Object and
Sheets() expects a string in your VLookup formula.
So I would suggest you amend with:
Dim Loan

HTH
Cordially
Pascal

"LJones" a écrit dans le message de
...
Hello,
Trying to use result of a "Set" statement as part of
array in a VLookup. Set statement returns the correct
value, but I get a 'type mismatch' error in the VLookup.
See below. Why doesn't this work and how do I fix?
Thx!,LJ
PS:sorry for long text below; it's the only way I can
keep it straight.
----------------------

Sub GetStrategy()

'Sets # of loops based on # of data items--works OK
For x = 1 To Range("Data_NumStrategy")
Range("Data_DebtIndex").Value = x

'Sets Loan as the name of each data item--works OK
Dim Loan As Object
Set Loan = Application.WorksheetFunction.Index(Range
("Data_SheetListCopy"), x, 1)

'Set Value of "Analysis_Balance" by looking up the
'corresponding value on Sheet(Loan), where Loan is
'the name returned in the step above--this gives me
'the Type Mismatch error.
Range("Analysis_Balance").Value =
Application.WorksheetFunction.VLookup("Analysis_Da te",
Sheets(Loan).Range("A1:G500"), 3, False)