CountA Macro not working :(

[tatendatiffany]

I am trying to turn this counta query into a macro does anyone have an idea of how to do it:

=COUNTA(TABF10!F:F)-2

and also this sum into a macro

=SUMPRODUCT(KTPT81T!G:G)

this is what i had done so far for the counta macro, am I on the right path:

Sub CountA()
Dim x As Long
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select
If ws.Name = "TABF10" Then
x = ws.Range("G" & Rows.Count).End(xlUp).Row
If x > 100 Then x = 100
Sheets("Index").Range("Z2") = Application.WorksheetFunction.CountA(Range("G5" & "G50"))
x = 0
End If
Next ws
End Sub

[westconn1]

are you trying to put the formula in the cell? this will make it dynamic and it will update if the value in the range it refers to changes, or to insert the result of the formula into the cell, this would be static, the result will not change when any values in range G5:G50 change

for the former, you should specify it is a formula like

Sheets("Index").Range("Z2").formula = "=counta(g5:g50)"

check result is correct

[tatendatiffany]

I need it to be dynamic and I want the result to be displayed in sheet 1 column y2, I have modified this code

Sub COUNTA()
With ThisWorkbook.Sheets("TABF10").Range("F:F")
    With Range(.Cells(1, 1), .Cells(.Rows.COUNT, 0).End(xlUp))
        Sheets("Index").Range("Z2").FormulaR1C1 = "=COUNTA(" & .Address(, , xlR1C1) & ")"
        End With
        End With
End Sub

but the result is coming back as 40 instead of the right figure of 43

[gep13]

Hello,

This question really relates to developing for an Office Application, as a result, I am going to move it to the Office Development Forum.

Gary

[westconn1]

but the result is coming back as 40 instead of the right figure of 43

i can not see that

With Range(.Cells(1, 1), .Cells(.Rows.COUNT, 0).End(xlUp))

this should error, the range is on the activesheet, but the cells are on tabf10 sheet, also i do not believe 0 would be a valid value

[tatendatiffany]

it is not erroring at all which is weird. I probably made a mistake somewhere or the whole code is messed up, let me double check and see if i should change it completely,


Thanks