Validation list to change columns values

So I've got a drop down list in cell B73 That when I change the selection I want it to copy the cells below it (B74:B94) from one of the charts above it. Through the power of Google I found this: http://www.eggheadcafe.com/conversat...eadid=29484871, someone who had the same need as me, and edited it accordingly to my needs. It however, is not working. I started with Case 1-7 and changed them thinking they need to be the same as the list but that hasn't fixed it either. File is attached, and below quote is what the VBA coding currently says.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("B73"), Target) Is Nothing Then
Select Case Target.Value
Case Human: Range("B74:B94").Value = Range("B2:B22").Value
Case Elf: Range("B74:B94").Value = Range("C2:C22").Value
Case Drow: Range("B74:B94").Value = Range("D2:D22").Value
Case Dwarf: Range("B74:B94").Value = Range("E2:E22").Value
Case Orc: Range("B74:B94").Value = Range("F2:F22").Value
Case Centaur: Range("B74:B94").Value = Range("G2:G22").Value
Case Gnome: Range("B74:B94").Value = Range("H2:H22").Value
End Select
End If
End Sub

I see a bunch of undeclared, uninitialized variables that all default to Variants with a value Empty.

Try this:

Please Login or Register  to view this content.

... was complete jargon to me,

That's the nature of programming, I'm afraid.

VBA, like some other languages, does not require you to declare variables -- it will create them on the fly as they're used. The type of variables it creates are all-purpose types called Variants, and their value is Empty until they are assigned to something. That sounds great in principle, but it's error-prone, as you've discovered. Putting Option Explicit at the top of every module requires you to declare variables.

Please Login or Register  to view this content.

I plugged it into your workbook, and it ran fine. Code that responds to worksheet events has to go in the sheet module. See attached.

EDIT: BTW, you certainly don't need code to do this -- you could use lookup functions.

Delete the code, then in B74 and copy down,

=HLOOKUP(B$73, $B$1:$H$22, ROWS(B$73:B74), FALSE)

You’re welcome. Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes