CODEWITHSUNDEEP
excelvba
user223276
user223276

Reputation: 3

How can I calculate a formula in Excel VBA and put its value in a variable? For example I want to set x equal to COUNTA(Sheet1!A:A)

How can I calculate a formula in Excel VBA and put its value in a variable?
For example I want to set x equal to COUNTA(Sheet1!A:A)

When I write x=WorksheetFunction.COUNTA(Sheet1!A:A) I get the error

"Expected: list separator or )"

Upvotes: 0

Views: 97

Answers (2)

user11246173
user11246173

Reputation:

You aren't passing the range reference to the worksheet function properly. You need to use a VBA style reference not a worksheet style reference.

'VBA style using worksheet codename
x = WorksheetFunction.COUNTA(Sheet1.Range("A:A"))

'VBA style using worksheet name
 x = WorksheetFunction.COUNTA(Worksheets("Sheet1").Range("A:A"))

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

The function needs a Range, so:

Sub kount()
    Dim r As Range, x As Long

    Set r = Sheets("Sheet1").Range("A:A")

    With Application.WorksheetFunction
        x = .CountA(r)
    End With
End Sub

Upvotes: 0

Related Questions