taking a Custom Number field to Split String giving type mismatch-CodePudding

I have a data field that is on the worksheet as a custom number format

geo: [![![data column example][1]][1]

sum:[![![data column example][2]][2]

I am taking that field and comparing it to wo other fields on another worksheet to determine if this one is in between those. So I've got the below code that uses variants for the arrays and splits along spaces. I think the best way is to use the datevalue and timevalue functions with inequalities, both of which take strings. any ideas why I'm getting a type mismatch error at the split?

UPDATE: Based on the #### comment, and the column reference mistake, I autosized the dateTime co and changed my column references. Now my sumfull string gets the text of the column. I am still getting a type match error on the next line. I've updated the code below. The code breaks at sumsplit = Split(sumfull, " ") with a Type mismatch error. The contents of .Cells(i.row, 4).text is "01/23/2022 18:53". This is also the value of sumfill when it breaks.

Option Explicit

Sub O_face()
Dim geo As Workbook
Dim sum As Workbook
Dim geowks As Worksheet
Dim sumwks As Worksheet

Dim i As Variant
Dim j As Variant
Dim lastrow As Long
Dim georng As Range
Dim sumrng As Range
Dim geofull As Date

Dim sumfull As Date
Dim sumfull2 As Date

Set geo = ThisWorkbook
Set sum = Workbooks.Open("MyFile.csv")

Set geowks = geo.Workshets(1)
geowks.Range("B:B").EntireColumn.AutoFit
Set sumwks = sum.Worksheets(1)
sumwks.Range("F:G").EntireColumn.AutoFit

lastrow = geowks.Cells(Rows.Count, "a").End(xlUp).Row
geowks.AutoFilterMode = False
geowks.Range("A1:L" & lastrow).AutoFilter Field:=5, Criteria1:="<>", Operator:=xlFilterValues

Set georng = geowks.Range("E2:E" & lastrow).SpecialCells(xlCellTypeVisible)


lastrow = sumwks.Cells(Rows.Count, "a").End(xlUp).Row
sumwks.AutoFilterMode = False
sumwks.Range("A1:P" & lastrow).AutoFilter Field:=3, Criteria1:="<>", Operator:=xlFilterValues

Set sumrng = sumwks.Range("C2:C" & lastrow).SpecialCells(xlCellTypeVisible)

'have to split the date time cell because it's a custome data type in the worksheet. Then compare the date and time seperately.....
For i = 1 To sumrng.Rows.Count

    sumfull = sumrng.Cells(i, 4)
    sumfull2 = sumrng.Cells(i, 5)

    For j = 1 To georng.Rows.Count
        geofull = georng.Cells(j, -2)
        If sumrng(i, 1) = georng(j, 1) And _
            geofull >= sumfull And geofull >= sumfull2 Then
                sumrng.Cells(i, 15) = "IS THIS WHAT YOU WANT!!!!"
            End If
        End If
    Next j
Next i

End Sub

CodePudding user response：

(a) Split returns an array of strings. You can assign the result to a dynamic String-Array or to a Variant-Variable, see https://stackoverflow.com/a/57113178/7599798 . What you try to do is assign it to a Variant Array - this will fail. You also don't need to set the dimensions of that array, split will take care about that anyhow. So that would be:

Dim sumsplit() As String
sumfull = CStr(sumrng.Cells(i.Row, "f").Text) 
sumsplit = Split(sumfull)

(b) Assuming that your data in Excel are Dates (not Strings that look like a Date), there is neither a reason to convert them to a string nor split that string to get the date and time part. Just use Date variables. In the background, Dates are Floating point Numbers (=Double). The number before the decimal defines the Date-Part (Days since 31.12.1899), the remainder the Time. To get Date and Time of an Excel-Date:

Dim sumfull As Date, fsumdate As Date, fsumtime As Date
sumfull = sumrng.Cells(i.Row, "f").value
fsumdate = int(sumfull)         ' Remove the digits after the decimal
fsumtime = sumFull-int(sumfull) ' The digits after the decimal is the Time.

(c) I don't fully understand the logic of your If-statement, but you can simply compare date variables with < and > - a higher number means a later date/time. I assume that you will not need to compare date and time parts separately. Probably this will do:

Dim geoDate As Date, fsumDate As Date, lSumDate As Date
fsumDate = sumrng.Cells(i.Row, "f").value
lsumDate = sumrng.Cells(i.Row, "g").value
geoDate  = georng.Cells(j.Row, "b").value

If geodate >= fsumdate And geodate <= lsumdate Then

(d) Generally, you should avoid using the Text-property. If for any reason the width of a cell is too small to display the date, Excel will display "######" instead - and you will get exact this into your program.