How to avoid type mismatch error when declaring two variable types in VBA-CodePudding

The following macro works fine when I'm using one variable ("i") as integer to create a serial number.

Sub Test1()
 
Dim i As Integer
i = Application.InputBox("How many days did the resource work in this period?")

    For i = 10001 To 10000   i
  
        ActiveCell.Value = i
        ActiveCell.Offset(1, 0).Activate
 
    Next i

End Sub

However, I want the initial value of the counter to correspond to the DATEVALUE of any date I input (instead of 10001 or any static number above) and then increment my output serial number from that initial value up:

Sub Test2()
'
' This is to create part of the unique ID using DATEVALUE
'

Dim StartDate As String
StartDate = Application.InputBox("Enter first date in this period", "[d]dMMMyyyy, e.g. 1Dec2021")

Dim i As Integer
i = Application.InputBox("How many days did the resource work in this period?")


    For i = Int(DateValue("StartDate")) To Int(DateValue("StartDate"))   i
  
        ActiveCell.Value = i
        ActiveCell.Offset(1, 0).Activate
 
    Next i

End Sub

I've been getting the type mismatch run-time error 13 so far even though I've tried to convert the string variable I assign as StarDate into an integer. Any insights would be appreciated!

PS: on a side note, I think once the mismatch is resolved, I'd have to use Long instead of Integer as the MS Excel date serial numbers are large enough to lead to an overflow error.

CodePudding user response：

for me it's very useful the Cint, Cdbl, clng, cstr, cdate statements. This is to convert a variable to another type of variable. Date is a variable that can hold a Long Type variable. A date can't be converted to an integer. So, e.g., I usually do CLng(now()) to convert the today date to a number (long)

Sub Test3()

Dim StartDate As String, inputDays As String, i As Long, xRow As Long

question: 
StartDate = InputBox("Enter first date in this period", "[d]dMMMyyyy, e.g. 1Dec2021")
If StartDate = vbNullString Then Exit Sub 'if the user Cancel or don't type, end the routine
If Not IsDate(StartDate) Then GoTo question 'if not is date, go back to the "question" line code


questionDays:
inputDays = InputBox("How many days did the resource work in this period?")
If inputDays = vbNullString Then Exit Sub 'the same, if is empty, quit
If Not IsNumeric(inputDays) Then GoTo questionDays 'the same, if not is a number, back to the second question

    xRow = 0
    For i = CLng(CDate(StartDate)) To CLng(CDate(StartDate))   inputDays
        With ActiveCell.offset(xRow, 0) 'from the active cell, offset x rows
            .value = Format(CDate(i), "dd-mm-yyyy")
        End With
        xRow = xRow   1 'add 
    Next i

End Sub