Run-Time Error 13 Type Mismatch
I am relatively new to VBA. I am trying to create an index which opens a second workbook table to retrieve a value. I keep getting Type mismatch on the index function, which I believe excel does like the data format. I set the variable to be variants to no avail. What am I doing wrong?
Dim Filename As String
Dim destSheet As Worksheet
Dim Column As Variant
Dim Row_S As Variant
Dim SID As Variant
Dim Data_S As Variant
Filename = "C:\Users\Documents\UBPR_Page12"
Tabname = "Data"
Set SID = Application.Sheets("Data_Table").Range("D11")
Set UBPRE = Application.Sheets("Data_Table").Range("D10")
controlFile = ActiveWorkbook.Name
Workbooks.Open Filename:=Filename
Sheets("Data").Select
Column = Sheets("Data").Rows(1).Find(what:=UBPRE, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Column
Row_S = Sheets("Data").Columns(2).Find(what:=SID, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Row
Data_S = WorksheetFunction.Index(Sheets("Data").Range("C2:AZ20000").Select, ("Row_S"), ("Column"), 1)
Thanks!
Run-Time Error 13 Type Mismatch
Hi Beamer607,
Remove the .Select from the range
Remove the double quotes from Row_S & Column
And you should be good to go.
'Data_S = WorksheetFunction.Index(Sheets("Data").Range("C2:AZ20000").Select, ("Row_S"), ("Column"), 1)
'Data_S = WorksheetFunction.Index(Sheets("Data").Range("C2:AZ20000"), Row_S, Column, 1)