Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to insert formula for dynamic column numbers (97-SR2)

    I have a spreadsheet that is exported from an Access query. Once the spreadsheet is updated, I use an excel macro to add columns and do basic formatting. This query pulls information for every month of the previous year ie Jan-Dec 01 as well as monthly information for the current year ending with the last full month. So I currently have the following:
    Jan01,Feb01...Dec01, Jan02...Apr02 (1 column for each month)
    Also have "total"fields for the past x years after the monthly stuff, so immediately after the above, I also have:
    1998,1999,2000,2001,2002

    Now to the problem. I have some code in the macro that inserts formulas into certain cells. One cell takes the totals for the past 3 months and divides by 90. So the code for this cell is:

    ActiveCell.FormulaR1C1 = _
    "=IF(SUM(RC[15]:RC[17])/90<>0,RC[-3]/SUM(RC[15]:RC[17])/90,0)"

    The only problem is that, obviously, the past 3 months are not always going to be a range 15 to 17 cells away from the current cell. What I would like to do is instead of using the 15:17, have the macro find the last used column in the row and count backward from there since these are always gooing to be the same. The code I have now is:

    Dim lastcell, bcell, ecell As Long
    lastcell = ActiveSheet.UsedRange.Columns.Count
    bcell = lastcell - 8
    ecell = lastcell - 6

    If I test with

    MsgBox bcell

    The message box returns the correct value. There are 34 columns in the sheet and it returns 26.

    This way, the most recent month (April02) is 8 cells to the left of the end and the 3rd to last month (Jan02) is 6 cells to the left. This is not going to change. So I tried doing something like (without the if):

    ActiveCell.Formula = "=Sum(RC[bcell]:RC[ecell[)/90"

    Unfortunately, as you programming gurus will probably notice right away, this code doesn't work. I get
    "Run-Time Error 1004
    Application-Defined or Object-Defined Error"

    I feel like I'm tap dancing on a land mine but the thing just won't go off. Any help is greatly appreciated

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to insert formula for dynamic column numbers (97-SR2)

    If I follow your question, you can use the Offset property to paste the formula relatve to the last column. Here's an example that doesn't exactly fit, but you should be able to modify it to:

    Range(Range("B9"), Range("B9").End(xlDown)).Offset(0, -1).FormulaR1C1 =<your formula here>
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to insert formula for dynamic column numbers (97-SR2)

    Hi John. Thanks for a quick response. I tried your example, but I don't think I did it right. Here's what I have now:

    Dim lastcell, bcell, ecell As Long
    lastcell = ActiveSheet.UsedRange.Columns.Count
    bcell = lastcell - 8
    ecell = lastcell - 6
    Range(Range("F2"), Range("F2").End(xlDown)).Offset(-8, 0).FormulaR1C1 = "=SUM(RC[15]:RC[17])"

    Still Get the Same error. I'm not familiar with the Offset property so I don't really know what it's doing. I will lookk at the Help file on it in the morning and see if I can make heads or tails of it. In the mean time, are there any clarifications I can make to the question to make it more sensible?

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to insert formula for dynamic column numbers (97-SR2)

    Unfortunately the powers that sign my paycheck have me working on an urgent project, so I'm answering in haste.

    You need something more like this, I think:
    [Edited]

    Dim lastcol As Integer, firstrow As Long
    lastcol = ActiveSheet.UsedRange.Columns.Count
    firstrow = ActiveSheet.UsedRange.Row
    Range(Cells(firstrow, lastcol), Cells(firstrow, lastcol).End(xlDown)).Offset(0, -6).FormulaR1C1 = _
    "=SUM(RC[15]:RC[17])"

    (I'm assuming you are working with a table of data; ActiveSheet.UsedRange is a great shortcut but it may not be what you intend. Naming the table and using that name instead may be safer.

    Also, when you Dimension variables in VBA, you must Dim each one, like this:
    Dim this as Long, that as Long, theother as Long

    If you use:
    Dim this, that, theother as Long

    variables 'this' & 'that' will be Dimensioned as Variant. Unless of course that is what you intend.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to insert formula for dynamic column numbers (97-SR2)

    I tried your last example and it was almost there. It placed the formula in the first cell I actually wanted to calculate. But I played around with it and came up with a solution. For anyone else who may be wandering, the code I have now is:

    Dim lastcell As Integer, bcell As Integer, ecell As Integer
    lastcell = ActiveSheet.UsedRange.Columns.Count
    bcell = (lastcell - 8 - ActiveCell.Column)
    ecell = lastcell - 6 - ActiveCell.Column

    ActiveCell.FormulaR1C1 = "=sum(rc[" & bcell & "]:rc[" & ecell & "])"

    This sets the formula for the current cell (which was selected previously in the code) = the sum of the cells 8 to 6 cells from the last column. So if you have a sheet with 12 columns (A thru L), if B1 is active, this code will generate the formula for cell B1 as

    =SUM(D1:F1)

    This way, I think, no matter how many more months are added, only the last 3 are summed, since there will always be 6 columns from the last month to the end of the "row".

    Thanks again for your help.

Posting Permissions

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