How to compare text values in VBA?

Question

I have a table as shown below. I would like to compare column A "Text" values with the next text value in the same column. In case they are equal to put an index i=1 if they are different, to put the next index i+1. The problem is that in the I get right index for each column B cell except cells that remain blank (as on the picture). Can anyone help with it, please?

enter image description here

Sub Ma1()
Dim ws As Worksheet
Dim LastRow As Long
Dim i, n As Integer

Set ws = ActiveWorkbook.Sheets("Sheet2")
LastRow = Sheets("Sheet2").Range("A" & Sheets("Sheet2").Rows.Count).End(xlUp).Row

Column1 = Sheets("Sheet2").Range("A3:A" & LastRow)

n = 1
For i = 3 To LastRow
On Error Resume Next
If Range("A" & i) = Range("A" & i + 1) Then
Range("B" & i) = n
Else: n = n + 1
End If
Next i

End Sub

Nordik · Accepted Answer · 2015-03-11 11:03:16Z

1

You should modify your IF:

If Range("A" & i) = Range("A" & i + 1) Then
   Range("B" & i) = n
Else
   n = n + 1
   Range("B" & i) = n-1
End If

answered Mar 11, 2015 at 11:03

Nordik

1661 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MVAmorim · Accepted Answer · 2015-03-11 11:05:33Z

0

try this:

Sub Ma1()
Dim ws As Worksheet
Dim LastRow As Long
Dim i, n As Integer

Set ws = ActiveWorkbook.Sheets(1)
LastRow = Sheets(1).Range("A" & Sheets(1).Rows.Count).End(xlUp).Row

Column1 = Sheets(1).Range("A3:A" & LastRow)

n = 1
For i = 3 To LastRow
    On Error Resume Next
    If Range("A" & i) = Range("A" & i + 1) Then
        Range("B" & i) = n
    ElseIf Range("A" & i) <> Range("A" & i + 1) And _
           Range("A" & i) = Range("A" & i - 1) Then
        Range("B" & i) = n
        n = n + 1
    End If
Next i

End Sub

answered Mar 11, 2015 at 11:05

MVAmorim

1052 gold badges3 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to compare text values in VBA?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related