Invalid next control variable reference ошибка vba

I’m sorry, i shouldve explained what i was trying to produce than asking to fix something i wanted to do. My experience in vba has been self-taught, and i’m a little new to asking for help.

The script Floris produced seemed to have function but not as intended. Turns out what i wrote is a little outdated, and needs to be wiped and restarted. This was actually an old script i started a few months back that worked off of a web-query. But the website went thru some changes and now the script is all over the place.

the main issue i was having was a compile-error «Invalid Next Control Variable Reference» Which turns out to be caused by an open ‘Do while’ loop, that doesnt seem to have much of an exit point from the research i looked up. Was supposed to have used another ‘If’ command instead. At the same time, when attempting to solve that ‘Do While’ i added an extra ‘Next’ (cause i thought they were compatible), and it screwed with the script.

Hard to explain.. But the ‘Do While’ i used, i wanted it to combine the values only if the number of values were greater

rnumbers = Rows(ActiveCell.Range("A3").End(xlDown)) + 3
'or CellCount = ActiveCell.Range("A" & Rows.Count).End(xldown).Row

Do While Rows(ActiveCell.Range("A3").End(xlDown)) > 3

But instead it was supposed to be

Dim CellCount As Range

CellCount = ActiveCell.Range("A" & Rows.Count).End(xlDown).Row + 2
'cause its the active cell + two additional cells

If CellCount > 3

Which then opens up into the script Floris submitted. (But that failed too, because of what was stated above).

Thanks again, hope that it explains everything… Sorry if i wasted your time with that one Floris, really do appreciate the assistance. Just wish i had asked for the help sooner, would have saved me a lot of frustration that i’m dealing with now. >_>

Basically, I am trying to pull the data from an Excel file to this worksheet (Auto_Update Sub) and the code is described below:

Sub Auto_Update()
Dim filename As String
Dim r As Integer
Dim i As Double
Dim t As Integer
Dim DPR As Object
Dim new_DPR As Object
Dim well As Object
Dim x As Integer

If IsEmpty(ThisWorkbook.Sheets("SD-28P").Cells(1, 35)) = True Then
ThisWorkbook.Sheets("SD-28P").Cells(1, 35) = Date - 2
End If
Excel.Application.Visible = False
For i = Date - ThisWorkbook.Sheets("SD-28P").Cells(1, 35) To 1 Step -1
    filename = "Z:\DPR\DPR_" + Format(Date - i, "yyyymmdd") + ".xls"
    Set DPR = Excel.Application.Workbooks.Open(filename)
    Set new_DPR = DPR.Worksheets("Daily Production Report")
    For x = 247 To 272 Step 1
        If Trim(new_DPR.Cells(x, 2).Value) = "SD-01PST" Then t = x
        Exit For
    For r = t To t + 35 Step 1
        Set well = ThisWorkbook.Worksheets(Trim(new_DPR.Cells(r, 2).Value))
        f = First_Empty(well, 4)
        If new_DPR.Cells(r, 6).Value = Date - i Then
                new_DPR.Cells(r, 6).Copy
                well.Cells(f, 1).PasteSpecial (xlPasteValues)
                new_DPR.Cells(r, 8).Copy
                well.Cells(f, 3).PasteSpecial (xlPasteValues)
                new_DPR.Cells(r, 10).Copy
                well.Cells(f, 4).PasteSpecial (xlPasteValues)
                new_DPR.Range(new_DPR.Cells(r, 12), new_DPR.Cells(r, 17)).Copy
                well.Range(well.Cells(f, 5), well.Cells(f, 10)).PasteSpecial (xlPasteValues)
                new_DPR.Range(new_DPR.Cells(r, 20), new_DPR.Cells(r, 26)).Copy
                well.Range(well.Cells(f, 11), well.Cells(f, 17)).PasteSpecial (xlPasteValues)
                new_DPR.Range(new_DPR.Cells(r, 28), new_DPR.Cells(r, 30)).Copy
                well.Range(well.Cells(f, 18), well.Cells(f, 20)).PasteSpecial (xlPasteValues)
                well.Range(well.Cells(f - 1, 2), well.Cells(f - 1, 22)).Copy
                well.Range(well.Cells(f, 2), well.Cells(f, 22)).PasteSpecial (xlPasteFormats)
                well.Cells(f - 1, 1).Copy
                well.Cells(f, 1).PasteSpecial (xlPasteFormulasAndNumberFormats)
        End If
    Next r
    Excel.Application.CutCopyMode = False
    DPR.Saved = True
    DPR.Close
    ThisWorkbook.Application.CutCopyMode = False
Next i
ThisWorkbook.Sheets("SD-28P").Cells(1, 35) = Date
ThisWorkbook.Save
Excel.Application.Visible = True
ThisWorkbook.Sheets(4).Activate

But then, the code returns an error at the line: Next i (Invalid Next Control Variable Reference). I double checked the variable and the syntacx of the For … Next loop, however, I couldn’t not find any possible error. Please kindly help! Thank you very much in advance.