String syntax puzzle

B

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)
 
B

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
 
B

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)

.
Click to expand...
.
Click to expand...
 
Top