VBA add formula

[jocker_boy]

Hello,

i have this formula in excel in cell FN11:

=SUMPRODUCT(--(MOD(COLUMN(CL11:FM11)-COLUMN(CL11);2)=0);(MOD(COLUMN(CL11:FM11)<=MATCH(FO$3;$C$5:FM$5;FALSE);2));CL11:FM1

And i would like to add this formula to my VBA code considering that the columns can change.
So i already have defined this variables:

ic - Inicial column: CL
lc - last column FM

My goal is something similar to this:

=SUMPRODUCT(--(MOD(COLUMN(Cells(11,ic),Cells(11,lc))-COLUMN(Cells(11,ic);2)=0);(MOD(COLUMN(Cells(11,ic),Cells(11,lc))<=MATCH(Cells(11,lc+2);Cells(5,ic),Cells(5,lc);FALSE);2));Cells(11,ic),Cells(11,lc)

Thanks,
Gonçalo

 

[bebo021999]

From your initial formula, grab it inside EVALUATE, like this:

Option Explicit
Sub test()
Dim sC As String, eC As String
sC = "CL" ' start column 
eC = "FM" ' end column
Range("FN11").Value = Evaluate("=SUMPRODUCT((MOD(COLUMN(" & sC & "11:" & eC & "11)-COLUMN(" & sC & "11),2)=0)* (MOD(COLUMN(" & sC & _
"11:" & eC & "11)<=MATCH(FO$3,$C$5:" & eC & "$5,FALSE),2))*" & sC & "1:" & eC & "11)")
End Sub

 

[jocker_boy]

From your initial formula, grab it inside EVALUATE, like this:

Option Explicit
Sub test()
Dim sC As String, eC As String
sC = "CL" ' start column
eC = "FM" ' end column
Range("FN11").Value = Evaluate("=SUMPRODUCT((MOD(COLUMN(" & sC & "11:" & eC & "11)-COLUMN(" & sC & "11),2)=0)* (MOD(COLUMN(" & sC & _
"11:" & eC & "11)<=MATCH(FO$3,$C$5:" & eC & "$5,FALSE),2))*" & sC & "1:" & eC & "11)")
End Sub

Thanks for your help.
But i only get: "#VALUE!" as result.

I don't understand the "evaluate", but i need to have the formula inside the cell so that i can copy to others.

Thanks.

 

[jocker_boy]

Any help, please.

 

[jocker_boy]

I finally did it:

Original formula:
=SUMPRODUCT(--(MOD(COLUMN(ED6:GC6) - COLUMN(ED6);2)=0);(MOD(COLUMN(ED6:GC6)<=MATCH($GE$3;($C$5:$GC$5);FALSE);2));ED6:GC6)

And in VBA:

Sub try()

    Dim lc As Long
    Dim lc1 As Long
    
'Find last column in rows with data
    lc = Cells(5, Columns.Count).End(xlToLeft).Column
    lc1 = Cells(1, Columns.Count).End(xlToLeft).Column

'add formula
    Cells(6, lc + 2).Formula = "=SUMPRODUCT(--(MOD(COLUMN(" & Range(Cells(6, lc1 + 1), Cells(6, lc)).Address(False, False) & ") - COLUMN(" & Range(Cells(6, lc1 + 1), Cells(6, lc1 + 1)).Address(False, False) & "),2)=0),(MOD(COLUMN(" & Range(Cells(6, lc1 + 1), Cells(6, lc)).Address(False, False) & ")<=MATCH(" & Cells(3, lc + 2).Address & ",(" & Range(Cells(5, 3), Cells(5, lc)).Address(True, True) & "),FALSE),2))," & Range(Cells(6, lc1 + 1), Cells(6, lc)).Address(False, False) & ")"

End Sub

Just to share