CODEWITHSUNDEEP
vbaexcel
Ale
Ale

Reputation: 665

How to compare text values in VBA?

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

Upvotes: 0

Views: 189

Answers (2)

MVAmorim
MVAmorim

Reputation: 105

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

Upvotes: 0

Nordik
Nordik

Reputation: 166

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

Upvotes: 1

Related Questions