Formula / Macro wanted - Help!

[chippymark]

Hi,
I will try and explain in words people may understand. Cells A4 to Z4 may contain an ‘X’ Starting in Cell AA I want to look across row 4 find the first ‘X’ and insert the value in row 2 of the same column. So if the first X is in M4 then AA4 will = M2. Now the tricky part is AB4 needs to look for the next ‘X’ and then put the value from row 2. So lets say the next ‘X’ is in R4 then AB4 = R2.

Hope that helps explain

Any help appreciated -
Cheers Mark

 

[chippymark]

Thanks for all your help here, the above code seems to put everything on one line ...and does not look to be pulling the data all from row2.

Any Ideas ?

 

[repairman615]

Did you try the second code? ...posted twice and am not sure if it was the first or second code.

 

[chippymark]

Mate, I really appreciate all your help and time... I can see what the above code is trying to achieve but I get the following error: Method 'Range' of object '_Global' failed

sorry to be a pain.

 

[repairman615]

No worries,

Does this return the same error?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ChooseMe()<br><br><SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> MyRange <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> MyRange = Range("A4:Z4")<br><br><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 100<br><br>    n = 0<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range(MyRange).Offset(i, 0)<br>        <br>        <SPAN style="color:#00007F">If</SPAN> n = 2 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>        <br>        <SPAN style="color:#00007F">If</SPAN> UCase(c.Value) = "X" <SPAN style="color:#00007F">Then</SPAN><br>            Range("AB4").Offset(i, n).Value = c.Offset(-2, 0).Value<br>            n = n + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>    <SPAN style="color:#00007F">Next</SPAN> c<br><br><SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[chippymark]

I get the same error and it highlights the line :
For Each c In Range(MyRange).Offset(i, 0)

I know what should happen but putting into code is the hard thing.
I actually have more than two columns to work though 83 infact and 100 rows. so I am wanting the code to work on row 4 until 'n' reaches column 83, then the code should drop down a row and repeat but ensure each time the result is pulled from row 2.

I know in my head what should happen but can't quite tweak this code.

Cheers

 

[chippymark]

This is now resolved : here is the code that worked, thanks for all your help

Sub CheckForX()

Dim c As Range
Dim MyRange As Range
Dim n As Integer
Dim i As Integer
Dim l As Integer
l = -1
Set MyRange = Range("C4:BR4")

For i = 0 To 68

n = 0
l = l + -1
For Each c In MyRange.Offset(i, 0)

If n = 100 Then Exit For
If UCase(c.Value) = "X" Then
Range("BS4").Offset(i, n).Value = c.Offset(l, 0).Value
n = n + 1


End If


Next c

Next i

End Sub