Can't use a variable here???

[Bill McKeever]

The following code works, but with 18 cases it's
cumbersome & takes a lot of typing. I wonder if the
expression, Formula1:="=$Z$70:$Z$71", could not be
rewritten with the "=$Z$70:$Z$xx" part of it replaced
with a variable.

Unfortunately, I can't get it to work that way. Can it
be done?

Thanks for your help. -- Bill McKeever
willymac [at] alaskalife {dot} (net)

Select Case CrewSize
Case 1
ActiveSheet.Range("TimeCardEmployee").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$Z$70:$Z$71"
End With
Case 2
ActiveSheet.Range("TimeCardEmployee").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$Z$70:$Z$72"
End With

..
..
..

Case 18
ActiveSheet.Range("TimeCardEmployee").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$Z$70:$Z$87"
End With
End Select

 

[Dick Kusleika]

Bill

Yes, the Formula property accepts a string so you can manipulate that string
like you can any other string. Ex:

With ActiveSheet.Range("TimeCardEmployee").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop _
Formula1:="=$Z$70:$Z$" & (70+CrewSize)
End With