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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,311
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
7,311
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,561
Messages
5,832,512
Members
430,138
Latest member
silver_john

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