VBA Run-timer Error 1004 with .offSet()

Question

I have a workbook containing properties, each with a respective property ID. The code below aims to find the row corresponding to the ID chosen from a list in a comboBox and then fill the remainder of the fields in the form with the data in it's row.

Private Sub propertyCodeCombo_change()
    Set wks = Application.Workbooks("Book1.xlsm").Worksheets("Property")
    Dim propertyCell As Range
    Set propertyCell = wks.Range("A2")
    Do Until propertyCell.Value = propertyCodeCombo.Value
        Set propertyCell = propertyCell.Offset(1, 0)   <--Error Occurs here
    Loop
    addressText = propertyCell.Offset(0, 1).Value
    suburbText = propertyCell.Offset(0, 2).Value
    propertyTypeCombo = propertyCell.Offset(0, 3).Value
    bedroomsText = propertyCell.Offset(0, 4).Value
    bathroomsText = propertyCell.Offset(0, 5).Value
    weeklyRentalFeeText = propertyCell.Offset(0, 6).Value
    statusCombo = propertyCell.Offset(0, 7).Value
    commisionText = propertyCell.Offset(0, 8).Value
    ownerCodeCombo = propertyCell.Offset(0, 9).Value
End Sub

I find strange as I have almost identical code as seen below that does not cause an error and functions perfectly.

Set wks = Application.Workbooks("Book1.xlsm").Worksheets("Property")
Dim propertyCell As Range
Set propertyCell = wks.Range("A2")
Do Until IsEmpty(propertyCell)
    Set propertyCell = propertyCell.Offset(1, 0)
Loop

why not just propertyCell.entireColumn.Find(propertyCodeCombo.Value,,xlWhole)? If the find is nothing then just catch the error. — Rosetta
– Rosetta, Commented Sep 16, 2016 at 4:19

ThunderFrame · Accepted Answer · 2016-09-16 04:11:28Z

1

Your code gets the error because you don't find a match, so your Offset command ends up reaching the last row of the sheet. Offsetting one more row will cause an error.

That's why your other similar code works - you apply a different test, so you exit the loop earlier.

You'll get much better performance with this code, as it uses an array instead of Offset:

Private Sub propertyCodeCombo_change()
  Dim wks As Worksheet

    Set wks = ThisWorkbook.Worksheets("Property")
    Dim propertyCell As Range
    Set propertyCell = wks.Range("A2")

    Dim comboValue As Variant
    comboValue = propertyCodeCombo.Value

    Dim props As Variant
    props = wks.UsedRange.Value

    If IsArray(props) Then
    Dim propRow As Long
      For propRow = LBound(props) To UBound(props)

        If props(propRow, 1) = comboValue Then
          addressText = props(propRow, 2)
          suburbText = props(propRow, 3)
          propertyTypeCombo = props(propRow, 4)
          bedroomsText = props(propRow, 5)
          bathroomsText = props(propRow, 6)
          weeklyRentalFeeText = props(propRow, 7)
          statusCombo = props(propRow, 8)
          commisionText = props(propRow, 9)
          ownerCodeCombo = props(propRow, 10)
          Exit For
        End If
      Next propRow
    End If

End Sub

edited Sep 16, 2016 at 4:11

answered Sep 16, 2016 at 4:05

ThunderFrame

9,5162 gold badges31 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4039065 Over a year ago

An application.match returned to a variant may be appropriate. I find that the operation is usually completed before the time that sucking the .UsedRange into an array takes. Of course, there would also be the additional time of looking up all of the subsequent values.

Collectives™ on Stack Overflow

VBA Run-timer Error 1004 with .offSet()

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related