# Formula / Macro wanted - Help!

#### chippymark

##### Board Regular
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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### repairman615

##### Well-known Member
Hello,

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

##### Board Regular
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

##### Board Regular
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

##### Well-known Member

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

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

Nice.

#### chippymark

##### Board Regular
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

##### Well-known Member
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

##### Well-known Member
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>

Replies
13
Views
2K
Replies
3
Views
475
Replies
4
Views
652
Replies
26
Views
5K
Replies
0
Views
486

1,191,419
Messages
5,986,454
Members
440,031
Latest member
davidvillegasr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back