Creating dataset with Excel Macro

[hornofgondor]

Hello,

I am trying to develop a small dataset using excel macro. The logic is fairly simple. However, it has to be repeated for about 4000 different records (each record is a row in excel) and thus I am trying to use a macro to build this. I am totally new to macros and am having difficulty figuring it out. I am providing the details as to what I want below.


The first sheet with the name SHORT_TIME_ROUTES has 5 columns. First two columns are the record names (each record corresponds to a route). Third column shows the time to travel that route and the fourth column shows the total length of the route.

What I want to do is, create multiple records for the same route with length intervals. For example, if a particular route has a total length of 150 m, then I would like to have 30 new records for this route with an interval of 5 m. I have shown the output I need in Sheet1. As can be seen, i calculated the total number of intervals by dividing the length with the interval length (5m). For every record, the starting point will be 0 m. Then it keeps on incrementing at 5 m unless the length of the route terminates before the next 5 m interval.

I have recorded a macro to do this. But, having no experience with this, it is not yielding me the results i want. Can anyone help me with this. The code is pasted below and the excel sheet is attached.

Sub SHORT()
'
' SHORT Macro
'

'
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("F2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "1"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-3]=0,0,IF((RC[-2]=1)*AND(RC[-2]<RC[-3]),5,IF((RC[-2]>1)*AND(RC[-2]<RC[-3]),5+RC[-1],RC[-4]-(RC[-3]-1)*5)))"
    Range("A2:D2").Select
    Selection.Copy
    Range("A2:E2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A3").Select
    ActiveSheet.Paste
    Range("F3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C[1]"
    Range("H2").Select
    Selection.Copy
    Range("H3").Select
    ActiveSheet.Paste
    Range("A3:E3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A4").Select
    ActiveSheet.Paste
    Range("F3:H3").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("F3:H4"), Type:=xlFillDefault
    Range("F3:H4").Select
    Range("A3:A4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFill Destination:=Range("A3:H12"), Type:=xlFillDefault
    Range("A3:H12").Select
    Selection.AutoFill Destination:=Range("A3:H446"), Type:=xlFillDefault
    Range("A3:H446").Select
End Sub

[Tinbendr]

Have you found a solution?

I got this working, but there are too many rows for single sheet.

How can it be split?

You could put a combobox on the route sheet, and only select the route you need to work on.