I have a spreadsheet with dates as the column headers. My macro searches for
the first date that is after today and inserts a column to be used for
totals. The formula in the new column needs to sum the values in that row
from column F to the column to the left of the formula. Since the total
column is not always going to be in the same position when my macro runs, I
am not sure how to write the formula.
For example, in row 2, the formula would be =sum(F2:RC[-1]), but I tried
that and a bunch of other stuff that didn't work.

Thanks,
Denise

You can find the column of the date in question by something like

Set rng = Sheets("yoursheet").Range("daterange").Find(date)
datecol = date.column

You need to define date as the date you are looking for, and obviousl
have the ranges defined

Then you can use datecol as the maximum column in your sum formul

--
duan

-----------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...fo&userid=1162
View this thread: http://www.excelforum.com/showthread.php?threadid=27596

You can't mix the A1 and R1C1 reference style like that, but maybe:

dim myCell as range
'some code that sets myCell to something

mycell.FormulaR1C1 = "=sum(RC6:RC[-1])"

(RC6= same row, column 6 (F).)




Denise wrote:

I have a spreadsheet with dates as the column headers. My macro searches for
the first date that is after today and inserts a column to be used for
totals. The formula in the new column needs to sum the values in that row
from column F to the column to the left of the formula. Since the total
column is not always going to be in the same position when my macro runs, I
am not sure how to write the formula.
For example, in row 2, the formula would be =sum(F2:RC[-1]), but I tried
that and a bunch of other stuff that didn't work.

Thanks,
Denise

--

Dave Peterson