PDA

View Full Version : Help on displaying vlookup to specific cells in VBA



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!

mdmackillop
12-25-2012, 09:46 AM
Can you post a sample workbook?

Paul_Hossler
12-26-2012, 08:22 PM
I've never seen it this way. Does it work? It looks like worksheet formatting


All!Cells(r, 1) = ItemCategory.Value

I think you want to reference cells in VBA like this:


WorkSheets("All").Cells(r, 1) = ItemCategory.Value

or maybe

All.Cells(r, 1) = ItemCategory.Value


IF 'All' is the code name of the worksheet which might not be the same as the name displayed in worksheet tab that the user sees.

So since you have this


Set Sheet = ActiveWorkbook.Sheets("All")

you would reference the cells like:


Sheet.Cells(r, 1) = ItemCategory.Value

(BTW, I'd change the variable name to AllSheet since 'Sheet' is VBA keyword



WorkSheets("All").Cells(r, 1) = ItemCategory.Value


However -- with the exception of the "!" 's -- the format looks right assuming that the addresses are correct


Application.WorksheetFunction.VLookup(All!Cells(r, 9), Sheet4!("$U$4:$V$7"), 2, False)

BTW #2 - you might want to add error checking just in case the looked up value doesn't exist


There's still a lot that could be done to simplify, and this isn't tested AT ALL, but hopefully it'll get you farther along


Option Explicit
Private Sub PutData()
Dim result1 As String
Dim AllSheet As Worksheet

Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)

Else
MsgBox "Illegal row number"
Exit Sub

End If

Set AllSheet = ActiveWorkbook.Sheets("All")

If r > 1 And r < LastRow Then
AllSheet.Cells(r, 1) = ItemCategory.Value
AllSheet.Cells(r, 2) = TextBoxItem.Text
AllSheet.Cells(r, 3) = TextBoxTask.Text
AllSheet.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

result1 = Application.WorksheetFunction.VLookup(Sheet2.Range(r, 4), Sheet12.Range("$L$15:$T$20"), 9, False)

AllSheet.Cells(r, 5) = result1.Value

AllSheet.Cells(r, 6) = TextBoxHazID.Text
AllSheet.Cells(r, 7) = HazGroup.Value
AllSheet.Cells(r, 9) = Severity.Value

AllSheet.Cells(r, 8) = Application.WorksheetFunction.VLookup(AllSheet.Cells(r, 9), Sheet4!("$U$4:$V$7"), 2, False)
AllSheet.Cells(r, 10) = Application.WorksheetFunction.VLookup(AllSheet.Cells(r, 9), Sheet4!("$U$23:$V$26"), 2, False)
AllSheet.Cells(r, 12) = Probability.Value
AllSheet.Cells(r, 11) = Application.WorksheetFunction.VLookup(AllSheet.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

AllSheet.Cells(r, 14) = AllSheet.Cells(r, 5).Value * AllSheet.Cells(r, 8).Value * AllSheet.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 AllSheet.Cells(r, 14).Value <= 2 Then
AllSheet.Cells(r, 15) = "Negligable"

ElseIf AllSheet.Cells(r, 14).Value <= 4 Then
AllSheet.Cells(r, 15) = "Low"

ElseIf AllSheet.Cells(r, 14).Value <= 10 Then
AllSheet.Cells(r, 15) = "Medium"

ElseIf AllSheet.Cells(r, 14).Value <= 15 Then
AllSheet.Cells(r, 15) = "High"

ElseIf AllSheet.Cells(r, 14).Value <= 20 Then
AllSheet.Cells(r, 15) = "Extremely High"

Else
AllSheet.Cells(r, 15) = ""

End If

AllSheet.Cells(r, 16) = TextBoxControl.Text
AllSheet.Cells(r, 17) = TextBoxMonitoring.Text

DisableSave

Else
MsgBox "Invalid row number"

End If
End Sub


Stop back if you have more questions

Paul