Excel Date Conversion - revisted

Started by stanl, July 29, 2013, 12:13:40 PM

Previous topic - Next topic

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

   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
;UNDEBUGGED
objRange = objXL.Range("A1:A100")
objRange .NumberFormat = "0.00000000"
this= "=" : objRange.Address : "*24"
objRange .Value = oXL.Evaluate(this)


Deana F.
Technical Support
Wilson WindowWare Inc.

td

Guesstimate.

Code (winbatch) Select

s=oXL.Selection
s.NumberFormat="0.00000000"
s.Value = oXL.Evaluate(:"=":s.Address:"*24")
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

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().