UserForm: Enter a number for the line to which the data goes

Mooncake

New Member
Joined
Apr 6, 2019
Messages
27
I'm building a Rent Sheet. I'm using a UserForm to gather information (i.e. Lot#, Amount Paid, Check#, etc.)
I would like to use the Lot # as a reference for my table.
Example: If I enter Lot# 32 and click 'Submit', then the information starts at cell N34 (The starting cell of information for Lot# 32) of my table.
as you can see I have no problem with 'Offset' to LIST information down the sheet, but this one is a bit more difficult for me.
In short, I would like the Lot # to tell Excel where to put the data.

Code:
Sub Continue_Button_Click()
'Clicking the continue buton will do the following:
'
'
If Background_CheckBox = True Then
Sheets("Data").Range("Data_Start").Offset(1, 8).Value = "65.00"
End If


Sheets("Data").Range("Data_Start").Offset(1, 1).Value = Check_MO
Sheets("Data").Range("Data_Start").Offset(1, 16).Value = Amount_Paid


Unload Rent_UF


End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Thanks for the advice.
I guess I need a bit more help. Is it okay to use a dropdown box for Lot#, or should I be using a textbox? Does it matter?
It's still not working, although I am not sure how to use the 1 line of code offered.

Code:
Private Sub Lot_List_Change()
Dim Lot_List As Integer
Lot_List = Application.Match(lotnum, "Data!M3:M53", 0)
End Sub
 
Upvote 0
Thanks for the advice.
I guess I need a bit more help. Is it okay to use a dropdown box for Lot#, or should I be using a textbox? Does it matter?
It's still not working, although I am not sure how to use the 1 line of code offered.

Rich (BB code):
Private Sub Lot_List_Change()
Dim Lot_List As Integer
Lot_List = Application.Match(lotnum, "Data!M3:M53", 0)
End Sub

It shouldn't matter whether dropdown or textbox. But let's match against the entire column so that the row number returned will be the actual row number. From there you can easily offset or whatever to get the columns you want.
Code:
Lot_List = Application.Match(lotnum, "[COLOR=#ff0000]Data!M:M[/COLOR]", 0)
 
Upvote 0
I guess I'm just not comprehending it. When I hit 'Continue' I get "Run-time error '13' Type mismatch"
I don't know what the mismatch is. My Lot# drop-down references column B (specifically B2:B53) which is on the sheet called Lists.
The column to which I want to navigate is Data!M:M
I'm doing my best to work this out, but I'm at a loss.:eek:



Code:
Sub Continue_Button_Click()
'Clicking the continue button will do the following:
'
Dim lotnum As Integer
Lot_List = Application.Match(lotnum, "Data!M:M", 0)
'
If Background_CheckBox = True Then
Sheets("Data").Range("Data_Start").Offset(1, 8).Value = "65.00"
End If


Sheets("Data").Range("Data_Start").Offset(1, 1).Value = Check_MO
Sheets("Data").Range("Data_Start").Offset(1, 16).Value = Amount_Paid




Unload Rent_UF


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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
Back
Top