Strange formatting error with variables when looking for match

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
243
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have a bit of code that looks in a cell for a date and time which looks like this.

VBA Code:
Dim sdt As Date
sdt = ThisWorksheet.range("K1")

Which works fine.

sdt now shows as ": sdt : #24/12/2021 06:30:00# : Date" in the immediate window. So far so good.

sdt is then placed in A4 where it displays as "24/12/2021 06:30:00"

I have some later code that looks for a match for sdt in A:A (I know this sounds weird but its just for the example) which throws an error because it cannot find a match.

Why?

Well, if you look closely... closlier... that's right. For some reason A4, despite being the value of sdt, has an extra space between the date and the time when it appears in the cell but not when its a variable.

Anyone know why the heck it is doing this?

To be fair, the source K1 also has an extra space too (can't be helped, the source is drawn from a report generated elsewhere) - but why does the extra space disappear and then reappear like this?

I suppose a bit of code would be possible to remove the extra space from the source.

But I'm mostly just puzzled about why the space only reappears on the page and not in the variable in my code

Thanks!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
It is highly unlikely that your match throwing an error is due to the extra space you are referring to.
In my case the immediate window is showing using the default excel date format (1 soace)
In Excel the cell itself using the default date format has only 1 space in it. The edit box however has the 2 spaces you are referring to.
As long as all occurences are not text and recognised as date, then match and Find (formula option) should find it.
They are more likely to not find it due to a mismatch in the internal rounding used by excel for time.

You would need to give us an example of the data you are trying to match and that is erroring out.
 
Upvote 0
This is the actual code I'm using

VBA Code:
Sub Time()

    Dim ws1, ws2 As Worksheet
'sdt short for start date time
    Dim sdt As Date
'edt short for end date time
    Dim edt As Date
    Dim rng, rng2, edtrng As Range
    Dim rowno As Integer
    
    Set ws1 = Worksheets(7)
    Set ws2 = Worksheets(1)
    Set rng = ws2.Range("A4")
    Set rng2 = ws2.Range("A4:A148")
    
'formatting - can be ignored
    rng2.EntireRow.Hidden = False
    
'K1 is "24/12/2021  06:30:00"
    sdt = ws1.Range("K1").Value
  
'finds the largest value in L which found manually is "25/12/2021  06:45:00 "
    edt = Application.WorksheetFunction.Max(ws1.Columns("L"))
'just for me to check the value easily
        Debug.Print edt
 'this section just adds 15 minutes for each row starting at A4 e.g A4 =24/12/2021  06:30, A5 =24/12/2021  06:45, A6 = 24/12/2021 07:00...
    For rowno = 4 To 148
        rng.Value = sdt
        sdt = sdt + (15 / 60 / 24)
        Set rng = rng.Offset(1, 0)
    Next

'errors out because there's no match
   rng2.Find(What:=edt).Select

 'formats the way the time is shown on the sheet
    'Range("A4:A148").NumberFormat = "h:mm"
    

End Sub

I've added comments to explain what various parts are doing.

As stored using the Date data type and shown in the locals window, there is only one space between the date and the time. If then set a cell value using either variable, the extra space appears. Trying to match the variable to the cell value appears to not work because of the extra space - if you manually set a cell value with only a single space/manually set the variable to a double space, it does find the match.
 
Upvote 0
Can you provide an XL2BB of ws1 = Worksheets(7) that includes K1 and column L.
And how do you "manually" add a space to the cell and or variable ?
 
Upvote 0
There's too much data in K & L to provide the entire thing so here's a sample

06:30:0006:45:00
06:30:0006:45:00
06:30:0006:45:00
06:30:0006:45:00
06:30:0006:45:00
06:30:0008:15:00
06:30:0008:15:00
06:45:0008:45:00
06:45:0008:30:00
06:45:0008:30:00
06:45:0008:30:00
06:45:0008:30:00
07:30:0007:45:00
07:30:0007:45:00
07:45:0008:15:00
07:45:0008:00:00
07:45:0008:30:00
08:00:0009:00:00
08:00:0008:15:00
08:00:0010:15:00
08:15:0008:45:00
08:15:0010:30:00
08:15:0008:30:00
08:15:0008:45:00
08:30:0008:45:00
08:30:0009:30:00
08:30:0008:45:00
08:30:0008:45:00
08:30:0008:45:00
08:30:0011:30:00


and by manually I mean physically type - either directly into the cell (so without the extra space and not using the variable to set the cell) or, in the code, instead of looking for data and setting sdt/edt with ranges, just directly type the value
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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
Back
Top