SUMPRODUCT(1/COUNTIF syntax error

[Saab95]

Hi,

Why formulas in vba are not as simple in cells

I have a syntax error but why , andtried with double or single quotes. No change.


Sub TEST99()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Master" Then
lastRow = ws.Range("AT2" & Rows.Count).End(xlUp).Row
ws.Range("AV2").Formula = ""=SUMPRODUCT(1/COUNTIF(("AT2:AT" & lastRow),("AT2:AT" & lastRow)))""
End If
Next ws
End Sub

Thank you ?

 

[Joe4]

All the quotes can be a bit confusing. There is an easy way to do this - let Excel do the work for you!
Turn on your Macro Recorder, and record yourself manually entering the formula directly on your sheet.
Then turn off the Macro Recorder, and view the code your just recorded. You will see the formula that you need, and you can copy/paste it to your other VBA code.

 

[Saab95]

All the quotes can be a bit confusing. There is an easy way to do this - let Excel do the work for you!
Turn on your Macro Recorder, and record yourself manually entering the formula directly on your sheet.
Then turn off the Macro Recorder, and view the code your just recorded. You will see the formula that you need, and you can copy/paste it to your other VBA code.

Thanks, never thought about that. That's pretty smart.

 

[Joe4]

You are welcome!

 

[FormR]

Hi, I concur with Joe, for your particular scenario incorporating the lastrow variable you can try.

ws.Range("AV2").Formula = "=SUMPRODUCT(1/COUNTIF(AT2:AT" & lastRow & ",AT2:AT" & lastRow & "))"