String syntax puzzle

[Bill McKeever]

Why does this code work:

CrewSize = ActiveSheet.Range("CrewCount").Value

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

Whereas this code does not work ??????? :

CrewSize = ActiveSheet.Range("CrewCount").Value
myString = "=$Z$70:$Z$" & Mid(Str(CrewSize), 2)

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

I am perplexed.

Bill McKeever
willymac at alaskalife {dot] [net)

 

[Bill James]

Your code example implies that:

(69 + CrewSize) = Mid(Str(CrewSize), 2)

Which I suspect is wrong. Based on your first code
sample you should try:

myString = "=$Z$70:$Z$" & (69 + CrewSize)
' ...
AlertStyle:=xlValidAlertStop, _
Formula1:=myString ' notice change here

 

[Bill McKeever]

Isn't it funny how, when troubleshooting your own code,
you often find that the problem is just what you
suspected it would be, instead of what it really is?

-----Original Message-----
Your code example implies that:

(69 + CrewSize) = Mid(Str(CrewSize), 2)

Which I suspect is wrong. Based on your first code
sample you should try:

myString = "=$Z$70:$Z$" & (69 + CrewSize)
' ...
AlertStyle:=xlValidAlertStop, _
Formula1:=myString ' notice change here

-----Original Message-----
Why does this code work:

CrewSize = ActiveSheet.Range("CrewCount").Value

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

Whereas this code does not work ??????? :

CrewSize = ActiveSheet.Range("CrewCount").Value
myString = "=$Z$70:$Z$" & Mid(Str(CrewSize), 2)

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

I am perplexed.

Bill McKeever
willymac at alaskalife {dot] [net)

.

.