Formula in VBA

[tomas]

Hi I have a problem in coming up with a formula in my macro so to cut to the chase I need to replicate formulas which are in highlighted cells. The problem is that column total is dynamic and it depend on how many campaigns in row 6 there will be.

thanks in advance

 

[Marc L]

Hi !

Is "Total" header present before to replicate formula ?
​

 

[tomas]

Hi !

Is "Total" header present before to replicate formula ?
​

Yes it will be there before

 

[Deepak]

Check this..

Option Explicit

Sub test()
Dim strF As String, c As Integer, r As Integer

With Sheets("List1")
    c = .[b6].CurrentRegion.Columns.Count:    r = .[b6].CurrentRegion.Rows.Count
   
 '   .Cells(6, c + 1) = "total"
 '   .Cells(r + 6 + 1, 1) = "inv"
 '   .Range(.Cells(r + 6 + 1, 2), .Cells(r + 6 + 1, c)) = "=RANDBETWEEN(100,500)"
 '   .Cells(r + 6 + 1, c + 1) = "=sum(" & Range(Cells(r + 6 + 1, 2), Cells(r + 6 + 1, c)).Address & ")"
   
    strF = "=IF(SUM(" & Range(Cells(7, 2), Cells(7, c)).Address(False) & _
            ")=0,"""",SUMPRODUCT(" & Range(Cells(7, 2), Cells(7, c)).Address(False) & _
            "," & Range(.Cells(r + 6 + 1, 2), .Cells(r + 6 + 1, c)).Address & ")/" & Cells(r + 6 + 1, c + 1).Address & ")"
   
    .Range(.Cells(7, c + 1), .Cells(r + 6 - 1, c + 1)).Formula = strF
End With
End Sub

 

[tomas]

Great Deepak works as neede

 

[Marc L]

I was off net …

I'm not sure the IF is necessary :​

Sub Demo()
With Worksheets("List1").[A6].CurrentRegion.Rows
    With .Item("2:" & .Count).Columns
         .Item(.Count).Formula = "=SUMPRODUCT(" & .Item(2).Resize(, .Count - 2).Rows(1).Address(False) & _
                                 "," & .Item(2).Resize(, .Count - 2).Rows(.Rows.Count).Offset(2).Address & _
                                 ")/" & .Cells(.Count).Offset(.Rows.Count + 1).Address
    End With
End With
End Sub

 

[tomas]

Thanks guys

Could you please yet see this with reference to previous file why it says error ?

Sub test2()

Dim strf As String

strf = "=SUMIF(" & Range(Cells(10, 2), Cells(10, 9)).Adress(False) & ","">0""," & Range(Cells(12, 2), Cells(12, 9)).Adress(False) & ")"

End Sub

 

[Marc L]

Just a typo error : .Address !

​