VBA to copy/paste columns and update formula in each new column

[Lungfish]

Morning all.

I am trying to sort out a macro for copy/paste columns of formula.

As each column is copied and moved to the next, the formula needs to update to reflect it's new location.

Starting cell is C3. The data set is 24rows x 60colums. (ie. 24hrs x 60 days. Copy C3 - C26 and paste one column at a time through to BJ3-BJ26)
As each column is pasted, the formula should incrementally update one part of the formula.
Formula in C3 is :

=COUNTIFS(DATA!$A$15:$A$40000,"="&TODAY(),DATA!$H$15:$H$40000,[B]"=0"[/B],DATA!$D$15:$D$40000,"=12")

Formula in C4 is :

=COUNTIFS(DATA!$A$15:$A$40000,"="&TODAY(),DATA!$H$15:$H$40000,[B]"=1"[/B],DATA!$D$15:$D$40000,"=12")

...
Formula in C26 is :

=COUNTIFS(DATA!$A$15:$A$40000,"="&TODAY(),DATA!$H$15:$H$40000,[B]"=23"[/B],DATA!$D$15:$D$40000,"=12")

This is pulling data from various locations for each hour of TODAY.
The copy paste for column D to BJ (60days) should update like this :

=COUNTIFS(DATA!$A$15:$A$40000,"="&[B]TODAY()-1[/B],DATA!$H$15:$H$40000,"=0[B]"[/B],DATA!$D$15:$D$40000,"=12")

so, TODAY()-1 is yesterdays data in column D, for 24hrs.
Column E would be TODAY()-2, Column BJ would be TODAY()-59, for a total of 60 days data.

I have tried recording the macro, but it turns out to be massive and clunky looking. I just don't have the experience/knowledge to simplify it and make it elegant.

Thanks in advance.

Martin

 

[Lungfish]

Morning again,

I've had a crack at working on a solution, some of it from the macro recorder and various bits of info sourced from all over the place.

What I have come up with, so far, is by no means a working solution but an attempt to actually have a go myself.

The code below, doesn't like the way I am setting up the formula string and gives me an "expected end of statement" error (Highlighted red)

After that I am at a bit of a loss on how to run the loop to fill the formula.

Regards,
Martin

Sub PasteFormula()
[COLOR=#008000]' orginal formula (starts in cell C3) =COUNTIFS(DATA!$A$15:$A$25000,"="&TODAY(),DATA!$H$15:$H$25000,"=0",DATA!$D$15:$D$25000,"=12")[/COLOR]
Dim hr As Integer [COLOR=#008000]' hrs in a day[/COLOR]
Dim d As Integer [COLOR=#008000] ' number of days in data set[/COLOR]
Dim rr As Integer [COLOR=#008000]' row reference numbers - start to finish[/COLOR]
Dim cr As Integer[COLOR=#008000] ' Column reference numbers - Start to finish[/COLOR]
Dim Myform As String [COLOR=#008000]' Formula for start cell and day 1[/COLOR]
Dim Myform2 As String [COLOR=#008000]' Incremental formula for days 2 to 60[/COLOR]


Application.Calculation = xlCalculationManual


  Worksheets("HRSTATS").Select
  For rr = 3 To 26      [COLOR=#008000]' Rows 3 to 26[/COLOR]
  For cr = 3 To 63      [COLOR=#008000]' Columns C to BJ[/COLOR]
  For hr = 0 To 23      [COLOR=#008000]' midnight to 11pm[/COLOR]
  For d = 1 To 59       [COLOR=#008000]' Day 2 to 60[/COLOR]
  Myform = "=COUNTIFS(DATA!R15C1:R25000C1,""=""&TODAY(),DATA!R15C8:R25000C8,""=" & hr & "[B][COLOR=#ff0000]",DATA!R15C4:R25000C4,""=12"")"                    [/COLOR][/B][COLOR=#008000]' day 1 formula[/COLOR]
  Myform2 = "=COUNTIFS(DATA!R15C1:R25000C1,""=""&TODAY() & "-" & d & "",DATA!R15C8:R25000C8,""=" & hr & "",DATA!R15C4:R25000C4,""=12"")"   [COLOR=#008000] ' Formula for days 2 to 60[/COLOR]
  
    Worksheets("HRSTATS").Range("C" & rr).Formula = Myform
  
Next rr


Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic


End Sub

 

[Lungfish]

Morning.

Still plodding along by myself.
I've sorted out some of my stupidity in the last post as well!
I have modified the original formula to enable easier filling down the rows throughout the sheet.
The code below achieves what I need to do in the first column.

Any suggestions on how to get that formula into the next column (for another 59 columns) and add into the formula "&TODAY()-1", "&TODAY()-2", ... "& TODAY()-59", etc?

Sub PasteFormula()
' orginal formula =COUNTIFS(DATA!$A$15:$A$25000,"="&TODAY(),DATA!$H$15:$H$25000,"=" & $B3,DATA!$D$15:$D$25000,"=" &$A$2)
Dim hr As Integer ' hrs in a day
Dim Myform As String ' Formula for start cell and day 1

Application.Calculation = xlCalculationManual

Myform = "=COUNTIFS(DATA!R15C1:R25000C1,""="" & TODAY(),DATA!R15C8:R25000C8,""="" & $B3 & "",DATA!R15C4:R25000C4,"" & $A$2)"
hr = 0

For Each c In Range("C3:C26")
c.Value = Myform
hr = hr + 1
Next
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Lungfish]

Morning all,

almost 80 views and no response?!?!?

I've moved the question to excel forum.

Cheers
Martin