Match, then Look Left 4 Columns, then Loop to End of Range

[ryguy7272]

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---

 

[Jim Thomlinson]

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.

 

[JLGWhiz]

A couple of things i noticed. Offset(-4, 0) moves up four rows and will
throw an error from row 4 because it can only offset -3. Assuming it should
have been Offset(0, -4) which would then be four columns to the left of the
matched cell, would that not be the same row your months are on and no zeros
would ever be found? Maybe if you drew it out on paper, so you could see
each step, it would help get the logic right. Then coding it will be easier.

 

[Shane Devenshire]

Hi,

You say you are checking the 4 columns to the left of a location using
OFFSET(-4,0)

The first argument of the OFFSET function is the row argument not the column.

Try OFFSET(0,-4) ....

Cheers,
Shane Devenshire

 

[ryguy7272]

JLGWhiz, you are right, it is (0, -4); wasn't paying attention there. I
modified Jim's function slightly; now working with this:
=SUM(OFFSET(C4, 1, MATCH($B$1,$C$4:$O$4), 4,1))

I have 11 in Cell B1, and I am looking to match that value in this range,
$C$4:$O$4

With the function mentioned above, in Q6, I get zero. This seems to work,
because in J6:O6, I have all zeros, so far so good. However, in Q7, I also
get zero, but I have the following setup:
J7 = 0
K7 = 0
L7 = 15003
M7 = 13065
N7 = 0
O7 = 0
I would like to see 28068 in Q7, because that is 15003 + 13065. If K7 had a
number >0, I would like to ignore it, as it is >4 columns left of column O.
I think the sum and Offset function is close, but something is not quite
right...just wish I knew what. I'll forget about the looping thing in the
initial post; I can just do a fill-down.

Any thoughts?

Regards,
Ryan---

 

[JLGWhiz]

I thought you were offsetting -4. Formula looks like +4.

 

[ryguy7272]

I blame myself for not being able to describe this issue better. This is
easily solvable without code; really doesn't even belong in this forum.
Anyway, I am almost done, but still encumbered by something. This function
shows the appropriate value in the column that matches the value in B1:
=OFFSET(C5,0,MATCH($B$1,$D$4:$O$4))

All it does though, is give me the value in the appropriate column. I
wanted to find this value, and sum this plus three cells to the left of
this...all inclusive.

Thanks,
Ryan---

 

[Max]

=OFFSET(C5,0,MATCH($B$1,$D$4:$O$4))

.. gives me the value in the appropriate column.
I wanted to find this value, and
sum this plus three cells to the left of this...all inclusive.

Set the OFFSET's width param to: -4
then wrap a SUM around it, viz.:
=SUM(OFFSET(C5,,MATCH($B$1,$D$4:$O$4),,-4))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---

 

[ryguy7272]

Thank you sir!! That was exactly it!!
Ryan--

 

[Max]

welcome, Ryan
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik