How to check for equal values in cells with a for loop?

Question

I want to check if the text value in a cell is the same as in the cell below with a for loop.

If the value in Cell(1) and Cell(2) does not match I want the value from Cell(3) written in Cell(4).

I get an error

"Overflow (Error 6)"

Dim i As Integer

For i = 1 To Rows.Count

    If Cells(2 + i,21) = Cells(3 + i,21) Then
        i = i + 1
    Else
        a = Cells(3 + i, 1)
        j = j + 1
        Cells(228 + j, 3) = a
    End If

Next i

End Sub

I have a production output and a timeline from 6 am to 12 am and I want to create a timetable as seen below.

Screenshot:

Your `a` variable is not declared: `Dim a as Range` and also not set: `Set a = Cells(3+i,1)`. Also declare `i` as `Long`, not `Integer` — Teamothy, Oct 23 '19 at 08:50
To start, replace `For i = 1 to Rows.Count` with a [more reliable lastrow statement](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row/38882823#38882823). — Plutian, Oct 23 '19 at 08:51
Don't use `Integer` variables (causing overflow really easy), use `Long` instead. As per @Plution, do also use a more reliable way of retrieving the last row. Furthermore, I would recommend the use of at least a worksheet reference and loop through an array (through memory) instead of cells. That being said. Please can you include some mockup sample data and expected result? — JvdV, Oct 23 '19 at 09:04
@Teamothy, I don't think `a` variable is meant to be a range object. Instead I think OP is trying to write `Cells(228 + j, 3) = Cells(3 + i, 1)` — JvdV, Oct 23 '19 at 09:05
@JvdV yes, my bad, he wanted a value not range, would be easier taking your approach. — Teamothy, Oct 23 '19 at 09:19
@JvdV I included some mockup sample data and expected result. And edited the code like you said. — CosmoCramer, Oct 23 '19 at 09:24
I edited the cells since i switched up rows and columns in the if statement. Now i get output but just one: ("6:25"), its the first time both values don't match. — CosmoCramer, Oct 23 '19 at 09:34

score 1 · Answer 1 · answered Oct 23 '19 at 09:47

Here I'm using a dictionary which will store every time for every product comma separated, so later will split that and take the first and last occurrence:

Sub TimeTable()

        'Declare an array variable to store the data
        'change MySheet for your sheet name
        arr = ThisWorkbook.Sheets("MySheet").UsedRange.Value 'this will store the whole worksheet, the used area.

        'Declare a dictionary object
        Dim Products As Object: Set Products = CreateObject("Scripting.Dictionary")

        'Loop through the array
        Dim i As Long
        For i = 3 To UBound(arr) 'start from row 3 because of your screenshoot
            If arr(i, 21) = vbNullString Then GoTo NextRow 'if column U is empty won't add anything
            If Not Products.Exists(arr(i, 21)) Then '21 is the column index for column U
                Products.Add arr(i, 21), arr(i, 1)
            Else
                Products(arr(i, 21)) = arr(i, 21) & "," & arr(i, 1)
            End If
NextRow:
        Next i
        Erase arr

        'Redim the array to fit your final data, 4 columns and as many rows as products
        ReDim arr(1 To Products.Count + 1, 1 To 4)

        'Insert the headers
        arr(1, 1) = "Time"
        arr(1, 4) = "Product / Error"

        'Now loop through the dictionary
        Dim Key As Variant, MySplit As Variant
        i = 2
        For Each Key In Products.Keys
            MySplit = Split(Products(Key), ",")
            arr(i, 1) = MySplit(LBound(MySplit))
            arr(i, 2) = "-"
            arr(i, 3) = MySplit(UBound(MySplit))
            arr(i, 4) = Key
            i = i + 1
        Next Key


        'I don't know where are you going to paste your data, so I'm making a new worksheet at the end of your workbook
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        With ws
            .Range("A1").Resize(UBound(arr), UBound(arr, 2)).Value = arr
            .Range("A1:C1").Merge
        End With

End Sub

score 1 · Accepted Answer · answered Oct 23 '19 at 09:51

You could use

Option Explicit

Sub test()

    Dim LastRowA As Long, i As Long, j As Long, LastRowW As Long
    Dim StartTime As Date, EndTime As Date, strOutPut

    j = 0

    With ThisWorkbook.Worksheets("Sheet1")

        LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = 3 To LastRowA

            If i > j - 1 Then

                StartTime = .Range("A" & i).Value
                strOutPut = .Range("U" & i).Value

                For j = i + 1 To LastRowA + 1

                    If strOutPut <> .Range("U" & j).Value Then

                        EndTime = .Range("A" & j - 1).Value
                        LastRow = .Cells(.Rows.Count, "W").End(xlUp).Row

                        .Range("W" & LastRow + 1).Value = StartTime
                        .Range("X" & LastRow + 1).Value = EndTime
                        .Range("Y" & LastRow + 1).Value = strOutPut

                        Exit For

                    End If

                Next j

            End If

        Next i

    End With

End Sub

Result

How to check for equal values in cells with a for loop?

2 Answers2