Hi, I have a list of Mnemonics of stocks. These lists look like this:

H:AND
H:AH
LX:TRE
W:UIO
etc...

From the letters before the ":" You can determine the country where the stock is trading.

I want to write a simple program that reads the cell where the mnemonic is in and compare the first two characters. If the first two characters are "H:" then I return a "1" in the next column, if "LX" then a "2", if "W:" then a "3" etc... (Dummyvariable)

I would have written the program something like this:



Sub Macro1()

For r = 1 To 20000
    W = Worksheets("Sheet1").Cells(r,2).Value
    If w = "" Then
       GoTo STOPPEN
    End If

    W = W.Substring(0,2)

If W = "H:" Then
   Worksheets("SHeet1").Cells(r,3).Value = 1
End IF

If W = "LX" Then
   Worksheets("SHeet1").Cells(r,3).Value = 2
End IF

If W = "W:" Then
   Worksheets("SHeet1").Cells(r,3).Value = 3
End IF

Next r

STOPPEN:

End Sub



This gives a problem at w = w.Substring(1,2)

Thanks

use instead.

Thanx

It works excellent