VBA - Index Match Functions - when calling getting "ByRef argument type mismatch"

[kpaull]

When running this code below the VBE highlights ImportSheetRange in this line: "ReturnValue = Indexmatch(strSKUEntered, ImportSheetRange, ExportSheetRange)" and spits the error "ByRef argument type mismatch".

Any help.

Running Excel 365 / Window 7.

Thanks!

Option Explicit
Sub Macro1()
    
    Dim kpResults, datExportDate, strImportDate1, strImportDate2, strSKUEntered As String
    Dim ImportSheetRange, ExportSheetRange As Range
    Dim ReturnValue As Variant
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With


'Prompt for SKU - provide default value as a guide/mask and allow to click on SKU in spreadsheet instead of typing in value


        strSKUEntered = Application.InputBox("Enter a SKU to process", , "242738-797-REM", , , , Type:=2)
        
'Find SKU on Export Worksheet, write -3 columns cell value (Date) to a variable
'Worksheet function formula in SS = =INDEX(Import!$A$2:$O$250000,SMALL(IF(Import!$A:$A=$D$452,ROW($A:$A)),ROW(1:1))-1,15)
  
        Set ImportSheetRange = ActiveWorkbook.Sheets("Import").Range("A2:P100000")
        Set ExportSheetRange = ActiveWorkbook.Sheets("Export").Range("A2:E100000")
  
        ReturnValue = Indexmatch(strSKUEntered, ImportSheetRange, ExportSheetRange)
               
        Debug.Print ReturnValue
          
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
    
    
End Sub




Function Indexmatch(LookFor As String, SearchRng As Range, ReturnRng As Range) As Variant
    
    Application.Volatile
    
    Indexmatch = WorksheetFunction.Index(ReturnRng, WorksheetFunction.Match(LookFor, SearchRng, 0), 0)
    
End Function

 

[jimrward]

When declaring multiple variables on a single line on the last one will have the stated type all others will be type variant unless explicated declared

So importsheetrange will be variant not range

Same with your string variables

 

[Marcelo Branco]

The second parameter of Match, lookup_array (SearchRng in the Indexmatch function), must be a one-dimensional range or array. It seems the code is calling the function using a two-dimensional range, i.e., ImportSheetRange (A2:P100000)

Also the last parameter, 0, doesn't seem correct - in this case i think you should use a column number

M.

 

[kpaull]

The second parameter of Match, lookup_array (SearchRng in the Indexmatch function), must be a one-dimensional range or array. It seems the code is calling the function using a two-dimensional range, i.e., ImportSheetRange (A2:P100000)

Also, the last parameter, 0, doesn't seem correct - in this case i think you should use a column number

M.

Thank you, Marcelo! I made the adjustments you suggested in my code below, marked in Red text. Still get the same error on arrImportSheet array.

Option Explicit
Sub Macro1()
    
    Dim kpResults, datExportDate, strImportDate1, strImportDate2, strSKUEntered As String
    Dim ExportSheetRange As Range
    Dim arrImportSheet(10) As String
    Dim ReturnValue As Variant
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With


'Prompt for SKU - provide default value as a guide/mask and allow to click on SKU in spreadsheet instead of typing in value


        strSKUEntered = Application.InputBox("Enter a SKU to process", , "242738-797-REM", , , , Type:=2)
        
'Find SKU on Export Worksheet, write -3 columns cell value (Date) to a variable
'Worksheet function formula in SS = =INDEX(Import!$A$2:$O$250000,SMALL(IF(Import!$A:$A=$D$452,ROW($A:$A)),ROW(1:1))-1,15)
  
            
        Set ExportSheetRange = ActiveWorkbook.Sheets("Export").Range("A2:E100000")
  
        ReturnValue = Indexmatch(strSKUEntered, arrImportSheet, ExportSheetRange)
               
        Debug.Print ReturnValue
          
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
    
    
End Sub


Function Indexmatch(LookFor As String, SearchRng As Range, ReturnRng As Range) As Variant
    
    Application.Volatile
    
    Indexmatch = WorksheetFunction.Index(ReturnRng, WorksheetFunction.Match(LookFor, SearchRng, 1), 0)
      
End Function

 

[kpaull]

Thank you for your quick reply jimrward!

As I understand it "Function Indexmatch(LookFor As String, SearchRng As Range, ReturnRng As Range) As Variant" means...

The return value will be in Indexmatcxh and it will be a Variant.

The variables mentioned are as mentioned.

I could be mistaken.

 

[kpaull]

When declaring multiple variables on a single line on the last one will have the stated type all others will be type variant unless explicated declared

So importsheetrange will be variant not range

Same with your string variables

Thank you for your quick reply jimrward!

As I understand it "Function Indexmatch(LookFor As String, SearchRng As Range, ReturnRng As Range) As Variant" means...

The return value will be in Indexmatcxh and it will be a Variant.

The variables mentioned are as mentioned.

I could be mistaken.