Results 1 to 5 of 5

20090209, 03:16 #1
 Join Date
 Feb 2009
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Macro cell formula using a variable (Excell 2003 SP3)
I am supplied a workbook with a sheet populated with data. I need to extract only certain data from this sheet (let's say every third row, but it changes randomly based on cell values) into a new sheet that my macro creates. I wanted the value of the new sheet cells to be formulas pointing back to the cells from the supplied sheet (ie: =Supplied!A53), but when I recorded this action I was given the RC notation (ie ActiveCell.FormulaR1C1 = "=Supplied!R[7]C[3]") which works well enough until a row is skipped on the supplied sheet and things get out of sync. I am switching back & forth between the two sheets and have different row & column variables directing the iteration loops for each. The macro search loop is incrementing down the supplied sheet and when cell values meeting certain criteria are encountered, the new sheet is activated and a formula pointing to the supplied sheet cell value is deposited in the next available row. Is there some way I can create the formula for the new sheet cell using the supplied sheet row & column variables?

20090209, 23:12 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Macro cell formula using a variable (Excell 2003 SP3)
Welcome to Woody's Lounge!
Let's say that you have variables lngTargetRow and lngTargetCol. You can use them like this:
ActiveCell.Formula = "=" & Worksheets("Supplied").Cells(lngTargetRow, lngTargetCol).Address(External:=True)

20090210, 04:11 #3
 Join Date
 Feb 2009
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Macro cell formula using a variable (Excell 2003 SP3)
Hans,
Thanks much!
I still have a few more lines of code to go, but your suggestion worked and educated me a little as well. I may have one or two more questions before it's finished
Jason

20090211, 01:54 #4
 Join Date
 Feb 2009
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Macro cell formula using a variable (Excell 2003 SP3)
Hans,
I ran into a similar problem later in the project where, as I looped down consecutive rows, I needed to insert a blank row based on a certain condition and then create a SUM calculation of numbers above (variable vertical cell amount) for each of 15 consecutive cells within this newly created row. I first approached this by using a variable (Cat_Start_Row) to hold which row above the SUM which represented the summing start point (the row immediately above the SUM is the end point) and then tried to deposit a formula into each of the 15 cells using the following notation.
For Summary_Col = 4 To 19
Worksheets(Employee_Summary_Sheet).Cells(Summary_R ow + 1, Summary_Col).Select
ActiveCell.Formula = "= SUM(" & Cells(Cat_Start_Row, Summary_Col) & ":" & Cells(Summary_Row, Summary_Col) & ")"
Next Summary_Col
But this had some syntax error when run, so then I recorded a macro that used the SUM command in the empty cell and then copied it to the right to the last of the 15 cells in the row. I then tried to replace the relative start number with the variable, but this has syntax issues as well when run (see code below). Any thoughts on how I can code this functionality?
ActiveCell.FormulaR1C1 = "=SUM(R[Cat_Start_Row]C:R[1]C)"
Selection.AutoFill Destination:=Range(Cells(Summary_Row + 1, 4), Cells(Summary_Row + 1, 19)), Type:=xlFillDefault
Any Help Is Greatly Appreciated
Jason

20090211, 06:47 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Macro cell formula using a variable (Excell 2003 SP3)
You could change
Worksheets(Employee_Summary_Sheet).Cells(Summary_R ow + 1, Summary_Col).Select
ActiveCell.Formula = "= SUM(" & Cells(Cat_Start_Row, Summary_Col) & ":" & Cells(Summary_Row, Summary_Col) & ")"
to
With Worksheets(Employee_Summary_Sheet)
.Cells(Summary_Row + 1, Summary_Col).Formula = "= SUM(" & .Cells(Cat_Start_Row, Summary_Col).Address & ":" & .Cells(Summary_Row, Summary_Col).Address & ")"
End With
or change
ActiveCell.FormulaR1C1 = "=SUM(R[Cat_Start_Row]C:R[1]C)"
Selection.AutoFill Destination:=Range(Cells(Summary_Row + 1, 4), Cells(Summary_Row + 1, 19)), Type:=xlFillDefault
to
Range(Cells(Summary_Row + 1, 4), Cells(Summary_Row + 1, 19)).FormulaR1C1 = "=SUM(R" & Cat_Start_Row & "C:R[1]C)"