Excel Do while to obtain adjacent cells when cell equals value

Question

I have two sheets. Sheet 1 (Customer tracker - OC) has a column which contains either yes or no for every row. If the value is yes , the two adjacent cells to the right have two values in them.

I would like to create a do while / for each macro that cycles through each cell in the yes/no column in sheet1, and where the value is yes , creates a new row in the blank sheet 2 which the two adjacent values from sheet 1.

I.e

Sheet1

   A    B    C
1  YES  4    6 
2  NO 
3  YES  91   42

desired result in sheet 2 ("contracts")

   A    B
1  4    6 
2  91   42

I have the below code , but it's erroring with "overflow".

    Sub ForEach_Loop()

Dim TrackerLastRow As Integer
Dim TrackerCurrentRow As Integer
Dim ContractLastRow As Integer
Dim ContractCurrentRow As Integer

TrackerLastRow = Worksheets("Customer Tracker - OC").Range("T1").End(xlDown).Row
    TrackerCurrentRow = 1

    ContractLastRow = Worksheets("Contracts").Range("A1").End(xlDown).Row
    ContractCurrentRow = 1

    Do While TrackerCurrentRow <= TrackerLastRow

      If Worksheets("Customer Tracker - OC").Range("T" & TrackerCurrentRow).Value = "Yes" Then
            Worksheets("Contracts").Range("A" & ContractCurrentRow) = Worksheets("Customer Tracker - OC").Range("T" & TrackerCurrentRow).Offset(, 1).Value
            Worksheets("Contracts").Range("B" & ContractCurrentRow) = Worksheets("Customer Tracker - OC").Range("T" & TrackerCurrentRow).Offset(, 2).Value
            TrackerLastRow = TrackerLastRow + 1
            TrackerCurrentRow = TrackerCurrentRow + 1
            ContractLastRow = ContractLastRow + 1
            ContractCurrentRow = ContractCurrentRow + 1
        End If

        TrackerCurrentRow = TrackerCurrentRow + 1
        ContractCurrentRow = ContractCurrentRow + 1

        Loop


End Sub

Any help would be appreciated

Start by replacing all Integer with Long then never use Integer again. — user4039065
I tried replacing Integer, but the macro didn't seem to do anything, looks like there are more issues. — M.Douda
Overflow error means you are trying to save a value that it's over the limit of each type of data variable. Example: You cannot save the number 1000 in a variable defined as Byte because the limit of Byte data is 255. So as @Jeeped said, replace all integer with Long, and then debug your code to see where it fails. Execute it with F8 — Foxfire And Burns And Burns
filter column A for "yes" and then copy/paste columns B:C in one step isn't an option? — Dirk Reichel
why are you incrementing variables inside the if statement inside the while loop? what purpose does the variable ContractLastRow serve? — Shazu

Unknown Unknown · Accepted Answer · 2018-07-26T10:32:19

It seems you are over-incrementing your vars and over-extending the functional limits of an integer type var.

Option Explicit

Sub ForEach_Loop()

    Dim TrackerLastRow As Long, TrackerCurrentRow As Long
    Dim ContractLastRow As Long, ContractCurrentRow As Long

    With Worksheets("Contracts")
        ContractLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        ContractCurrentRow = 1
    End With

    With Worksheets("Customer Tracker - OC")

        TrackerLastRow = .Cells(.Rows.Count, "T").End(xlUp).Row
        TrackerCurrentRow = 1

        Do While TrackerCurrentRow <= TrackerLastRow

            If .Cells(TrackerCurrentRow, "T").Value = "Yes" Then
                Worksheets("Contracts").Range("A" & ContractCurrentRow) = .Range("T" & TrackerCurrentRow).Offset(0, 1).Value
                Worksheets("Contracts").Range("B" & ContractCurrentRow) = .Range("T" & TrackerCurrentRow).Offset(0, 2).Value
                ContractCurrentRow = ContractCurrentRow + 1
            End If

            TrackerCurrentRow = TrackerCurrentRow + 1

        Loop
    End With


End Sub

Excel Do while to obtain adjacent cells when cell equals value

2 Answers