Userform TextBox Value being Find Value Offset - Runtime Error 91

[Konversed]

Hi All,

First time poster so please excuse any formatting issues and such.

I'm new to VBA but have been giving it a shot, I've become stuck after everything going so smoothly.

I'm getting the error message : [h=1]"Runtime Error 91: Object variable or with block variable not set"[/h]
Private Sub UserForm_Initialize()


Dim FlatValue1 As Range




PropVal = Sheets("INFO").Cells(41, 2).Value
FlatValue1 = Sheets("INFO").Cells(42, 2).Value


With Sheets("PROPS")


Set PropFoundValue1 = Sheets("PROPS").Range("A:A").Find(What:=PropVal.Value)


If PropValue1.Offset(0, 1).Value = FlatValue1 Then


RentTextBox.Text = PropValue1.Offset(0, 2)
StartDateTextBox.Text = PropValue1.Offset(0, 3)
TermStartDateTextBox.Text = PropValue1.Offset(0, 4)
RentDueTextBox.Text = PropValue1.Offset(0, 6)
DepositTextBox.Text = PropValue1.Offset(0, 7)

ElseIf PropValue1.Offset(1, 0) = PropVal And PropValue1.Offset(1, 1) = FlatValue1 Then


RentTextBox = PropValue1.Offset(1, 2)
StartDateTextBox = PropValue1.Offset(1, 3)
TermStartDateTextBox = PropValue1.Offset(1, 4)
RentDueTextBox = PropValue1.Offset(1, 6)
DepositTextBox = PropValue1.Offset(1, 7)





Else
MsgBox "Bug Found - Consult Rob"
End If


End With


With ComboBox1
.AddItem "Committed"
.AddItem "Managed"
.AddItem "Straight Let"

End With


With BreakClauseComboBox1
.AddItem "Yes"
.AddItem "No"

End With


End Sub




Any help is hugely appreciated! If you'd like me to upload the full excel I'd be happy to.

Kind Regards,

Rob.

 

[dmt32]

Hi,
welcome to the board.

see if update to your code does what you want:

Private Sub UserForm_Initialize()


    Dim PropFoundValue1 As Range
    Dim FlatValue1 As String, PropVal1 As String
    
    
    PropVal = Sheets("INFO").Cells(41, 2).Value
    FlatValue1 = Sheets("INFO").Cells(42, 2).Value
    
    
    Set PropFoundValue1 = Sheets("PROPS").Range("A:A").Find(What:=PropVal)
    
    If Not PropFoundValue1 Is Nothing Then
    
        If PropFoundValue1.Offset(0, 1).Value = FlatValue1 Then
    
            RentTextBox.Text = PropFoundValue1.Offset(0, 2)
            StartDateTextBox.Text = PropFoundValue1.Offset(0, 3)
            TermStartDateTextBox.Text = PropFoundValue1.Offset(0, 4)
            RentDueTextBox.Text = PropFoundValue1.Offset(0, 6)
            DepositTextBox.Text = PropFoundValue1.Offset(0, 7)
    
        ElseIf PropFoundValue1.Offset(1, 0) = PropVal And PropFoundValue1.Offset(1, 1) = FlatValue1 Then
    
        RentTextBox = PropFoundValue1.Offset(1, 2)
        StartDateTextBox = PropFoundValue1.Offset(1, 3)
        TermStartDateTextBox = PropFoundValue1.Offset(1, 4)
        RentDueTextBox = PropFoundValue1.Offset(1, 6)
        DepositTextBox = PropFoundValue1.Offset(1, 7)
        
        Else
        
        MsgBox "Bug Found - Consult Rob"
        
        End If
    
    Else
        MsgBox "Record Not Found", 48, "Not Found"
    End If
    
    ''REST OF CODE
    '
    '
    '




End Sub

Dave

 

[Konversed]

Hi Dave,

Thank you so much for your reply, I'll give this a shot on Monday and let you know.

Really appreciate you taking the time.

Kind Regards,

Rob.

 

[Konversed]

Hi Dave,

Progress I think, I'm guessing you just cleaned up my shoddy code.

I'm no longer getting the runtime error which is fantastic but not working at intended (at all). The combo boxes remain blank.

I'm trying to have the code find a value(s) in column A (property address)

Then

Find the appropriate value in column B also (flat address)

Then

Fill combo boxes with values based on values in that row



Would it help if I posted the excel file? I've come so far on my own but I'm stumped here ha

I'm very grateful.

Many Thanks,

Rob.

 

[dmt32]

Hi,
just place a copy of your workbook in a public dropbox & provide link to it here.
I and others here may be able to assist further

Dave

 

[Konversed]

Hi Dave,

Thanks for the advice:

https://www.dropbox.com/s/t90uyq4xlcn4a8n/Automated1.xlsm?dl=0

Kind Regards,

Rob.

 

[Konversed]

*Disgusting Self Bump*

Anyone, please help QQ

 

[Konversed]

Hi,
welcome to the board.

see if update to your code does what you want:

Private Sub UserForm_Initialize()


    Dim PropFoundValue1 As Range
    Dim FlatValue1 As String, PropVal1 As String
    
    
    PropVal = Sheets("INFO").Cells(41, 2).Value
    FlatValue1 = Sheets("INFO").Cells(42, 2).Value
    
    
    Set PropFoundValue1 = Sheets("PROPS").Range("A:A").Find(What:=PropVal)
    
    If Not PropFoundValue1 Is Nothing Then
    
        If PropFoundValue1.Offset(0, 1).Value = FlatValue1 Then
    
            RentTextBox.Text = PropFoundValue1.Offset(0, 2)
            StartDateTextBox.Text = PropFoundValue1.Offset(0, 3)
            TermStartDateTextBox.Text = PropFoundValue1.Offset(0, 4)
            RentDueTextBox.Text = PropFoundValue1.Offset(0, 6)
            DepositTextBox.Text = PropFoundValue1.Offset(0, 7)
    
        ElseIf PropFoundValue1.Offset(1, 0) = PropVal And PropFoundValue1.Offset(1, 1) = FlatValue1 Then
    
        RentTextBox = PropFoundValue1.Offset(1, 2)
        StartDateTextBox = PropFoundValue1.Offset(1, 3)
        TermStartDateTextBox = PropFoundValue1.Offset(1, 4)
        RentDueTextBox = PropFoundValue1.Offset(1, 6)
        DepositTextBox = PropFoundValue1.Offset(1, 7)
        
        Else
        
        MsgBox "Bug Found - Consult Rob"
        
        End If
    
    Else
        MsgBox "Record Not Found", 48, "Not Found"
    End If
    
    ''REST OF CODE
    '
    '
    '




End Sub

Dave

Hi Dave,

I can't believe I was so dense, I tried this again today with a fresh mindset and it works perfectly! You're a god who walks among men my friend!

Muchas Gracias!

Rob.