Search code examples
vbaexcelexcel-2010excel-2007

How to clear error 1004 on vba on a 16000 lines file?

I'm currently working on a 16 000 rows excel file

  • Column B is the date I want to update
  • Column H is the starting date
  • Column I is the ending date
  • Column K is the number of times date need to be updated (updated on n rows)

The idea is to display all dates from starting date to ending date in column B (one date per column).

You'll find below my current codes. I'm a beginner, it probably includes a lot of mistakes.

Sub Dates()
    Dim i As Long
    Dim k As Long
    Dim MyDate As Long
    Dim EndDate As Long
    Dim EndRowA As Long
    Dim EndRowB As Long
    Dim EndRowH As Long
    Dim StartDate As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")

    StartDate = ws.Cells(ws.Rows.Count, "H").Value
    MyDate = ws.Cells(ws.Rows.Count, "B").Value
    EndDate = ws.Cells(ws.Rows.Count, "I").Value
    EndRowA = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    EndRowB = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
    EndRowH = ws.Cells(ws.Rows.Count, 8).End(xlUp).Row

    Do While (i <= EndRowH And i <= EndRowA And i <= EndRowB)
        If ws.Cells(i, "H").Value = ws.Cells(i, "I").Value Then
            GoTo Continue
        ElseIf ws.Cells(i, "H").Value = ws.Cells(i, "I").Value Then
            For k = 1 To ws.Cells(i, k).Value
                ws.Cells(i + 1, "B").Select
                ws.Cells(i, "B").Value = ws.Cells(i - 1, "H").Value + 1
                Exit For
Continue:
            Next k
        End If
    Loop
End Sub

Can you please provide me with some help, I keep getting error 1004 on line :

If ws.Cells(i,"H").Value = ws.Cells(i, "I").Value Then

Main columns in the spreadsheet should look as follows:

Type    Date     Start date   End Date   #

A   01/01/2018  01/01/2018  01/10/2018  10
A   01/02/2018  01/01/2018  01/10/2018  10
A   01/03/2018  01/01/2018  01/10/2018  10
A   01/04/2018  01/01/2018  01/10/2018  10
A   01/05/2018  01/01/2018  01/10/2018  10
A   01/06/2018  01/01/2018  01/10/2018  10
A   01/07/2018  01/01/2018  01/10/2018  10
A   01/08/2018  01/01/2018  01/10/2018  10
A   01/09/2018  01/01/2018  01/10/2018  10  
A   01/10/2018  01/01/2018  01/10/2018  10
B   02/06/2018  02/06/2018  02/10/2018  5
B   02/07/2018  02/06/2018  02/10/2018  5
B   02/08/2018  02/06/2018  02/10/2018  5
B   02/09/2018  02/06/2018  02/10/2018  5
B   02/10/2018  02/06/2018  02/10/2018  5

Table

I thank you in advance

Solution

  • First, you need to initialize i to a numeric value, otherwise row 0 will result with an error.

    Second, your loop can be improved:

    Do While (i <= EndRowH And i <= EndRowA And i <= EndRowB)

    You can look for the Min value of these 3 EndRows, like in the section below, and then switch to For loop:

    EndRow = WorksheetFunction.Min(EndRowA, EndRowB, EndRowH)       
For i = 1 To EndRow ' <-- starting loop from the 1st row

    Third: you have If .Cells(i, "H").Value = .Cells(i, "I").Value Then and in your ElseIf you have ElseIf .Cells(i, "H").Value = .Cells(i, "I").Value Then, which is the exact same criteria >> most likely yuo meant to use something else.

    Fourth: I think you misplaced the location of your Continue: label.

    See code below for more explanations about your code errors.

    Modified Code

    Option Explicit

Sub Dates()

Dim i As Long
Dim k As Long
Dim MyDate As Long
Dim EndDate As Long
Dim EndRowA As Long
Dim EndRowB As Long
Dim EndRowH As Long
Dim EndRow As Long
Dim StartDate As Long
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

With ws
    StartDate = .Cells(.Rows.Count, "H").Value
    MyDate = .Cells(.Rows.Count, "B").Value
    EndDate = .Cells(.Rows.Count, "I").Value
    EndRowA = .Cells(.Rows.Count, 1).End(xlUp).Row
    EndRowB = .Cells(.Rows.Count, 2).End(xlUp).Row
    EndRowH = .Cells(.Rows.Count, 8).End(xlUp).Row

    ' get the minimumm last row from: EndRowA, EndRowB, EndRowH
    EndRow = WorksheetFunction.Min(EndRowA, EndRowB, EndRowH)

    For i = 1 To EndRow ' <-- starting loop from the 1st row
        If .Cells(i, "H").Value = .Cells(i, "I").Value Then
            GoTo Continue
        ElseIf .Cells(i, "H").Value = .Cells(i, "I").Value Then ' <-- same exact criteria as in your If
            For k = 1 To ws.Cells(i, k).Value
                ws.Cells(i + 1, "B").Select ' <-- not sure what do you need this line ???
                .Cells(i, "B").Value = .Cells(i - 1, "H").Value + 1
                Exit For
Continue: '<-- not sure if this is placed correctly ??
            Next k
        End If
    Next i

End With

End Sub