3

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
Improve this question
4
  • 2
    Relying on ActiveCell can cause issues. Instead of using a Do Until loop, perhaps consider finding the last row and then looping from start row to last row. Commented Jan 4, 2019 at 21:42
  • 2
    SampleA etc. are never initialized, and it isn't clear what cell you expect something like Cells("goodbye","B") to be since "goodbye" is manifestly not a row number. Commented Jan 4, 2019 at 21:43
  • Can there be blank cells in the range? And can the classification repeat or must it only occur directly before a change? Does the last value before the end blank constitute a change (appears so...)? Is data guaranteed to be ordered? Commented Jan 4, 2019 at 23:38
  • You omitted parts of the code because it's not clear where these sample variables come from. If you deal with ActiveCell, you need to advance it in a loop - otherwise you're stuck with one and the same cell. Commented Jan 7, 2019 at 8:46

3 Answers 3

Reset to default
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.

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

6 Comments

Thank you for this hint, JNevill! I like how this script is looking and I am understanding until certain points. My first question is: once we set this counter for every time there is a change, these integers 1, 2, 3, etc will replace column A? Looks like you never set a column for these to go in. My second question is: Could we add on to the next column, C, a formula for only the rows that have an integer? And my third question is: I am getting an error at currentValue=searchCell.Value. Why is this? Thank you so much!
1) We do set a column using that offset(-1,1). That says move up on row from the current cell and over one column. 2) I'm not following you on your use of Column C. "Only rows that have an integer" but I don't see any integers in your data or the data we are writing into column B. 3) What is the error you are getting? Also, hit the "Debug" button so that line is highlighted and hover over searchCell.Value and see what value it's reporting in that cell. That may give some hints (beside the error message, which is even more important)
Thanks for the quick response. 1) So offset(-1,1) is located in the changesFound= changesFound + 1 line? 2) Yes, you are right. I meant all rows in column B that now have a string, hello, goodbye, etc, and for the script to recognize that there is a string there and to apply a formula to those strings to show in column C. 3) The error states that there is a problem with the range.
changesFound is just a variable that we increment. The value for that variable is held only in that variable and we never write it out anwhere. We do, however, by the use of the SELECT CASE statement write out a string to Column B through the use of Offset(1, -1).value = "somestring". I suppose you can put whatever you like in Column C. If you want to do it in VBA you could just add searchCell.Offset(-1, 2).value = "This is column C, and column B is currently populated!". Still not sure on that error. Is there an error number associated to it?
Okay, I see. We can insert Offset(1,-1).value= "somestring" after the Do Until statement? Same with the one controlling column C? Looks like the exact Run-time error is : ' -2147417848 (80010108)': Method 'Value' of object 'Range' failed and it points to currentValue=searchCell.Value
|
0

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
Improve this answer

2 Comments

This is brilliant. Thank you! To add on to this, how would I apply a formula to all of the sampleA, sampleB, etc values above each hello?
You are welcome. If my answer solved your original question then you may consider marking it as accepted. Thank you. As for the “formula above each hello” you may want to give more details
0

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
Improve this answer

2 Comments

Thanks, Error 1004! This worked really well and I learned from the structure of this script. I have two questions about how to modify it. Now, instead of being very specific and naming sampleA, sampleB, etc., how can I tell the script to just read column A as a string? Is there a way I can just call these strings "sampleX" so that I can go all the way down a huge worksheet without specifying? With this, can I loop all the way until the end of the worksheet to say "hello", for example, every time there is a different string? Thank you thank you!
Glad to hear that a help! Yes , you can do by loop each line and change "PreviousString = .Range("A" & Row - 1).Value" to "sampleX=.Range("A" & Row - i).Value" and play around with the code. Give it a try and let me know if you need more help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.