Hi Everybody

I have an Excel Sheet "Sheet1" in Column A from Row 6 down to Row 140 I have
sequential numbers from 1 to 135. In Column B again from Row 6 down to Row
140 I have the single letters E, W, N in no particular order. Is it possible
to have a routine that will place a number 1 in frint of the First letter E
it finds e.g. 1E and when it finds the next E it places 2 in front of it e.g
2E and it would continue until it finds the last letter E placing the
sequential number in front as per the example. The routine would then
continue and do the same for the other 2 Letters N, W and begin each with the
number 1.

Hope I've made myself clear


--
Many thanks

hazel

Hi Hazel,

Give this a try:


Code
-------------------
Sub test()
Dim intLastrow, intLetterE, intLetterN, intLetterW

intLastrow = Cells(65536, 1).End(xlUp).Row
intLetterE = 0
intLetterN = 0
intLetterW = 0

For r = 6 To intLastrow
If Cells(r, 2) = "E" Then intLetterE = intLetterE + 1
If Cells(r, 2) = "E" Then Cells(r, 2) = intLetterE & Cells(r, 2)
If Cells(r, 2) = "N" Then intLetterN = intLetterN + 1
If Cells(r, 2) = "N" Then Cells(r, 2) = intLetterN & Cells(r, 2)
If Cells(r, 2) = "W" Then intLetterW = intLetterW + 1
If Cells(r, 2) = "W" Then Cells(r, 2) = intLetterW & Cells(r, 2)
Next r

End Su
-------------------


Hope this helps.

B

Hi Everybody

I have an Excel Sheet "Sheet1" in Column A from Row 6 down to Row 140
have
sequential numbers from 1 to 135. In Column B again from Row 6 down t
Row
140 I have the single letters E, W, N in no particular order. Is i
possible
to have a routine that will place a number 1 in frint of the Firs
letter E
it finds e.g. 1E and when it finds the next E it places 2 in front o
it e.g
2E and it would continue until it finds the last letter E placing the
sequential number in front as per the example. The routine would then
continue and do the same for the other 2 Letters N, W and begin eac
with the
number 1.

Hope I've made myself clear


--
Many thanks

haze

--
ben7
-----------------------------------------------------------------------
ben77's Profile: http://www.excelforum.com/member.php...fo&userid=3560
View this thread: http://www.excelforum.com/showthread.php?threadid=55708

put in C6 then copy down

=IF(B6="E";COUNTIF($B$6:B6;"E")&"E";"")