COUNTA and INDIRECT in VBA for assigning variables

Question

I have a variable num_d which I've declared as an Integer. Its value should be the result of this worksheet formula:

=COUNTA(INDIRECT("Sheet1!A"& 2*),INDIRECT("Sheet1!B"& 2*),INDIRECT("Sheet1!C"& 2*))

*2 is just an example here - this value could be anything.

I'd like to avoid populating a cell with the result of this formula, and instead assign num_d directly.

How can I translate the worksheet function into VBA?

Thanks in advance.

Answer 1

Use Worksheet and Range objects:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

Dim num_d As Long
num_d = WorksheetFunction.CountA(ws.Range("A2"), ws.Range("B2"), ws.Range("C2"))

If the row number is a variable, then just concatenate it:

Dim rowNum As Long
rowNum = 2

num_d = WorksheetFunction.CountA(ws.Range("A" & rowNum), _
                                 ws.Range("B" & rowNum), _
                                 ws.Range("C" & rowNum))