How to call function from sub procedure

[sal11235]

Hello boys,

I created a VBA function to count instances of a string across sheets. Here it is

Private Function FindString(c01)
For Each sht In ActiveWorkbook.Sheets
If Not sht.Name = "LIST" Then
x = WorksheetFunction.CountIf(sht.UsedRange, c01)
xcount = x + xcount
End If
Next
FindString = xcount
End Function

This is all fine, but I now need this to be turned into a subroutine where I can execute the procedure and it will fill all the cells I want with the result of the FindString function above. I just can't figure out how to bring the function into a procedure. This is how far I have got...

(I have a list of values in a column which I select, then all the values adjacent to the column are filled with the result of the FindString procedure)

Public Sub FindString()

FirstRow = Range(Left(ActiveCell.Address(RowAbsolute, ColumnAbsolute), 1) & "1").Offset(1, 0)
LastRow = Range(Left(ActiveCell.Address(RowAbsolute, ColumnAbsolute), 1) & Rows.Count).End(xlUp).Row

For i = FirstRow To LastRow

Cells(i, ActiveCell.Column).Offset(0, 1) = FindString

Next

End Sub

which is obviously wrong as I'm just writing out code the way I want to it to perform in my head!

Any ideas???.... will be very appreciated with big cyber hugs!

Thank yoouuuu

 

[Andrew Poulsom]

Welcome to MrExcel.

Your function has an argument so you need to pass it when calling it. Try:

Cells(i, ActiveCell.Column).Offset(0, 1).Value = FindString(Cells(i, ActiveCell.Column).Value)

 

[GTO]

Hi there,

I don't believe you'll want to call the function for each cell, as this slows processing. Maybe:

Public Sub FindStrings()
Dim FirstRow As Long, LastRow As Long, i As Long, lRet As Long
    
    lRet = FindString
    
    FirstRow = ActiveCell.Offset(1).Row
    LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
    
    For i = FirstRow To LastRow
        Cells(i, ActiveCell.Column).Value = lRet
    Next
End Sub

Hope that helps,

Mark

 

[GTO]

ACK! I missed the arg

 

[sal11235]

Ha! Thank you for the solution Andrew that has made my day :=)))))))

And thank you GTO for simplifying my code, really helpful for my learning

Two massive hugs from London :=)

 

[sal11235]

One last thing.. (of course, its always one last thing isnt it?)

seriously, the last thing ;=p..since I have this wealth of MVP power at my disposal and I could do with the training!

just from what you said GTO, about having an argument, I went over it and took the argument out but this doesn't seem to work.

Public Sub FindStrings()
Dim FirstRow As Long, LastRow As Long, i As Long
        
    FirstRow = 2
    LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
        For i = FirstRow To LastRow
        Cells(i, ActiveCell.Column).Offset(0, 1) = FindString
        
    Next
End Sub

Private Function FindString()
Dim c01 As Range
For Each sht In ActiveWorkbook.Sheets
If Not sht.Name = "LIST" Then
x = WorksheetFunction.CountIf(sht.UsedRange, c01)
xcount = x + xcount
End If
Next
FindString = xcount
End Function

I thought it might work as I'm just declaring the c01 as a range rather than an argument..But the code stops at the countif function. Is it telling me that I ALWAYS have to have c01 declared as an argument in the function?

If either of you could clear this up that would be cool...

 

[Andrew Poulsom]

You haven't assigned anything to c01. It's always better to pass everything a function needs as an argument, so stick with what you've got.

 

[sal11235]

really appreciate that, the textbooks that I've read on excel VBA don't have this kind of "best practice" advice so thank you, will take on board for the future :=)

 

[RoryA]

It's also best practice to give your variables/arguments/functions/subs meaningful names rather than 'c01' which tells you nothing at all.