Here is the trick with formulas. they are long strings. When you add a
variable you must break up the string. Here is a simple exampe

LastRow = 5

Range("B1").formula = "=Sum(A1:A" & LastRow & ")"

Note the parethesiin double quotes at the end. This formula is equivalent to

Range("B1").formula = "=Sum(A1:A5)"

You need to do the same with Z


ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(--('Data 4'!R8C3:R6533C3=""InAlm""),--('Data
4'!R8C4:R6533C4=RC[-" & Z & "]),--(--('Data 4'!R8C1:R6533C1)(FLOOR(NOW()-"
& Z & ",1))))"
Cells(x, y).Select


"Bertha needs help" wrote:

I want to make a daily log of alarms
in worksheet 'daily'
i have a list of alarms in column A starting at (A3)
then on row 2 i have the last 15 days starting with =today() all the way up
to =today()-14

Im bringing in data from another sheet called "Bf 4 Alarms".
Macro1() works so that i can briung in todays alarms staring at midnight and
put it in column B

But i want to be able to calculate the alarms for the past fifteen days and
place them under each date so i put together Macro2 but it wont run because
it wont allow me to put in the variable z inside the formula
how can i fix this macro so tha it works

sub Macro 1()
x=3


Do While Cells(x, 1).Value < ""

Cells(x, 2).Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(--('Data 4'!R8C3:R6533C3=""InAlm""),--('Data
4'!R8C4:R6533C4=RC[-1]),--(--('Data 4'!R8C1:R6533C1)(FLOOR(NOW()-1,1))))"
Cells(x, 2).Select
x = x + 1

Loop
end sub

Sub Macro2()

x=3
y=2
Z=1


Do While Cells(x, 1).Value < ""

Cells(x, y).Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(--('Data 4'!R8C3:R6533C3=""InAlm""),--('Data
4'!R8C4:R6533C4=RC[-Z]),--(--('Data 4'!R8C1:R6533C1)(FLOOR(NOW()-Z,1))))"
Cells(x, y).Select
x = x + 1
y = y + 1
z = z + 1
Loop

end sub