Please help... I have been stuck on this problem for several days
now...

I am trying to loop through a specific column in a sheet, and then
building a column with all unique hits, using VBA.

When I do this with numbers, everything works perfectly (all the rows
in the original sheet consist of a number, delimited by a dot and
another two numbers).

Now, when I treat the original sheet as text (which is the right
format), I cant get the same code to work.

I suspect it has something to do with the cells(x,x).VALUE commands,
which then converts the text to numbers or something?

I have posted the code below, all help very much appreciated!


Best regards,
-NorTor



Sub Unique_PGrp()

Sheets("Arkiv").Range("A2").Value = _
Sheets("Analyse").Range("D3").Value
PGrpCount = 1
actRow = 4

Sheets("Analyse").Select
Range("D3").Select

Do Until IsEmpty(ActiveCell.Value)
newVal = 0
Sheets("Arkiv").Select
Range("A2").Select
intPGrp = Sheets("Analyse").Cells(actRow, 4).Value

If intPGrp < "" Then
For i = 0 To PGrpCount
If intPGrp = ActiveCell.Value Then
newVal = 1
End If
ActiveCell.Offset(1, 0).Select
Next i

Range("A" & (1 + PGrpCount)).Select

If newVal = 0 Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = intPGrp
PGrpCount = PGrpCount + 1
End If
End If

Sheets("Analyse").Select
actRow = actRow + 1
Range("D" & actRow).Select
ActiveCell.Offset(1, 0).Select

Loop
End Sub

It looks like you have not declared any variables.

When you don't declare variables VBA uses what it thinks is the most
appropiate and in this case it is problally setting intPGrp as an
integer


It is best prctice to declare your varables at the start of your code

Sub Unique_PGrp()

dim intPGrp as String
Dim PGrpCount as integer
etc

and also use the $ which forces VBA to set value as text

intPGrp$ = Sheets("Analyse").Cells(actRow, 4).Value


---
Message posted from http://www.ExcelForum.com/