Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |