Insert formula into first and second blank columns, then autofill down

[plfas]

I import a set of data with dynamic rows and columns. The rows could go up to say 100,000 and the columns, no more than 50. There are headers in row 1.

There's a timestamp in Column that is a a string, so I need to get the DATEVALUE and TIMEVALUE from it and insert those values into the first two "available" columns. I then format them as day and time respectively.

My code is failing when i try to define endTim (the last cell in the TIMEVALUE dynamic column). It just fails. I'm new to VBA so I'm not great at debugging, but stDat, endDat, stTim all get correct values and so does timeCol so I'm not really sure why this won't work. Any help is much appreciated.

Sub DateVal()


Dim endRow As Long
Dim dateCol, timeCol As Integer
Dim stDat, endDat, stTim, endTim As Range


'finds last used row and new columns
endRow = Range("A" & Rows.Count).End(xlUp).Row
dateCol = Cells(1, 100).End(xlToLeft).Column + 1
timeCol = Cells(1, 100).End(xlToLeft).Column + 2


'define formula ranges
stDat = Cells(2, dateCol).Address
endDat = Cells(endRow, dateCol).Address
stTim = Cells(2, dateCol + 1).Address
endTim = Cells(endRow, timeCol).Address


    With Cells(1, dateCol)
        .Value = "Date"
        .Font.Bold = True
        .Font.Name = "Arial"
              End With
'inserts datevalue into date column
    Range(stDat & ":" & endDat).FormulaR1C1 = "=DATEVALUE(RC1)"
    
    With Cells(1, dateCol + 1)
        .Value = "Time"
        .Font.Bold = True
        .Font.Name = "Arial"
        End With
    'timevalue into time column
    Range(stTim & ":" & endTim).FormulaR1C1 = "=TIMEVALUE(RC1)"
    
'formatting
    Columns(dateCol).NumberFormat = "dd/mm/yyy"
    Columns(timeCol).NumberFormat = "HH:MM"
    
    Sheets(1).Calculate
        
End Sub

 

[ParamRay]

.
.

There's an issue with the way you declared some of your variables. With your current set-up, you have declared the following:

endRow As Long;
dateCol As Variant;
timeCol As Integer;
stDat As Variant;
endDat As Variant;
stTim As Variant; and
endTim As Range;


I would suggest changing the declarations to the following and, hopefully, it will make a difference:

Dim endRow As Long
Dim dateCol As Integer, timeCol As Integer
Dim stDat As String, endDat As String
Dim stTim As String, endTim As String