CODEWITHSUNDEEP
excelvba
Michelle Schroeder
Michelle Schroeder

Reputation: 23

Comparing the active cell value to the value of the cell before it (VBA)

First off, thank you for clicking into my question

I want the code to go through a list of values in a sheet called "Tests" and copy those values into a sheet called "List". The values in "Tests" are sometimes repeated though and I only want "List" to have unrepeated values.

Example:

"Tests" column A: dog, dog, cat, rabbit, rabbit, rabbit, turtle, turtle

"List" column A: dog, cat, rabbit, turtle

Can someone please point me in the right direction/ tell me where I went wrong? This is my code:

Option Explicit

Sub Main()

'create new sheet
' ActiveWorkbook.Sheets.Add

'adds a sheet and names it "List"
Sheets.Add.Name = "List"

'Select sheet1 (Tests)
Sheets("Tests").Activate

'Activate cell A2 in Tests -- not really necessary but ill leave it for now
Range("A2").Activate

'Create for loop for cells a2 through a1019
Dim cell As Integer
Dim listcell As Integer
listcell = 1



    For cell = 2 To 1019

    'create if statement for if Tests cell does match previous cell
    If (StrComp(ActiveCell.Value, ActiveCell.Offset(-1, 0).Value, vbTextCompare) = 0) Then
            
            ActiveWorkbook.Sheets("Tests").Range("A" & (cell + 1)).Select
            Sheets("List").Range("A" & listcell).Value = Sheets("Tests").Range("A" & cell).Value
            listcell = listcell + 1

    'else if doesn't match then write down and move to next cell in sheet
    ElseIf (StrComp(ActiveCell.Value, ActiveCell.Offset(-1, 0).Value, vbTextCompare) = 1) Then
            
            Application.Goto ActiveWorkbook.Sheets("List").Range("A" & cell)
            Sheets("List").Range("A" & listcell).Value = Sheets("Tests").Range("A" & cell).Value
            listcell = listcell + 1
    
    ElseIf (StrComp(ActiveCell.Value, ActiveCell.Offset(-1, 0).Value, vbTextCompare) = -1) Then
        
            Application.Goto ActiveWorkbook.Sheets("List").Range("A" & cell)
            Sheets("List").Range("A" & listcell).Value = Sheets("Tests").Range("A" & cell).Value
            listcell = listcell + 1

End If
Next
End Sub

Upvotes: 0

Views: 430

Answers (1)

dumples
dumples

Reputation: 91

If you need to use VBA as part of a bigger process that uses VBA or just don't like using functions that spill, then you can still use unique

Dim lArray As Variant
lArray = Application.WorksheetFunction.Unique(Thisworkbook.worksheets("Tests").range("A1:A1290"))

Then once you have it in a vba array, it is nearly trivial to put it somewhere else in your workbook. You might have to trap for the case where only 1 value is unique, since that looks like it puts it in a 1D array instead of a 2D array.

Upvotes: 1

Related Questions