Userform Help looking up different properties

[Patriot2879]

Hello good afternoon, please can you help me i have a user form where i have a 'Textbox1' to enter a diameter, and a 'Textbox2' to enter length. please see data.jpg.

for Example if diameter is M3, M4, M5, M6 on Row 1 from 'sheet1', then it enters the results in the 'Userform1', for ds (Testbox7), s (Textbox1), e (Textbox13), k (Textbox15), dw (Textbox17), c (Textbox19) and r into (Textbox21)

Please can you helpme.

 

[Patriot2879]

Change this line of code...

Select Case UCase(UserForm1.TextBox1.Text)

Dave

Thank you so much Dave that works amazing

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave

 

[Patriot2879]

You are welcome. Thanks for posting your outcome. Dave

Good morning, please can you help me once again, i have tried amending the code below for another part of my userform (please see below) where if the length for M3 is 8 then the answer is 7.65/8.35, and if m3 is 10 the answer will be 9.65/10.35 and so on. the code below works, but when i duplicate this and change the value and range i get an error. is there any way of putting the case M4 and value 8, 10, 12, 16, 20, 25 and 30 altogether? and range from B & 2 to B & 8 for example? Hope you can help me please?

With Sheets("Length l")
Select Case UCase(UserForm1.TextBox1.Text)
Case "M3"
If UserForm1.TextBox2.Value = 8 Then
UserForm1.TextBox5.Value = .Range("B" & 2)
End If

End Select
End With

End Sub

 

[NdNoviceHlp]

Hi again Patriot2879. As I indicated the last solution was "Not very eloquent or robust". This should work for any sheet if your data is set up the same. HTH. Dave
Userform code. Change command button number and sheet name to suit.

Private Sub CommandButton1_Click()
With Sheets("Length 1")
Select Case UCase(UserForm1.TextBox1.Text)
Case "M3"
UserForm1.TextBox5.Value = .Range("B" & GetRow(UserForm1.TextBox2.Value))
Case "M4"
UserForm1.TextBox5.Value = .Range("C" & GetRow(UserForm1.TextBox2.Value))
Case "M5"
UserForm1.TextBox5.Value = .Range("D" & GetRow(UserForm1.TextBox2.Value))
Case "M6"
UserForm1.TextBox5.Value = .Range("E" & GetRow(UserForm1.TextBox2.Value))
End Select
End With
End Sub

Function GetRow(InVal As Integer) As Integer
'input UserForm1.TextBox2.Value. Outputs row number based on "A"
Dim Cnt As Integer, LastRowA As Integer
With Sheets("Length 1")
    LastRowA = .Range("A" & .Rows.Count).End(xlUp).Row
'TextBox2.Value < A2
If .Range("A" & 2) > InVal Then
GetRow = 2
Exit Function
End If
'TextBox2.Value > A2 and less than A & lastrow
If .Range("A" & LastRowA) > InVal Then
For Cnt = 2 To LastRowA - 1
If .Range("A" & Cnt) <= InVal And .Range("A" & Cnt + 1) > InVal Then
GetRow = Cnt
Exit For
End If
Next Cnt
'TextBox2.Value >= A & lastrow
Else
GetRow = LastRowA
End If
End With
End Function

 

[Patriot2879]

Hi again Patriot2879. As I indicated the last solution was "Not very eloquent or robust". This should work for any sheet if your data is set up the same. HTH. Dave
Userform code. Change command button number and sheet name to suit.

Private Sub CommandButton1_Click()
With Sheets("Length 1")
Select Case UCase(UserForm1.TextBox1.Text)
Case "M3"
UserForm1.TextBox5.Value = .Range("B" & GetRow(UserForm1.TextBox2.Value))
Case "M4"
UserForm1.TextBox5.Value = .Range("C" & GetRow(UserForm1.TextBox2.Value))
Case "M5"
UserForm1.TextBox5.Value = .Range("D" & GetRow(UserForm1.TextBox2.Value))
Case "M6"
UserForm1.TextBox5.Value = .Range("E" & GetRow(UserForm1.TextBox2.Value))
End Select
End With
End Sub

Function GetRow(InVal As Integer) As Integer
'input UserForm1.TextBox2.Value. Outputs row number based on "A"
Dim Cnt As Integer, LastRowA As Integer
With Sheets("Length 1")
    LastRowA = .Range("A" & .Rows.Count).End(xlUp).Row
'TextBox2.Value < A2
If .Range("A" & 2) > InVal Then
GetRow = 2
Exit Function
End If
'TextBox2.Value > A2 and less than A & lastrow
If .Range("A" & LastRowA) > InVal Then
For Cnt = 2 To LastRowA - 1
If .Range("A" & Cnt) <= InVal And .Range("A" & Cnt + 1) > InVal Then
GetRow = Cnt
Exit For
End If
Next Cnt
'TextBox2.Value >= A & lastrow
Else
GetRow = LastRowA
End If
End With
End Function

Thank you so much, this works amazing thanks again

 

[NdNoviceHlp]

You are once again welcome. Have a nice day. Dave
ps. Please mark solution

 

[Patriot2879]

You are once again welcome. Have a nice day. Dave

Hi Good afternoon, please can you help me once more with the code below please i am trying to copy the data from the textboxes into certain cells in the 'PRC' sheet. I have put the info in where i want the data to go into ie, E14 to E23 but nothing goes into them, please can you help me.

Private Sub CommandButton5_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("PRC")
'find first row in database
irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Range("E14" & irow) = TextBox3.Value
.Range("E16" & irow) = TextBox5.Value
.Range("E15" & irow) = TextBox7.Value
.Range("E17" & irow) = TextBox9.Value
.Range("E18" & irow) = TextBox11.Value
.Range("E19" & irow) = TextBox13.Value
.Range("E20" & irow) = TextBox15.Value
.Range("E21" & irow) = TextBox17.Value
.Range("E22" & irow) = TextBox19.Value
.Range("E23" & irow) = TextBox21.Value
End With
TextBox3.Value = ""
TextBox5.Value = ""
TextBox7.Value = ""
TextBox9.Value = ""
TextBox11.Value = ""
TextBox13.Value = ""
TextBox15.Value = ""
TextBox17.Value = ""
TextBox19.Value = ""
TextBox21.Value = ""
End Sub

 

[NdNoviceHlp]

It may have been better to start a new thread. Also, please mark your solution. Dave

irow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1