Type Mismatch in Vlookup?
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) |
Type Mismatch in Vlookup?
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) |
All times are GMT +1. The time now is 10:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com