Add Data to another Sheet with VBA

[tnttnt]

Hi guys,

Im having an issue with some code ive been working on - Im not the most advanced VBA coder but I know my way around.

Basically its a sheet for managing overtime which users do from a Frontend - They simply add thier overtime, hit send and it adds it to the backend database (currently using Sheet1 for testing purposes)
The frontends 'sendable' data is in Range B17:L36 and the idea is it adds it on the first available empty cell.

Ive got it working but it wont let me fire the Macro more than 3 times if there is no data in the sheet, and comes up with a Runtime error 1004 - Cells not found. If there is already data in there and ive used the macro 3 times already it will come up with the error again. Maybe im missing something? Any help would be greatly appreiciated.

Code Below:
Thanks

Sub test233()
Dim Nool As String
Dim allOk(0) As Boolean
Blank = ""

j = Sheets("Sheet1").Range("A1:A" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row 'THIS IS WHERE TO RUNTIME ERROR HITS AFTER 3 TIMES WHEN THERE IS NO DATA

For i = 17 To 36

           'TIME
        If Not Blank = Sheets("FRONTEND").Cells(i, 2).Value Then
               allOk(0) = True
          End If
                  
        If allOk(0) Then

            Sheets("Sheet1").Range("A" & j).Value = Sheets("FRONTEND").Cells(i, 2).Value       'DATE
            Sheets("Sheet1").Range("B" & j).Value = Sheets("FRONTEND").Cells(i, 3).Value       'FROM'
            Sheets("Sheet1").Range("C" & j).Value = Sheets("FRONTEND").Cells(i, 4).Value       'TO
            Sheets("Sheet1").Range("D" & j).Value = Sheets("FRONTEND").Cells(i, 5).Value       'CUSTOMER
            
            j = j + 1
        End If
        
        allOk(0) = False
                
    Next i
    
    Application.ScreenUpdating = True
End Sub

[tnttnt]

Ive figured out after a bit of testing that it must be something to do with the Frontend as it always stops/errors on/around row 36 on Sheet1.
Which is how many rows are allowed on the Frontend.

[tnttnt]

I figured it out, But thought I would add my solution for anyone else with the same issue.

NEW CODE:

Sub test233()
Dim Nool As String
Dim allOk(0) As Boolean
Blank = ""

j = Worksheets("Sheet1").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count + 1
'j = Sheets("Sheet1").Range("A1:A" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row

For i = 17 To 36

           'TIME
        If Not Blank = Sheets("FRONTEND").Cells(i, 2).Value Then
               allOk(0) = True
          End If
                  
        If allOk(0) Then

            Sheets("Sheet1").Range("A" & j).Value = Sheets("FRONTEND").Cells(i, 2).Value       'DATE
            Sheets("Sheet1").Range("B" & j).Value = Sheets("FRONTEND").Cells(i, 3).Value       'FROM'
            Sheets("Sheet1").Range("C" & j).Value = Sheets("FRONTEND").Cells(i, 4).Value       'TO
            Sheets("Sheet1").Range("D" & j).Value = Sheets("FRONTEND").Cells(i, 5).Value       'CUSTOMER
            
            j = j + 1
        End If
        
        allOk(0) = False
                
    Next i
    
    Application.ScreenUpdating = True
End Sub