Link to home
Start Free TrialLog in
Avatar of Luis Diaz
Luis DiazFlag for Colombia

asked on

VBA Excel : Update values based on a numerical sequences

Hello experts,

I am looking for a VBA to update the various values if values of next column are not empty ex:

User generated image
The macro should be automatically enter all the numeric sequences as of A2 : 1st Comparison, 2nd Comparison... if data reported in Column B is not empty.

If ordinal number are complicated to report It will be acceptable to report like this :

1 Comparison,  2 Comparison etc...


Thank y ou very much for your help.
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

I am not sure if I have understood your requirement correctly, but try this to see if this is what you are trying to achieve.
Sub InsertNumericSequence()
Dim lr As Long, n
Dim rng As Range, cell As Range

lr = Cells(Rows.Count, "B").End(xlUp).Row
Set rng = Range("B3:B" & lr)

For Each cell In rng
    If cell <> "" Then
        n = WorksheetFunction.CountA(Range(Cells(3, "B"), Cells(cell.Row, "B")))
        Select Case Len(n)
            Case 1
                Select Case n
                    Case 1
                        cell.Offset(0, -1) = "1st Comparison"
                    Case 2
                        cell.Offset(0, -1) = "2nd Comparison"
                    Case 3
                        cell.Offset(0, -1) = "3rd Comparison"
                    Case Else
                        cell.Offset(0, -1) = n & "th Comparison"
                End Select
            Case Else
                If Right(n, 2) >= 11 And Right(n, 2) <= 20 Then
                    cell.Offset(0, -1) = n & "th Comparison"
                Else
                    Select Case Right(n, 1)
                        Case 1
                            cell.Offset(0, -1) = n & "st Comparison"
                        Case 2
                            cell.Offset(0, -1) = n & "nd Comparison"
                        Case 3
                            cell.Offset(0, -1) = n & "rd Comparison"
                        Case Else
                            cell.Offset(0, -1) = n & "th Comparison"
                    End Select
                End If
        End Select
    End If
Next cell

End Sub

Open in new window

Avatar of Luis Diaz

ASKER

Working perfectly.

I forgot to specify the following requirement: bold all the values reported in column A. Is there a way to integrate this in your previous code?
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Tested and it works!
Glad to know that. :)
I think you forgot to mark your question Solved by selecting the accepted solution. Please find some time to do that. :)
You are right. Sorry for that.
Never mind. :)