Split the numbers into time block format in vba excel

Question

I have the the problem, like this

0    500   100   1.8
500  800   50    1.6
800  2300  125   3.2

*I want this format (Split the numbers into time block format) in given below:*

00:00  05:00  -50  1800
00:00  05:00  -50  1800
05:00  08:00  -50  1600

08:00  23:00  -50  3200
08:00  23:00  -50  3200
08:00  23:00  -25  3200

and the code is given below:

Option Explicit

Sub main()
    Dim data As Variant
    Dim iData As Long, datum As Long, iRow As Long
    On Error Resume Next
    With Range("A2", Cells(Rows.Count, 1).End(xlUp))
        data = .Resize(, 4).Value
        iData = LBound(data)
        Do
            datum = data(iData, UBound(data, 2) - 1)
            Do While datum > 0
                iRow = iRow + 1
                .Cells(iRow).Resize(, 4) = Application.Index(data, iData, 0)
                .Cells(iRow).Offset(, 3).Value = .Cells(iRow).Offset(, 3).Value * 1000
                .Cells(iRow, UBound(data, 2) - 1).Value = -WorksheetFunction.Min(50, datum)
                datum = datum - 50
            Loop
            iData = iData + 1
        Loop While iData <= UBound(data)
        .Resize(1, 4).Copy
        .Resize(iRow, 4).PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
    End With
End Sub

Any help would be appreciated.

Please put your code in `code` tags and start them on a new line when needed. This is very hard to read. Plus, I don't really see the relation between your input and output, `500` becomes `05:00` and `100` becomes `-50`? And do you use vb.net (as a vsto add-in) or excel vba? — DrDonut, Feb 22 '17 at 07:55
sir, i am using excel vba and this is the requirement where input is 500 and output should be 05:00 — Satyendra Babu, Feb 22 '17 at 08:03
What problems do you get with your current code? What errors? What is the output now? — DrDonut, Feb 22 '17 at 08:08
I am not able to find the logic for this time format pls help.. — Satyendra Babu, Feb 22 '17 at 09:29
If you convert the time you want to write to a DateTime object, then you can use this approach: http://stackoverflow.com/questions/5457069/excel-macro-how-can-i-get-the-timestamp-in-yyyy-mm-dd-hhmmss-format — DrDonut, Feb 22 '17 at 09:36
sir provide me the separate method for this problem so that i can embed this method in my code — Satyendra Babu, Feb 22 '17 at 10:09
No, I will not write your code for you. I can help you find the right way to do it, but you have to write it yourself, that is the only way to get better. — DrDonut, Feb 22 '17 at 10:15

score 0 · Answer 1 · answered Feb 22 '17 at 10:42

Sorry, I didn't see that excel does not provide it's own method to convert a string to a dateTime, but you can do this:

To write the first to columns

private Sub getColStr(in as String) as string
    return in(0)+in(1)+":"+in(2)+in(3)
End Sub

The last two columns should be easy.

Split the numbers into time block format in vba excel

1 Answers1