Simple Do Until Loop in VBA

Question

I am new to Excel VBA and have a simple question. Once I understand how to to this loop, I can build on it.

I would like to automate a classification. The data repeats itself in a single column i.e sampleA, sampleA, sampleA, sampleB, sampleB, sampleC, sampleC, sampleC, etc. I would like my macro to identify when the sample name changes between the sample names. So, for example, when the following cell after sampleA turns into sampleB, I would like the macro to understand that there is a change and write a phrase next to this (this will next turn into an equation with the data in the sample's respective rows but baby steps :) ).

Overall, the macro needs to sense the changes between a column of identical sample names until a new name is reached.

I have been researching solutions and the "do until" loop seems to be the closest solution to what I need. Also, to show that sampleA is not the same as sampleB I used <>.

Sub DoUntilStringMacro()

Dim sampleA As String

Dim sampleB As String

Dim sampleC As String

Dim classification As String

    Do Until ActiveCell.Value = ""

        If sampleA <> sampleB Then
            classification = "hello"
        ElseIf sampleB <> sampleC Then
            classification = "goodbye"
        ElseIf sampleC <> sampleA Then
            classification = "see you soon"
        End If

        answer = classification
        Cells(classification, "B").Value = answer

    Loop

End Sub

Actual results: error at Cells(classification, "B").Value = answer Something is wrong here. I am trying to display results in column "B".

Expected results on Excel Worksheet in columns:

Sample:  Classification
sampleA  --
sampleA  --
sampleA  hello
sampleB  --
sampleB  goodbye
sampleC  --
sampleC  --
sampleC  see you soon

Answer 1

1. You declare your sampleA,B,C variables but you never set them so you when compare those three variables nothing happens.

2. Inside your loop you never set ActiveCell to anything so ActiveCell just stays whatever it is. You will loop forever because of this.

Here's a rewrite that isn't really optimized. There are certainly better ways to do this (using a for loop over the Range("A1:A" & lastFilledRow) for instance), but I wanted to keep this pretty close to your attempt to see how to solve this the way you were planning as it's certainly viable and reasonable way to do it.

I added a ton of comments to explain what was happening.

Sub DoUntilStringMacro()

    Dim currentValue As String
    Dim previousValue As String
    Dim classification As String

    'set the first cell to search and we will iterate
    '   from there
    Dim searchCell As Range
    Set searchCell = Sheet1.Range("A2")

    'Lets also get a counter variable to see how many
    '   times we've found a change...
    Dim changesFound As Integer

    'Loop until the searchCell is empty
    Do Until searchCell.Value = ""


        'set the currentValue
        currentValue = searchCell.Value

        'If the previousValue variable is empty then
        '   this is the first cell we're analyzing
        '   so don't bother running this bit of code
        '   that does the comparison
        If previousValue <> "" Then

            'Compare to what we stored in previousValue
            If currentValue <> previousValue Then
                'We found a change, increment our counter
                changesFound = changesFound + 1

                'and based on that value lets figure out
                '   what to write out
                Select Case changesFound
                    Case 1
                        'This is the first time we hit a
                        '   a change so write out "hello"
                        'BUT!!! we need to write it the
                        '   cell above the current
                        '   searchCell and one column over
                        '   We'll use .Offset() to do that
                        searchCell.Offset(-1, 1).Value = "Hello"
                    Case 2
                        searchCell.Offset(-1, 1).Value = "goodbye"
                    Case 3
                        searchCell.Offset(-1, 1).Value = "see you soon"
                End Select

            End If
        End If

        'So we are going to iterate again, lets capture
        '   the currentValue into the previousValue
        '   variable so we have it to compare on the
        '   the next loop
        previousValue = currentValue

        'Also... we want to make sure that searchCell
        '   is the next cell otherwise we will just
        '   keep testing the same cell over and over
        '   again until excel crashes.
        'Again we'll use `.Offset()` to move to the
        '   next row
        Set searchCell = searchCell.Offset(1)

    Loop

    'Heres the kicker... we needed one more iteration
    '   since we exited when the searchCell was blank... 
    '   so that Case 3 never hit... 
    '   we'll just go ahead and fill that out now
    searchCell.Offset(-1, 1).Value = "See you soon"

End Sub

I've done away with ActiveCell since that is not a good idea. Select, Activate, ActiveCell these are all crap shoots in VBA. It's better to say "Explicitly this cell/range is what I want" rather than hoping that the cell you are wanting is currently active.

Answer 2

You could try For loop:

Option Explicit

Sub Change()

    Dim Lastrow As Long, Row As Long
    Dim PreviousString As String, CurrenctString As String

    'With statement refers to shee 1
    With ThisWorkbook.Worksheets("Sheet1")
        'Find last row in Sheet 1 - Column A
         Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        'Loop each row start from 3 to lastrow
        For Row = 3 To Lastrow
            PreviousString = .Range("A" & Row - 1).Value
            CurrenctString = .Range("A" & Row).Value

            'Check if both strings are not the same
            If PreviousString <> CurrenctString Then
                If PreviousString = "sampleA" And CurrenctString = "sampleB" Then
                    .Range("B" & Row - 1).Value = "hello"
                ElseIf PreviousString = "sampleB" And CurrenctString = "sampleC" Then
                    .Range("B" & Row - 1).Value = "goodbye"
                ElseIf PreviousString = "sampleC" And CurrenctString = "sampleD" Then
                    .Range("B" & Row - 1).Value = "see you soon"
                End If

            End If

        Next Row

    End With


End Sub

Answer 3

You could use formulas and avoid loops:

Sub IdentifySampleNameChanges()
    With Worksheets("Sheet1") ‘ change “Sheet1” to your actual sheet name
          With .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Offset(, 1)
             .Formula = "=IF(A2<>A3,""Hello"","""")"
             .Value = .Value
        End With 
    End With 
End Sub