VBA to pull data based on a cell value

[Alphaboss7]

Hi Guys,

I know this may be simple, but for the life of me I cannot get this to work. I'm trying to just simply use VBA to lookup values based on a specific cell value.

This specific cell has a drop down list, so depending on the value, the data being pulled will come from different sheets that have identical layouts. I just wanted to use the following code as a pattern to see if I could get it to work based on one value from the dropdown list.

Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Range("Team_DrpDn") = "SysAdmin" Then
            For i = 27 To 30
            Range("G" & i).Value = Application.WorksheetFunction.VLookup(Range("E" & i).Value, Sheets("SysAd Historical").Range("E10:G13").Select, 3, False)
            Next i
        End If


End Sub

So I would just replicate the code and use "Network" instead of "SysAdmin" when the specific cell range "Team_DrpDn" changes to that value.

Let me know if that's not enough clarification.

Thanks,
AB7

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Sht As String
    Dim i As Long
    
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address = Range("Team_DrpDn").Address Then
        Select Case Target.Value
            Case "SysAdmin"
                Sht = "SysAd Historical"
            Case "Network"
                Sht = "Network Sheet"
        End Select
        
        For i = 27 To 30
            Range("G" & i).Value = Application.VLookup(Range("E" & i).Value, Sheets(Sht).Range("E10:G13"), 3, False)
        Next i
    End If
End Sub

 

[Alphaboss7]

Hi Fluff, that worked great! Thank you so much for your help!

- AB7

 

[Fluff]

You're welcome & thanks for the feedback