0

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
Improve this question
5
  • 2
    When using Find, always specify the What, LookAt, and LookIn parameters. See the remarks in the docs. Commented Oct 5, 2022 at 19:54
  • You are passing worksheet instead of range to the function and then trying to set it? Commented Oct 5, 2022 at 19:55
  • @BigBen how do a specific a string value in cells? Commented Oct 5, 2022 at 19:57
  • @SiddharthRout Yes, I want the function to run through the worksheet. Do you mean I need to define the range outside of the function? So move Set sys_Rng = sht.Cells.Find("System Number(s)") to be before I call the function ` system_position = sys_position(ws)` Commented Oct 5, 2022 at 19:58
  • Posted an answer. See if that is what you want? Commented Oct 5, 2022 at 20:02

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

17 Comments

I get an error saying object required at Set Cell = ws.Cells.Find("Axsun Part Number", _ After:=ws.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False)
I just edited the post. You may have to refresh the page to see the edit. Also you are not using the exact code.. I had typed Set aCell = and not Set Cell =
I changed it to Dim Cell As Range does the a effect the script?
It did not work, I not get an error saying Expected sub, function, or property
@SiddharthRout - FYI previously... stackoverflow.com/a/73912301/478884
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.