Userform TextBox Value being Find Value Offset - Runtime Error 91

Konversed

New Member
Joined
Mar 5, 2016
Messages
6
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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,382
Office Version
  1. 2019
Platform
  1. Windows
Hi,
welcome to the board.

see if update to your code does what you want:

Code:
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

New Member
Joined
Mar 5, 2016
Messages
6
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

New Member
Joined
Mar 5, 2016
Messages
6
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,382
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 5, 2016
Messages
6
Hi,
welcome to the board.

see if update to your code does what you want:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top