Problem with VBA Formula/Quotes

[Sassbearilla]

I know this is going to be a simple answer, but I just can't seem to get to it.

What I want is to translate this Excel formula to VBA:

=SUMPRODUCT(F5:F18/COUNTIF(F5:F18,F5:F18))

I know it's gotta be something like:

Range("F" & ReportLR + 1) = WorksheetFunction.SumProduct("F5:F" & ReportLR / WorksheetFunction.CountIf("F5:F" & ReportLR, "F5:F" & ReportLR))

Hopefully this is clear enough. Any help is very appreciated!!

 

[Fluff]

Hi & welcome to MrExcel.
Try it like

Range("F" & reportlr + 1) = Evaluate("SumProduct(F5:F" & reportlr & " / CountIf(F5:F" & reportlr & ", F5:F" & reportlr & "))")

 

[Sassbearilla]

Hi & welcome to MrExcel.
Try it like

Range("F" & reportlr + 1) = Evaluate("SumProduct(F5:F" & reportlr & " / CountIf(F5:F" & reportlr & ", F5:F" & reportlr & "))")

Yup, I knew it was simple... ugh. Thank you!

I've been browsing Mr. Excel for a while, gotten a lot of good information here, figured I needed to join finally lol.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Sassbearilla]

@Fluff Can I reopen this? I think I need to actually input a formula2 formula into the cell, rather than use VBA, but I'm not sure how to accomplish that either.

Pseudocode would look something like this:

Range("F" & ReportLR + 1).Formula2 = "=SUMPRODUCT(F5:ReportLR/COUNTIF(F5:ReportLR,F5:ReportLR))"

I'm just not sure how to combine the VBA variable and the Excel formula. Thoughts?

 

[Sassbearilla]

Figured it out. Just didn't have my quotes in the right place. *facepalm*

.Range("F" & ReportLR + 1).Formula2 = "=SUMPRODUCT(F5:F" & ReportLR & "/COUNTIF(F5:F" & ReportLR & ",F5:F" & ReportLR & "))"

 

[Fluff]

Glad you sorted it & thanks for the feedback.