Adding Number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Back
Top