CODEWITHSUNDEEP
excelvba
CuriousGeorge
CuriousGeorge

Reputation: 49

Return value from function to Cell

I am trying to return a value from a function and not sure how to go about this. The location I want to return it to is a merged cell from "G22:I26" on worksheets(1) and the original function is being done on worksheets(3).

Sub CountBlank()
    Dim xxEmpty As Long
    Dim LastRow As Long
      LastRow = Worksheets(3).Cells(Rows.Count, 3).End(xlUp).Row
      xxEmpty = Worksheets(3).CountBlank(Range("CY2:CY" & LastRow))
      Return xxEmpty = Worksheets(1).Cells("G22:I26") 'Syntax Error 

End Sub

Upvotes: 0

Views: 102

Answers (1)

T.M.
T.M.

Reputation: 9938

How to return a function result

Basically you return results to a function and not to a sub procedure (leave aside direct changes to parameters passed by reference - ByRef). Return is no valid method to return such function results as in other programming languages, you have to assign a value to the function itself.

Furthermore you have to use the Worksheetfunction.CountBlank function (or Application.CountBlank), CountBlank itself is no function or method related to a worksheet as you tried by xxEmpty = Worksheets(3).CountBlank(…).

Eventually avoid to overload procedures with existing names, so call your function e.g. getBlanks(), but not CountBlank().

a) Example call showing the result in VB Editor's immediate window:

Sub Test()
    Dim LastRow As Long
    LastRow = Worksheets(3).Cells(Rows.Count, 3).End(xlUp).Row
    Debug.Print getBlanks(Worksheets(3).Range("CY2:CY" & LastRow)) & " blank rows counted"
End Sub

Function getBlanks(rng As Range) As Long
' Help:
'      Return xxEmpty = Worksheets(1).Cells("G22:I26") 'Syntax Error
     getBlanks = WorksheetFunction.CountBlank(rng)
End Function

If you want to return the function result directly to a cell, you could either just enter the function name choosing a precise range as argument:

=getBlanks(CY2:CY4711)

or you'd have to modify the function a little bit if you want to count only blanks from say row 2 up to the last entry.

Upvotes: 3

Related Questions