Macro help please!!!!!

[marwantaimeh]

​[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl63, width: 68, align: right"]27-Feb14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl65, width: 68, align: right"]28-Feb14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl65, width: 62, align: right"]1-Mar-14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]2-Mar-14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

​

[/TD]
[TD]

​

[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD="align: center"]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]











[TABLE="width: 843"]
<tbody>[TR]
[TD="colspan: 13"]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.[/TD]
[/TR]
[TR]
[TD="colspan: 11"]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[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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