JeffGrant
Well-known Member
- Joined
- Apr 7, 2021
- Messages
- 516
- Office Version
- 365
- Platform
- 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:
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.
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
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 | |||||
---|---|---|---|---|---|
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