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

[Mooncake]

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.

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

 

[Roderick_E]

application.match(lotnum, range,0) will return the row number

 

[Mooncake]

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.

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

 

[Roderick_E]

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.

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.

Lot_List = Application.Match(lotnum, "[COLOR=#ff0000]Data!M:M[/COLOR]", 0)

 

[Mooncake]

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.

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