Time issues in code

[Auric__]

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

 

[Claus Busch]

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.

 

[Claus Busch]

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.

 

[Auric__]

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


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.

 

[Claus Busch]

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.

 

[Claus Busch]

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.

 

[Auric__]

Hi Auric,

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


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