Results 1 to 5 of 5
  1. #1
    New Lounger
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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)

  3. #3
    New Lounger
    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

  4. #4
    New Lounger
    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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)"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •