Finding the colon in a time string

[JeffGrant]

Hi All,

I am really stuck on this one. Hope that someone can point me in the right direction.

When I paste data into column B, sometimes I will get a time value at the bottom of the data, and other times I wont. Unfortunately I can fix the address of the time value because it will vary.

So I run this code over it:

Sub GetDetails()

    Sheets("Sheet1").Range("B32").Select
    Selection.End(xlUp).Select

' Length Test
    If Len(ActiveCell) = 17 And Len(ActiveCell.Offset(-1, 0)) = 17 Then
        MsgBox "Check Data!!! Race Name = 17 characters as does the length of the Time Value"

        ElseIf Len(ActiveCell) = 17 Then ' 17 is the exact length of the time value in decimal format.
                                 ' the problem will be if the Race Name is also 17 characters
        Range("D25").Value = ActiveCell.Offset(-1, 0).Value

            Else
                Range("D25").Value = ActiveCell.Value
                Exit Sub
    End If
End Sub

To switch between those times when I do have a time value and those times when I don't. The idea is to always have the string text in D25, be like what you can see now.

Test.xlsm
	B	C	D
4
5	Flemington
6
7	Flemington
8	Good (4)
9
10	01-01-00 0:00
11
12	02-01-00 0:00
13
14	03-01-00 0:00
15
16	04-01-00 0:00
17
18	05-01-00 0:00
19
20	06-01-00 0:00
21
22	07-01-00 0:00
23
24	08-01-00 0:00		Helper Cell Offset Equation
25			1400m R5 Vrc Member T Gunnersen Am Hcp
26	09-01-00 0:00
27	1400m R5 Vrc Member T Gunnersen Am Hcp
28	14:30
29
30
31
32
Sheet1

At the moment, my decider is based upon the length of the string after the Selection.End(xlUp).Select statement. If the length is 17 then I am assuming there is a time value present and set the value in D25 to be that of the offset cell. If the length is not 17, then I am assuming there is not a time value present and set the value in D25 to be the active cell.

This works fine until the day comes when the length of the string in the offset = 17.

As you can see I am handling this via a message box, however this is not ideal.

I have tried several options to find the ":" in the time value, but was not successful because excel is only looking for the 17 digit decimal time.

I am open to suggestions on how I can better handle this when the string text length = 17.

Looking forward yo some great suggestions.

Jeff

 

[Fluff]

How about

Sub JeffGrant()
   With Range("B" & Rows.Count).End(xlUp)
      If IsNumeric(.Value) Then
         Range("D25").Value = .Offset(-1).Value
      Else
         Range("D25").Value = .Value
      End If
   End With
End Sub

 

[JeffGrant]

Hi Fluff,

that's perfect and a very simple solution.

Thanks

 

[Fluff]

You're welcome & thanks for the feedback.