Solved: Convert formula to vba code

[Shazam]

Hi everyone!


I'm trying to get this into a vba code but some reason I'm not doing it correctly.

Here is the formula it sum unique values.

=SUMPRODUCT(--(B3:B40<>""),--(MATCH(F3:F40&N3:N40,F3:F40&N3:N40,0)=ROW(INDEX(F3:F40,0,0))-ROW($A$3)+1),N3:N40)


I got this far but no luck. Any help?



Sub Sum()
Dim maxrow As Long, Db As String, frmla As String
maxrow = Range("N65536").End(xlUp).Row - 1
Db = Chr(34)
frmla = "=SUMPRODUCT(--(B3:B" & maxrow & "<>),--(MATCH(F3:F" & maxrow & "&N3:N" & maxrow & "" _
& ",F3:F" & maxrow & "&N3:N" & maxrow & ",0)=ROW(INDEX(F3:F" & maxrow & ",0))" _
& "-ROW(A3)+1),N3:N" & maxrow & ")"
Range("N" & maxrow + 1).Formula = frmla

End Sub




The debugger highlight this line and gives me a runtime error '1004'



Range("N" & maxrow + 1).Formula = frmla

[Jacob Hilderbrand]

Try this.

frmla = "=SUMPRODUCT(--(B3:B" & MaxRow & "<>" & """" & """" & "),--(MATCH(F3:F" & MaxRow & "&N3:N" & MaxRow & "" _
& ",F3:F" & MaxRow & "&N3:N" & MaxRow & ",0)=ROW(INDEX(F3:F" & MaxRow & ",0))" _
& "-ROW(A3)+1),N3:N" & MaxRow & ")"

When I have problems like this I will take out the = sign and put the formula in a cell as text, then in the cell, add the = and see if the error is more noticable (Part of the formula may become highlighted and hopefully it is the actual problem part).

For this, I noticed that you are missing the "" in the first part of the formula. Added in VBA as """" & """".

[Bob Phillips]

Sub Sum()
Dim maxrow As Long, Db As String, frmla As String
maxrow = Range("N65536").End(xlUp).Row - 1
Db = Chr(34)
frmla = "=SUMPRODUCT(--(B3:B" & maxrow & "<>""""),--(MATCH(F3:F" & maxrow & "&N3:N" & maxrow & "" _
& ",F3:F" & maxrow & "&N3:N" & maxrow & ",0)=ROW(INDEX(F3:F" & maxrow & ",0))" _
& "-ROW($A$3)+1),N3:N" & maxrow & ")"
Range("N" & maxrow + 1).Formula = frmla

End Sub

[Shazam]

Thanks both of you they work excellent.:hi:

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care