Date question

[Makk14]

Hello experts,

In cell A2 I enter a $ value; in cell B2 I enter a date
In cells C1..N1 I have titles January through December

How would I code the following:
Depending on the date in B2, the value in A2 will be copied to one of the cells in C2…N2? (I don't think I can do this with because of the limitations of how many nested statements can be used)

Thank you!

 

[Smitty]

Maybe a change event:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
         <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("B2")
        <SPAN style="color:#007F00">'   Only look at that range</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN>
            Range("A2").Copy Range("A2").Offset(, Month([B2].Value) + 1)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[u742884]

Here's a solution that does not require any code.

In b3 (or this can somewhere out of the way), key formula =month(b2). This will return the month # (1-12) of your date.

In C1:N1, instead of keying the month name, key in an actual date for each month (1/1/06, 2/1/06...12/1/06). Then format these cells as Custom -- mmmm. This shows them as month names again but now they are date values.

In cells C3:N3, use your =month formula again pointed to your month date/names in row 1. Ex. in C3: =month(c1). (again this can be somewhere out of the way or just hidden).

Then in cells C2, key formula: =if(c3=$b3,$a2,0) and copy across to N2.

To test, I keyed $100 in A2, 8/4/06 in B2. The $100 showed up in my August column.

 

[barry houdini]

This formula in C2 copied across to N2 (and down to subsequent rows if required)

=IF(TEXT($B2,"mmmm")=C$1,$A2,"")

Book1
	A	B	C	D	E	F	G
1			january	february	march	april	may
2	23	04-Apr-06				23
3	21	25-Feb-06		21
4
Sheet3

 

[u742884]

Barry's is a much cleaner version.