"<Application-defined or object-defined error> Error 1004"

1
votes

I have a simple for-next to fill some labels on a userform but can not figure out why a keep getting a " Error 1004" When I type the Contr.Caption the .Caption function is missing?

Dim Control_Name As String
Dim DSlot As Long
Dim DLName As Long
Dim Contr As Control

With Worksheets("apartments").Range("depositslot")
    DSlot = .Find("Slot").Row + 1
    DLName = .Find("Last").Column
End With

For Each Contr In Layout.Controls
    If TypeName(Contr) = "Label" Then
        Control_Name = Mid(Contr.Name, 1, 6)
            If Control_Name = "LBName" Then
                Contr.Caption = Worksheets("apartments").Range(DSlot, DLName).Value
                DSlot = DSlot + 1
            End If
    End If
Next
2
vbaexcel
Caution that .Find 'remembers' all of the options that were used last it was run through, even on the worksheet. Optional parameters like LookAt:=(xlPart or xlWhole), MatchCase:=(True or False), SearchFormat:=(True or False) shouldn't be left to default to whatever was used last. - user4039065

2 Answers

5
votes

Your line saying:

Contr.Caption = Worksheets("apartments").Range(DSlot, DLName).Value

should be

Contr.Caption = Worksheets("apartments").Cells(DSlot, DLName).Value

The Range property does not accept two parameters of type Long.

2
votes

Try changing the "Range" object by "Cells", since you have references of "Row, Column". You should have something like this:

Contr.Caption = Worksheets("apartments").Cells(DSlot, DLName).Value