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

 

[repairman615]

Hello,

Please test on a Copy.

Alt + F11
Keystrokes Alt - I - M

Paste in:


<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> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br>i = 0<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A4:Z4")<br>    <br>    <SPAN style="color:#00007F">If</SPAN> i = 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(0, i).Value = c.Offset(-2, 0).Value<br>        i = i + 1<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[chippymark]

Hi thanks for the quick reply, I tried the above but nothing happened ? when I tried to run the Macro, I got an error message 'for without next'
Any Ideas?
Cheers

 

[chippymark]

I added the 'Next' in the code and it looks to work for the first 'X' it finds but does not continue ? So I only get the first result.

 

[repairman615]

Did you add next c?

like:

<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> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br>i = 0<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A4:Z4")<br>    <br>    <SPAN style="color:#00007F">If</SPAN> i = 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(0, i).Value = c.Offset(-2, 0).Value<br>        i = i + 1<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> c<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[chippymark]

It's Working now ) Many Thanks... Saved me loads of time.. Cheers

 

[repairman615]

Nice.

 

[chippymark]

Hi repairman 615, I want to repeat the same code for rows 5 through to 100. is there an easy way or do I have to copy and paste and keep and keep changing the row numbers ect. I can imagine this will take forever. I have done 3 rows wqhich work fine but 97 to go is a daunting task.

 

[repairman615]

I can not be certain of this:

Try on a copy:

<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> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br>i = 0<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A4:Z100")<br>    <br>    <SPAN style="color:#00007F">If</SPAN> i = 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(0, i).Value = c.Offset(-2, 0).Value<br>        i = i + 1<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> c<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[repairman615]

After looking at that further, I do not think that will do.

Try:

<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><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(0, 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>