Results 1 to 5 of 5

20020515, 18:23 #1
 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 (97SR2)
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 JanDec 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
"RunTime Error 1004
ApplicationDefined or ObjectDefined 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

20020515, 18:32 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Macro to insert formula for dynamic column numbers (97SR2)
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

20020515, 19:28 #3
 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 (97SR2)
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?

20020515, 21:31 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Macro to insert formula for dynamic column numbers (97SR2)
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

20020516, 14:58 #5
 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 (97SR2)
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.