Error "Invalid Procedure Call or Argument" When Calling a Function-CodePudding

I am getting the error of "invalid procedure call or argument" at line copy = Application.WorksheetFunction.CountIfs(parameter_Rng, parametername, routing_Rng, routingname). I did add this new line of Dim startrow as Long startrow = getrowindex(sysnum, "SD Revision", "Test-Config")

But that is a very common line that I have used multiple times throughout my code and had no issues with the function returning an integer variable. This is what my code currently looks like:

Global Parameter As Long, RoutingStep As Long, parameter_Rng As Range, routing_Rng As Range 
Public Sub Main() 

Dim wb As Workbook, ws As Worksheet, dict As Object, sysrow As Integer, sysnum As String, wsName As String, wbSrc As Workbook, SDtab As Worksheet 

Dim spectyp As Long, specmin As Long, specmax As Long\
Dim cell As Range, syswaiver As Long, axsunpart As Long
Dim wavelength_col As Long, sweeprate_col As Long, coherence_length_col As Long, power_col As Long, kclock_depth_col As Long, kclock_count_col As Long 
Dim wavelength_value As Double, coherencelength_value As Double\
Dim wavelength_rowindex As Double, wavelength_rowindex_1 As Double, coherencelength_rowindex As Double

Dim startrow As Long  

Parameter = getcolumnindex(SDtab, "PARAMETER") 
startrow = getrowindex(sysnum, "SD Revision", "Test-Config-OCT") 
RoutingStep = getcolumnindex(SDtab, "Routing Step 1") 

Set parameter_Rng = SDtab.Range(SDtab.Cells(startrow, Parameter), SDtab.Cells(SDtab.Rows.Count, Parameter).End(xlUp)) 

Set routing_Rng = SDtab.Range(SDtab.Cells(startrow, RoutingStep), SDtab.Cells(ws.Rows.Count, RoutingStep).End(xlUp)) 


End Sub

Function getrowindex(WDnum As String, parametername As String, routingname As String, Optional partialFirst As Boolean = False, Optional partialSecond As Boolean = False)

Dim ws As Worksheet, rowname As Range, addr As String, copy As Long, Output As Integer 
Set ws = ThisWorkbook.Worksheets(WDnum) 

Set rowname = ws.Columns(Parameter).Find(What:=parametername, Lookat:=IIf(partialFirst, xlPart, xlWhole), LookIn:=xlFormulas, MatchCase:=True) 

copy = Application.WorksheetFunction.CountIfs(parameter_Rng, parametername, routing_Rng, routingname) 

If copy > 1 Then
    Output = MsgBox("This row combination " & parametername & " and " & routingname & " appears in multiple lines. Check before running again.", vbCritical) 
    Stop 
Else 

If Not rowname Is Nothing Then 
        addr = rowname.Address 
        If partialSecond Then routingname = "*" & routingname & "*" 
    Do 
        If rowname.Offset(0, 1).Value Like routingname Then 
            getrowindex = rowname.Row 
            Exit Do 
        End If 

        Set rowname = ws.Columns(Parameter).FindNext(after:=rowname) 
    Loop While rowname.Address <> addr 
ElseIf rowname Is Nothing Then 
    Output = MsgBox(parametername & " row could not be found. Check before running again.", vbCritical) 

    Stop 
End If 
End If 

End Function

CodePudding user response：

You can rewrite getrowindex such that it doesn't rely on those two Global ranges:

Function getrowindex(WDnum As String, parametername As String, routingname As String, _
            Optional partialFirst As Boolean = False, Optional partialSecond As Boolean = False)

    Dim ws As Worksheet, rowname As Range, addr As String, copy As Long, Output As Integer
    Dim rngParam As Range, rngRouting As Range
    
    Set ws = ThisWorkbook.Worksheets(WDnum)
    
    Set rowname = ws.Columns(parameter).Find(What:=parametername, Lookat:=IIf(partialFirst, xlPart, xlWhole), _
                                             LookIn:=xlFormulas, MatchCase:=True)
    
    If Not rowname Is Nothing Then
        addr = rowname.Address
        If partialSecond Then routingname = "*" & routingname & "*"
        Do
            If rowname.EntireRow.Columns(RoutingStep).Value Like routingname Then
                If rngParam Is Nothing Then 'first match?  Check for additional matches...
                    Set rngParam = ws.Range(rowname, ws.Cells(Rows.Count, parameter))
                    Set rngRouting = rngParam.EntireRow.Columns(RoutingStep)
                    If Application.WorksheetFunction.CountIfs(rngParam, parametername, rngRouting, routingname) > 1 Then
                        MsgBox "This row combination " & parametername & " and " & routingname & _
                               " appears in multiple lines. Check before running again.", vbCritical
                        Stop
                    End If
                End If
                
                getrowindex = rowname.Row
                Exit Do
            End If
    
            Set rowname = ws.Columns(parameter).FindNext(after:=rowname)
        Loop While rowname.Address <> addr
    Else
        MsgBox parametername & " row could not be found. Check before running again.", vbCritical
        Stop
    End If
    
End Function

Your entire code is a little convoluted though - your use of Global variables will make this very difficult to maintain and/or troubleshoot...