Macro cell formula using a variable (Excell 2003 SP3)

Topic

New Reply

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?

Reply | Quote

Replies

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)

Reply | Quote

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

Reply | Quote

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_Row + 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

Reply | Quote

You could change

Worksheets(Employee_Summary_Sheet).Cells(Summary_Row + 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)”

Reply | Quote