Lets start with the function before we go too far... match returns the cell
where the month was found. So we should be able to get away with the match
and offset function...

=sum(offset($C$4, 1, MATCH($B1,$C$4:$T$4), 4,1))
From cell C4 move down 1 row and across ? columns. Resize to a 4 rows by 1
column range. Sum of that range. Sum may not be the correct function. We may
want min, max or countif...

or something like that. Assuming that works then we can copy it and paste it
into the correct range.
--
HTH...

Jim Thomlinson


"ryguy7272" wrote:

I have some VBA code, part of which uses an InputBox to get the number of the
month (for instance, now it is 11) from a user. Result goes into B1. Months
are listed in Range C4:T4 (includes a few additional items). So i have a
simple function that gets the column that matches the month: =MATCH(B1,C4:T4)

I am trying to come up with a function that goes to this column; in this
case Column Q is November. Next month the column would be R; the Match
function above handles this. Then, the function looks in the range like
Offset(-4, 0) and figures out if all of these are zero. If all four cells to
the left of the current month are zero, I want to put something in the
current cell like "None".

Finally, I need code that finds the end of the used range, in column T, and
then copies the function that was created above, down that number of rows.
Something such as this may work:

Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "T").End(xlUp).Row
Range("U5:U" & lastrow).Select
Selection.FormulaR1C1 = ...function above...

Please help!
Deadline looms...


Thanks,
Ryan---



--
RyGuy