How do I pass an argument from a subroutine to a function in VBA?

Question

I'm trying to look for values to create a final ticket number for a ticket reconciliation process. This is what should happen:

subroutine looks for a value in cell "Gx"

• if it finds a value
  • pass value to function to strip out letters, convert to a number, pass back to subroutine to place in cell "Ax"
• if there is no value
  • pass value of "Cx" to function etc.

This loops through the number cells I have in my worksheet based on the number of rows filled in a separate column.

The function works fine by itself in the worksheet, but when I pass it a value from the subroutine column A fills up with the number of the row ie. A37=37, A8=8. I don't think I'm passing the argument correctly to the function, but I'm not certain. Here's the code for the subroutine and the function:

    Sub final_ticket_number()
    Dim rw As Integer
    Dim i As Integer

    'header label
    Range("A1").Value = "Final Ticket #"

    'set number of rows for loop
    With Worksheets(1)
        rw = .Range("B2").End(xlDown).Row
    End With

    'check col G for empty, use col C as backup
    For i = 2 To rw
        If Not IsEmpty(Cells(i, "G")) Then
            'strip out letters in col G, place in col A
            Cells(i, "A").Value = getNumeric("G" & i)
        Else
            'strip out letters in col C, place in col A
            Cells(i, "A").Value = getNumeric("C" & i)
        End If
    Next i
    
    End Sub

    Function getNumeric(cellRef As String) As Long 'remove letters from ticket numbers
    Dim stringLength As Integer
    Dim i As Byte
    Dim Result As String
    stringLength = Len(cellRef)

    'loops through each character in a cell to evaluate if number or not
    For i = 1 To stringLength
        If IsNumeric(Mid(cellRef, i, 1)) Then
            Result = Result & Mid(cellRef, i, 1)
        End If
    Next i

    'convert remaining characters to number
    getNumeric = CLng(Result)

    End Function

What am I missing?

Answer 1

As I understand it, the only thing that is wrong is your Len (cellRef), here you are only passing the range and not his value. See how I did it, I had to specify the spreadsheet, do the same that will work.

Use debug.print to see the outputs of the variables. Write in the code "debug.print XvariableX" and in the immediate check (Ctrl + G) you see the value assigned to the variable. good luck.

Sub final_ticket_number()
Dim rw As Integer
Dim i As Integer

Range("A1").Value = "Final Ticket #"

With Worksheets(1)
    rw = .Range("B2").End(xlDown).Row
End With

For i = 2 To rw
    If Not IsEmpty(Cells(i, "G")) Then
        Cells(i, "A").Value = getNumeric("G" & i)
    Else
        Cells(i, "A").Value = getNumeric("C" & i)
    End If
Next i
End Sub

Function getNumeric(cellRef As String) As Long 'remove letters from ticket numbers
Dim stringLength As Integer
Dim i As Byte
Dim Result As String

Dim Wrs As String
Wrk = ActiveWorkbook.Name
Workbooks(Wrk).Activate
Wrs = ActiveSheet.Name
stringLength = Len(Workbooks(Wrk).Worksheets(Wrs).Range(cellRef))

For i = 1 To stringLength
    If IsNumeric(Mid(Workbooks(Wrk).Worksheets(Wrs).Range(cellRef), i, 1)) Then
        Result = Result & Mid(Workbooks(Wrk).Worksheets(Wrs).Range(cellRef), i, 1)
    End If
Next i

getNumeric = CLng(Result)
End Function