Finding the colon in a time string

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
210
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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
BCD
4
5Flemington
6
7Flemington
8Good (4)
9
1001-01-00 0:00
11
1202-01-00 0:00
13
1403-01-00 0:00
15
1604-01-00 0:00
17
1805-01-00 0:00
19
2006-01-00 0:00
21
2207-01-00 0:00
23
2408-01-00 0:00Helper Cell Offset Equation
251400m R5 Vrc Member T Gunnersen Am Hcp
2609-01-00 0:00
271400m R5 Vrc Member T Gunnersen Am Hcp
2814: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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,283
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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
 
Solution

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
210
Office Version
  1. 365
Platform
  1. Windows
Hi Fluff,

that's perfect and a very simple solution.

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,283
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,488
Messages
5,770,380
Members
425,613
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top