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.

Please Login or Register  to view this content.

Hi steve427,
welcome to the forum

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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...