I am trying to separate multiple dates from one cell into multiple cells containing one date in a transposed area and then paste them back over the original area as separate entries.
An example cell might have dates stored like 10/1110/1110/13 or 10/310/310/410/5.
The second scenario is what is causing the error as there is no leading zero for single digit days like 10/3, for example.
Ideally, the code would separate the dates into separate cells like: 10/11,10/11,10/13 and 10/3,10/4,10/5. When single digits days are present ,however, it comes out completely jumbled up and inaccurate.
Admittedly, I had help from another coworker with this code who is on vacation currently, which is why I am having such trouble understanding this. Is there something I could change to account for single digit days or should I approach this process differently?
'separate column J by "/" and store in transpose area
dim h as variant
dim i as variant
dim j as variant
dim counter as variant
dim stringcheck as variant
dim strInput as variant
dim strCurrent as variant
strInput = Cells(j, 10)
h = 0
For counter = 1 To Len(strInput) - 2
stringcheck = InStr(strInput, "/")
Debug.Print j & stringcheck
If stringcheck <> 0 Then
If Mid(strInput, counter, 1) = "/" Then
Cells(17, i + h) = strCurrent & Mid(strInput, counter, 3)
counter = counter + 2
h = h + 1
strCurrent = vbNullString
Cells(17, i + h) = Cells(j, 10)
strCurrent = strCurrent & Mid(strInput, counter, 1)
'else just paste the value
Cells(17, i) = strInput