Case Senerio and Offset That Creates A Data Validation Box

[Red Salerno]

Help on this would be greatly appreciated. See the attached. I'm trying to use CASE in VBA Code and offset that looks to a matrix and creates a data validation box.

In Case 1 I'm trying to use an offset.

In Case 2 and 3 I'm referencing a range which works, but I'm looking for the flexibility provided by Case 1 (BUT IT DOES NOT WORK)

Thanks

Red

[D_Marcel]

Red,

I just removed the quotation marks of the cell B4 and it worked for me.

11425


Is this that you're trying to do?

Sorry if I'm wrong.

Douglas

[Red Salerno]

Yes that worked.....I actually did that first BUT it did not work. I spent a long time looking at this so I'm not sure what happened.

[p45cal]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$2" Then SetList2
End Sub

Sub SetList2()
Dim Sectortype As Long
Sectortype = Range("I2").Value
With Range("G7")
.Value = Sheets("Test").Range("A5").Offset(, Sectortype).Value
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & Sheets("Test").Range("B2").Offset(2, Sectortype - 1).Value
End With
End Sub

[D_Marcel]

Don't worry Salerno, it happens.

Glad to know that it's done.