VBA Function - beginner

Hi

I just begun using VBA

I wrote a function to extract a text item from a single row or single column of data entries. The text item chosesn is a numbered item and all non text is ignored. So if the list consisted of "AA","BB"," ","DD","EE" say then the input agruments (3,list) to the function would generate the result DD and similarly (2,list) would generate the result BB.

I wrote the function "nonzerotext" and the test harness subroutine "showtext" to display the output in a message box. This worked.

However, the function does not work alone in the spreadsheet. Any ideas.

See function and test harness below.

Private Function nonzerotext(listpos As Integer, List As Range) As String

Dim firstrow As Integer
Dim lastrow As Integer
Dim firstcolumn As Integer
Dim rowcounter As Integer
Dim numdetected As Integer

firstrow = List.Row

lastrow = List.Row + List.Count - 1

firstcolumn = List.Column

numdetected = 0

For rowcounter = firstrow To lastrow
    
    If WorksheetFunction.IsText(List([rowcounter], [firstcolumn])) Then
        numdetected = numdetected + 1
        If numdetected = listpos Then
            nonzerotext = List([rowcounter], [firstcolumn])
        End If
    End If

Next rowcounter

End Function


Sub showtext()
 
 Dim textis As String
    
    textis = nonzerotext(5, Range("A1:A10"))
        
    MsgBox textis
    
End Sub

Hi steve427,
welcome to the forum

Function nonzerotext(listpos As Integer, List As Range) As String

no need for private as it "Indicates that the Function procedure is accessible only to other procedures in the module where it is declared." VBA help
and to return the value in one sting

nonzerotext = List([rowcounter], [firstcolumn]) & nonzerotext

just clicked what you are doing. the number "numdetected" needs to progress on every loop
the

 For rowcounter = firstrow To lastrow
        numdetected = numdetected + 1
        If WorksheetFunction.IsText(List(rowcounter, firstcolumn)) Then
            If numdetected = listpos Then
                nonzerotext = List(rowcounter, firstcolumn)
            End If
        End If

    Next rowcounter

I don't quite see the need for the loop and counter using your example; however, the code below solves the immediate problem.

    If WorksheetFunction.IsText(List(listpos, firstcolumn)) Then
        nonzerotext = List(listpos, firstcolumn)
    Else
        nonzerotext = "no text in range " & List.Address(0, 0)
    End If

Your original code did not work with the example because you had an empty cell in the range but your counter is not adjusted to account for it so that after that cell the count is off by one...