viewpoint-particle

Author Topic: Excel Date Conversion - revisted  (Read 3312 times)

stanl

  • Pundit
  • *****
  • Posts: 936
Excel Date Conversion - revisted
« on: July 29, 2013, 12:13:40 pm »
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
 
   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

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: Excel Date Conversion - revisted
« Reply #1 on: July 29, 2013, 01:23:52 pm »
Usually the OBJECT referenced after the WITH should be used with each of the elements in the WITH...END WITH.

For example:

Code: Winbatch
;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

  • Tech Support
  • *****
  • Posts: 3017
    • WinBatch
Re: Excel Date Conversion - revisted
« Reply #2 on: July 29, 2013, 02:01:27 pm »
Guesstimate.

Code: Winbatch
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

  • Pundit
  • *****
  • Posts: 936
Re: Excel Date Conversion - revisted
« Reply #3 on: July 29, 2013, 02:24:56 pm »
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

  • Pundit
  • *****
  • Posts: 936
Re: Excel Date Conversion - revisted
« Reply #4 on: July 30, 2013, 02:05:58 am »
as a P.S. - I knew I had come across Evaluate before; it was 2004 and the code involved Excel's SumProduct().