rch6753
12-18-2012, 11:47 AM
Good Morning Fellas,
I am very new here and I have been self teaching myself through the use of this forum and though you guys might be able to help. This is my first forray into VBA so what I have is probaby not the most efficient way to go about things but here goes.
Basically I made a multipage userform to input data into a spreadsheet. I want to use a vlookup to put a value into a cell based on a selection made in the userform. I need to do this for 4 different selections made in my userform. I am not sure how to implement the vlookup in VBA. I was successful when I did it as a function in excel, but I cannot seem to make it work right here. I have searched extensively for help with this but I couldnt quite manage to find something applicable.
Here is the code:
Private Sub PutData()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
MsgBox "Illegal row number"
Exit Sub
End If
If r > 1 And r < LastRow Then
All!Cells(r, 1) = ItemCategory.Value
All!Cells(r, 2) = TextBoxItem.Text
All!Cells(r, 3) = TextBoxTask.Text
All!Cells(r, 4) = TaskFreq.Value
'Next cell r,5 need to use the vlookup using the value in r,4. This continues for r,8; r,10; r,11
Dim result1 As String
Dim Sheet As Worksheet
Set Sheet = ActiveWorkbook.Sheets("All")
result1 = Application.WorksheetFunction.VLookup(Sheet2.Range(r, 4), Sheet12.Range("$L$15:$T$20"), 9, False)
All!Cells(r, 5) = result1.Value
All!Cells(r, 6) = TextBoxHazID.Text
All!Cells(r, 7) = HazGroup.Value
All!Cells(r, 9) = Severity.Value
All!Cells(r, 8) = Application.WorksheetFunction.VLookup(All!Cells(r, 9), Sheet4!("$U$4:$V$7"), 2, False)
All!Cells(r, 10) = Application.WorksheetFunction.VLookup(All!Cells(r, 9), Sheet4!("$U$23:$V$26"), 2, False)
All!Cells(r, 12) = Probability.Value
All!Cells(r, 11) = Application.WorksheetFunction.VLookup(All!Cells(r, 12), Sheet4!("$U$10:$V$14"), 2, False)
'Next the cell r,14 needs to be the product of the values of cells r,5;r,8; and r,11
All!Cells(r, 14) = All!Cells(r, 5).Value * All!Cells(r, 8).Value * All!Cells(r, 11).Value
'Next the cell r,15 needs to say: negligible, low, medium, high or extremely high based on the value of cell r,14
If All!Cells(r, 14).Value <= 2 Then _
All!Cells(r, 15) = "Negligable"
ElseIf All!Cells(r, 14).Value <= 4 Then _
All!Cells(r, 15) = "Low"
ElseIf All!Cells(r, 14).Value <= 10 Then _
All!Cells(r, 15) = "Medium"
ElseIf All!Cells(r, 14).Value <= 15 Then _
All!Cells(r, 15) = "High"
ElseIf All!Cells(r, 14).Value <= 20 Then _
All!Cells(r, 15) = "Extremely High"
Else
All!Cells(r, 15) = ""
End If
All!Cells(r, 16) = TextBoxControl.Text
All!Cells(r, 17) = TextBoxMonitoring.Text
DisableSave
Else
MsgBox "Invalid row number"
End If
End Sub
Basically what I need some guidance on is how do I incorporate that VLOOKUP and then apply it to cells (r,5) (r,8) (r,10) and (r,11). Thanks again for any help!
I am very new here and I have been self teaching myself through the use of this forum and though you guys might be able to help. This is my first forray into VBA so what I have is probaby not the most efficient way to go about things but here goes.
Basically I made a multipage userform to input data into a spreadsheet. I want to use a vlookup to put a value into a cell based on a selection made in the userform. I need to do this for 4 different selections made in my userform. I am not sure how to implement the vlookup in VBA. I was successful when I did it as a function in excel, but I cannot seem to make it work right here. I have searched extensively for help with this but I couldnt quite manage to find something applicable.
Here is the code:
Private Sub PutData()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
MsgBox "Illegal row number"
Exit Sub
End If
If r > 1 And r < LastRow Then
All!Cells(r, 1) = ItemCategory.Value
All!Cells(r, 2) = TextBoxItem.Text
All!Cells(r, 3) = TextBoxTask.Text
All!Cells(r, 4) = TaskFreq.Value
'Next cell r,5 need to use the vlookup using the value in r,4. This continues for r,8; r,10; r,11
Dim result1 As String
Dim Sheet As Worksheet
Set Sheet = ActiveWorkbook.Sheets("All")
result1 = Application.WorksheetFunction.VLookup(Sheet2.Range(r, 4), Sheet12.Range("$L$15:$T$20"), 9, False)
All!Cells(r, 5) = result1.Value
All!Cells(r, 6) = TextBoxHazID.Text
All!Cells(r, 7) = HazGroup.Value
All!Cells(r, 9) = Severity.Value
All!Cells(r, 8) = Application.WorksheetFunction.VLookup(All!Cells(r, 9), Sheet4!("$U$4:$V$7"), 2, False)
All!Cells(r, 10) = Application.WorksheetFunction.VLookup(All!Cells(r, 9), Sheet4!("$U$23:$V$26"), 2, False)
All!Cells(r, 12) = Probability.Value
All!Cells(r, 11) = Application.WorksheetFunction.VLookup(All!Cells(r, 12), Sheet4!("$U$10:$V$14"), 2, False)
'Next the cell r,14 needs to be the product of the values of cells r,5;r,8; and r,11
All!Cells(r, 14) = All!Cells(r, 5).Value * All!Cells(r, 8).Value * All!Cells(r, 11).Value
'Next the cell r,15 needs to say: negligible, low, medium, high or extremely high based on the value of cell r,14
If All!Cells(r, 14).Value <= 2 Then _
All!Cells(r, 15) = "Negligable"
ElseIf All!Cells(r, 14).Value <= 4 Then _
All!Cells(r, 15) = "Low"
ElseIf All!Cells(r, 14).Value <= 10 Then _
All!Cells(r, 15) = "Medium"
ElseIf All!Cells(r, 14).Value <= 15 Then _
All!Cells(r, 15) = "High"
ElseIf All!Cells(r, 14).Value <= 20 Then _
All!Cells(r, 15) = "Extremely High"
Else
All!Cells(r, 15) = ""
End If
All!Cells(r, 16) = TextBoxControl.Text
All!Cells(r, 17) = TextBoxMonitoring.Text
DisableSave
Else
MsgBox "Invalid row number"
End If
End Sub
Basically what I need some guidance on is how do I incorporate that VLOOKUP and then apply it to cells (r,5) (r,8) (r,10) and (r,11). Thanks again for any help!