Macro help please!!!!!

[marwantaimeh]

​

	A	B	C	D	E	F	G	H	I
1	27-Feb14 <tbody> </tbody>	28-Feb14 <tbody> </tbody>	1-Mar-14 <tbody> </tbody>	2-Mar-14 <tbody> </tbody>	​	​	February	March	April
2
3
4		X
5

<tbody>
</tbody>

if range of cells A4:AD, contains "x", then read in which month it is located, then write "x" under which month it is in, under columns G, H and I.
in the case we have here, there is an x in 28-feb-14, then I want it automatically to write x under february in column G, row 4, and if the x is moved to 2-mar-14, then i want to x automatically moves to March in column H, row 4. Thanks

<tbody>
</tbody>

 

[CharlesChuckieCharles]

First where you have the month names turn them into dates February into 01/02/14 Then format the cells as custom MMMM

so now its shows as February

underneath the new month names put the following formula


=IF(LOOKUP(MONTH($G1),MONTH($A1:$D1),$A2:$D2)="","",LOOKUP(MONTH($G1),MONTH($A1:$D1),$A2:$D2))

Drag it down and across to fill in your grid


It works assuming 27-Feb14 is a real date and not text

 

[ukmikeb]

deleted

 

[marwantaimeh]

First of all thank you for your time.

It is not exactly what i wanted but if we kept of tweaking with it , it might work.

What I want exactly to happen is the following:

If there is a "x" under feb 28th, then i want under the month Feb to have an "x" and later on if the "x" is push from 28th feb to 1st of march , then i want the x under FEB to be moved to March.

Does that make sense?

First where you have the month names turn them into dates February into 01/02/14 Then format the cells as custom MMMM

so now its shows as February

underneath the new month names put the following formula


=IF(LOOKUP(MONTH($G1),MONTH($A1:$D1),$A2:$D2)="","",LOOKUP(MONTH($G1),MONTH($A1:$D1),$A2:$D2))

Drag it down and across to fill in your grid


It works assuming 27-Feb14 is a real date and not text

 

[CharlesChuckieCharles]

Explain "Push"

have you tried it?

 

[Momentman]

Is the "x" always in row 4? I have assumed that for this current "macro"

Right click the tab and select view code, paste this code in there

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A4:D4")) Is Nothing Then
        Range("G4:I4").Clear
        If Target.Value = "x" Then
            colfind = Range("G1:I1").Find(Format(Target.Offset(1 - Target.Row, 0).Value, "mmmm")).Column
            Cells(Target.Row, colfind).Value = "x"
        End If
    End If
End Sub

Please check out the formula by Charles. Why kill the problem with a macro if a formula can perform just as well

 

[ukmikeb]

Hi

Try in G2:-

=IF(SUMPRODUCT(($A2:$D2="x")*(MONTH(G$1)=MONTH($A$1:$D$1))),"x","")

copied across and down.

hth