Run-time error 1004 when trying to open a VBA form

[Velosai]

This one has thrown me a little as I don't understand why it is even throwing the error.

I have it breaking on class module so I can see it is falling over on "lastRow = Sheets(CStr(y)).Range("A5", Range("A5").End(xlDown)).Rows.Count + 4".

When debugging (although it still falls over)
y = "2023" Can confirm there is a sheet called 2023
lastRow = 0 Not yet populated

I have also tried putting ActiveWorkbook in front of the sheet, in case it was being weird about that. ActiveWorkbook.FullName also reports the correct workbook.

Private Sub UserForm_Initialize()
    Dim x As Integer
    Dim y As Integer
    Dim cellString As String
    Dim person As String
    Dim lastRow As Integer
    
    y = year(Now)
    
    If Month(Now) < 4 Then
        y = y + 1
    End If
    
    ddlPosition.Clear
    lastRow = Sheets(CStr(y)).Range("A5", Range("A5").End(xlDown)).Rows.Count + 4 <- Errors here
    For x = 5 To lastRow
        cellString = "A" & x
        person = Sheets(CStr(y)).Range(cellString).Value
        If Len(person) > 0 And person <> "Events" Then
            ddlPosition.AddItem person
        End If
    Next


End Sub

[georgiboy]

Welcome to the forum Velosai,

y = "2003" Can confirm there is a sheet called 2003

y would be 2023, do you have a sheet named 2023?

I wish it was 2003 again...

-------------------------

I am guessing that you may be getting a Run-time error '6':

If you have one value in cell A5 then your 'Range("A5").End(xlDown)).Rows.Count' part will go to the bottom of the worksheet - when you then try to add the 4 as you do on the end of that line, it will produce an error.
Perhaps if you change that line to the below you will not get the error in the future:

lastRow = Sheets(CStr(y)).Range("A" & Rows.Count).End(xlUp).Row

IMO: It is a good idea to declare your variables, if you put the line 'Option Explicit' at the top of your module and then run the code, the errors you get will let you know which variables have not been declared.

Hope this helps

[Velosai]

Good catch ... typo on my behalf. I mean 2023 and yes there is a sheet called 2023.

The error message I am getting is definitely Run-time 1004, as per the title of the thread

I am already declaring my variables and can't see one I haven't declared ........ EDIT: Realised that I missed the x declaration when I was copying across the sub.

[georgiboy]

Sorry I should have looked closer at the title.

Does the line of code I proposed produce the same error?

EDIT: I have removed the +4 from the line of code proposed above as it was not needed.

[Velosai]

Ok, its just dawned on me why it is errors out. The code should be

lastRow = Sheets(CStr(y)).Range("A5", Sheets(CStr(y)).Range("A5").End(xlDown)).Rows.Count + 4

Because I am not firing the code from that sheet, so when I am firing it the range is weird and the count is negative which screws it all up.

[georgiboy]

Good spot,

Think you will be better off defining the last row with the below as the data would not have to be contiguous for it to work & you only need to reference the sheet once:

lastRow = Sheets(CStr(y)).Range("A" & Rows.Count).End(xlUp).Row

[Velosai]

Unfortunately, in this instance, no. Just because there are peculiarities with the sheets involved. My code seems to handle them, where are yours seems to over report the number of rows. But do agree, normally yours would be the better option.

[georgiboy]

That could have been down to the +4 that was on the end of that line when i first proposed it, it wasn't needed. I suppose it could also be that there is data under the area you are capturing with your line of code.

Either way you have it sorted

[amorapotter]

The error you're encountering (Run-time error 1004) indicates that there's an issue with the way you're referencing the range in your code. The issue is likely related to the way you're using Range without specifying the sheet for the second reference. The second Range("A5") doesn't explicitly reference a sheet, and it might be causing the error. mapquest