Excel Date Conversion - revisted

[stanl]

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

Code (WINBATCH) Select Expand


   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

[Deana]

Usually the OBJECT referenced after the WITH should be used with each of the elements in the WITH...END WITH.

For example:

Code (winbatch) Select Expand

;UNDEBUGGED
objRange = objXL.Range("A1:A100")
objRange .NumberFormat = "0.00000000"
this= "=" : objRange.Address : "*24"
objRange .Value = oXL.Evaluate(this)

[td]

Guesstimate.

Code (winbatch) Select Expand


s=oXL.Selection
s.NumberFormat="0.00000000"
s.Value = oXL.Evaluate(:"=":s.Address:"*24")


[stanl]

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

[stanl]

as a P.S. - I knew I had come across Evaluate before; it was 2004 and the code involved Excel's SumProduct().