Time issues in code
 

I have this sub:

Sub fixTimes(what As Range)
Dim cell As Range
For Each cell In what
If IsNumeric(cell.Formula) Then
Select Case cell.Formula
Case 0 To 2359
cell.Value = (CLng(cell.Formula) \ 100) & ":" & _
(CLng(cell.Formula) Mod 100)
End Select
End If
Next
End Sub

....which is called from Workbook_SheetChange in the ThisWorkbook object:

Dim cell As Range
For Each cell In Target
Select Case cell.Column
Case 2 To 8
fixTimes cell
[etc.]

(It's important to note that columns B:H have their formatting set
automagically by Excel after the first few rows.)

The sub lets me enter times as a flat number, e.g. 600 for 6:00 am. My
problem is, if I enter data that is already properly formatted as a time
(which can happen via copy/paste or delete/undo), the sub runs against the
existing time using the existing formula (which is 0.25 for 6:00 am). This
changes the entered time to something very close to midnight... not what I
want.

I tried checking the .Text value for ":", but since this is already set
(after those first few rows) before my sub runs (e.g. entering "600" sets
the cell to "8/22/1901 12:00:00 AM"), it always returns True, and my sub
never gets to make the appropriate changes.

So, anyone have any ideas how to make this DWIM? (WIM = if I enter a number,
make it a time; if I enter a time, do nothing.)

--
Waiting is good. It means you're not going to die.
The person you need to feel sorry for is the one
who gets rushed into the ER and treated first.

Time issues in code
 

Hi Auric,

Am Sat, 10 Aug 2013 21:11:53 +0000 (UTC) schrieb Auric__:

The sub lets me enter times as a flat number, e.g. 600 for 6:00 am. My
problem is, if I enter data that is already properly formatted as a time
(which can happen via copy/paste or delete/undo), the sub runs against the
existing time using the existing formula (which is 0.25 for 6:00 am). This
changes the entered time to something very close to midnight... not what I
want.

if the cell already has a time the cell value is smaller 1
try:
If IsNumeric(cell.Formula) And cell.Value 1 Then


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Time issues in code
 

Hi again,

Am Sun, 11 Aug 2013 10:27:42 +0200 schrieb Claus Busch:

If IsNumeric(cell.Formula) And cell.Value 1 Then

the ":" is in cell.text not in cell.value. So you can try:
If IsNumeric(cell.Formula) And InStr(cell.Text, ":") = 0 Then


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Time issues in code
 

Claus Busch wrote:

Am Sun, 11 Aug 2013 10:27:42 +0200 schrieb Claus Busch:

If IsNumeric(cell.Formula) And cell.Value 1 Then

the ":" is in cell.text not in cell.value. So you can try:
If IsNumeric(cell.Formula) And InStr(cell.Text, ":") = 0 Then

As I mentioned, I already tried that. Due to Excel's automagic formatting,
which I'm reluctant to disable (if it even can be) there is *always* a ":"
after the first few rows.

--
You gotta do the good you can, when you can,
else you end up doing no good at all.

Time issues in code
 

Hi Auric,

Am Sun, 11 Aug 2013 17:27:50 +0000 (UTC) schrieb Auric__:

As I mentioned, I already tried that. Due to Excel's automagic formatting,
which I'm reluctant to disable (if it even can be) there is *always* a ":"
after the first few rows.

try:
If IsNumeric(cell.Formula) And Not InStr(cell.Value, ",") 0 Then


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Time issues in code
 

Hi Auric,

Am Sun, 11 Aug 2013 19:44:01 +0200 schrieb Claus Busch:

If IsNumeric(cell.Formula) And Not InStr(cell.Value, ",") 0 Then

sorry, typo. The line above is for german system
Try:
If IsNumeric(cell.Formula) And Not InStr(cell.Value, ".") 0 Then

or
If IsNumeric(cell.Formula) And InStr(cell.Value, ".") = 0 Then


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Time issues in code
 

Claus Busch wrote:

Hi Auric,

Am Sun, 11 Aug 2013 19:44:01 +0200 schrieb Claus Busch:

If IsNumeric(cell.Formula) And Not InStr(cell.Value, ",") 0 Then

sorry, typo. The line above is for german system
Try:
If IsNumeric(cell.Formula) And Not InStr(cell.Value, ".") 0 Then

or
If IsNumeric(cell.Formula) And InStr(cell.Value, ".") = 0 Then

Aha! That works! Thank you!

(I don't know why it never occured to me to check for the decimal...)

--
You need only two tools: WD-40 and duct tape.
If it doesn't move and it should, use WD-40.
If it moves and shouldn't, use the tape.