CODEWITHSUNDEEP
excelexcel-formulavba
xnanitox
xnanitox

Reputation: 495

Repeat cell From year To year in new columns - Excel

i have an excel sheet with these 4 columns:

FROM_YEAR|TO_YEAR|MAKE|MODEL

So these four columns are filled, now i want to add three extra columns, it could be to the right of same sheet -see image- or in a new sheet...

enter image description here

Let's say i have:

2000,2003,Honda,Civic

I want to repeat MAKE and MODEL from 2000 to 2003 like this:

2000,Honda,Civic

2001,Honda,Civic

2002,Honda,Civic

2003,Honda,Civic

Into the right or in a new sheet, having as reference the columns from the left... i could do this with PHP and MySQL database but i want to do this in Excel cuz i want to learn if it is possible.

You can see a test sheet i've uploaded in google for reference: https://docs.google.com/spreadsheets/d/1YLY4Dc0NJZ8BW86CTEVPuF_EIOzks591ENU_vEyy5Bw/edit?usp=sharing

Let me know!

Upvotes: 0

Views: 82

Answers (1)

CMArg
CMArg

Reputation: 1567

Something like the following?

Dim lLastRow As Long
Dim i, a, j As Integer

Sub Button1_Click()
lLastRow = Worksheets("Sheet1").Cells(1, 1).End(xlDown).Row
a = 1
For i = 1 To lLastRow
    n = Worksheets("Sheet1").Cells(i, 2).Value - Worksheets("Sheet1").Cells(i, 1).Value + 1
    For j = 1 To n
        Worksheets("Sheet1").Cells(a, 6) = Worksheets("Sheet1").Cells(i, 1).Value + j - 1
        Worksheets("Sheet1").Cells(a, 7) = Worksheets("Sheet1").Cells(i, 3)
        Worksheets("Sheet1").Cells(a, 8) = Worksheets("Sheet1").Cells(i, 4)
        a = a + 1
    Next
Next
End Sub

Upvotes: 2

Related Questions