Expected Array Error When No Array Is Present

Question

I have these two functions that are checking for a column index for the specific header assigned to each. For the first function, sys_position there is no problem and the message box appears, however in the second function partnum_position the expected array error is thrown. The only difference that I can see of the two is the specific header that is assigned in `Set sys_Rng = sht.Cells.Find("System Number"). Does anyone have any idea why this is happening?

Public Sub Main()
Dim wb As Workbook, ws As Worksheet, system_position As Integer, partnum_position As Integer

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

 system_position = sys_position(ws)
 partnum_position = partnum_position(ws)

 End Sub 

---

 Function sys_position(sys_Rng As Variant) As Integer 
Dim sht As Worksheet
Set sht = Worksheets("Sheet1") 'define start sheet
Set sys_Rng = sht.Cells.Find("System Number(s)") 'set values to look for

    If Not sys_Rng Is Nothing Then 'if found then do this oporation
       MsgBox ("System Number Column is " & sys_Rng.Column)
    Else
       MsgBox ("No Header Found.")
    End If
Set sys_Rng = Nothing ' clear variable
Set sht = Nothing ' clear variable
End Function

---

Function partnum_position(partnum_Rng As Variant) As Integer 
Dim sht As Worksheet
Set sht = Worksheets("Sheet1") 'define start sheet
Set partnum_Rng = sht.Cells.Find("Part Number")

    If Not partnum_Rng Is Nothing Then
       MsgBox ("Part Number Column is " & partnum_Rng.Column)
    Else
       MsgBox ("No " & partnum_Rng & " Header Found.")
    End If
Set partnum_Rng = Nothing ' clear variable
Set sht = Nothing ' clear variable
End Function

EDIT

 Public Sub Main()
 Dim wb As Workbook, ws As Worksheet, i As Range, dict As Object, system_position As Long, partnum_position As Long
Dim wbSrc As Workbook

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

Call position 
 End Sub 

---

 Sub position() 

 Dim sht As Worksheet
 Dim sys_Rng As Range, partnum_Rng As Range, syswaivernum As Range, partnumber As Range

Set sht = Worksheets("Sheet1") 'define start sheet
Set sys_Rng = sht.Cells.Find("System Waiver Number(s)") 'set values to look for
Set partnum_Rng = sht.Cells.Find("Axsun Part Number")

If Not sys_Rng Is Nothing Then 'if found then do this oporation
    MsgBox ("System Waiver Number Column is " & sys_Rng.Column)
Else
    MsgBox ("No Header Found.")
End If

If Not partnum_Rng Is Nothing Then
    MsgBox ("Axsun Part Number Column is " & partnum_Rng.Column)
Else
    MsgBox ("No " & partnum_Rng & " Header Found.")
End If

Set sys_Rng = Nothing ' clear variable
Set partnum_Rng = Nothing ' clear variable
Set sht = Nothing ' clear variable
 End Sub

Answer 1

1. When working with rows, it is advisable to define them as Long rather than Integer. Excel 2007+ has 1048576 rows so we should always declare the variable which will hold the row value as Long instead of Integer. You will get an Overflow error if you try to store say 1048576 to an Integer variable.

2. When using .Find, use all the necessary parameters.

Is this what you are trying?

Option Explicit

Dim wb As Workbook, ws As Worksheet

Public Sub Main()
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")

    sys_position
    partnum_position
End Sub
 
Private Sub sys_position()
    Dim aCell As Range
    Set aCell = ws.Cells.Find(What:="System Waiver Number(s)", _
        After:=ws.Range("A1"), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)

    If Not aCell Is Nothing Then
        MsgBox ("System Waiver Number Column is " & aCell.Column)
    Else
        MsgBox ("No Header Found.")
    End If
End Sub 

Private Sub partnum_position()
    Dim aCell As Range
    Set aCell = ws.Cells.Find("Axsun Part Number", _
        After:=ws.Range("A1"), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)

    If Not aCell Is Nothing Then
        MsgBox ("Axsun Part Number Column is " & aCell.Column)
    Else
        MsgBox ("No Header Found.")
    End If
End Sub 

EDIT

3.

Considering that you are not returning any value, it is better to use a Sub procedure and not Function procedure. Your code can be drastically reduced to

Option Explicit

Public Sub Main()
    FindPosition "System Waiver Number(s)", "System Waiver Number Column is "
    FindPosition "Axsun Part Number", "Axsun Part Number Column is "
End Sub
 
Private Sub FindPosition(SearchText As String, msg As String)
    Dim wb As Workbook, ws As Worksheet
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")
    
    Dim aCell As Range
    
    Set aCell = ws.Cells.Find(What:=SearchText, _
        After:=ws.Range("A1"), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)

    If Not aCell Is Nothing Then
        MsgBox (msg & aCell.Column)
    Else
        MsgBox ("No " & SearchText & " Header Found.")
    End If
End Sub