I put together some code which automated and sped up the process of converting a 'general' format AM/PM date text to a fraction of an hour
ad = oWS.UsedRange.Address()
ad = ItemExtract(2,ad,":")
oWS.Range("$F$1:%ad%").Select
s=oXL.Selection
s.NumberFormat="0.00000000"
ForEach c in s
c.Value = c.Value * 24
Next
A user on a VBA site suggested I could eliminate the loop (which does take a bit on 10,000 or more rows with the code below. Have worked with VBA's evaluate, but not sure how to address a WITH block or how to handle .Address in the function.
With Range("A1:A100")
.NumberFormat = "0.00000000"
.Value = Evaluate("=" & .Address & "*24")
End With
Usually the OBJECT referenced after the WITH should be used with each of the elements in the WITH...END WITH.
For example:
;UNDEBUGGED
objRange = objXL.Range("A1:A100")
objRange .NumberFormat = "0.00000000"
this= "=" : objRange.Address : "*24"
objRange .Value = oXL.Evaluate(this)
Guesstimate.
s=oXL.Selection
s.NumberFormat="0.00000000"
s.Value = oXL.Evaluate(:"=":s.Address:"*24")
Deana, Tony...
At first I tried both examples and got invalid results, but after inserting the initial : in the Evaluate in Tony's guestimate... worked
Thanks
as a P.S. - I knew I had come across Evaluate before; it was 2004 and the code involved Excel's SumProduct().