重复单元格从一年到一年的新列 - Excel [英] Repeat cell From year To year in new columns - Excel

问题描述

我有一个excel表与这4列：

i have an excel sheet with these 4 columns:

FROM_YEAR | TO_YEAR | MAKE | MODEL

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...

让我们说：

2000 ，2003，Honda，Civic

2000,2003,Honda,Civic

我想从2000年到2003年重复MAKE和MODEL，如下所示：

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

2000，Honda，Civic

2000,Honda,Civic

2001，Honda，Civic

2001,Honda,Civic

2002，本田，公民

2002,Honda,Civic

2003，本田，公民

2003,Honda,Civic

在右侧或新表格中，以左侧的列为参考，我可以用PHP和MySQL数据库这样做，但是我想在Excel中做这件事我想知道是否可行。

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.

你可以看到一个测试表我' ve uplo请参阅谷歌作为参考：
https：// docs。 google.com/spreadsheets/d/1YLY4Dc0NJZ8BW86CTEVPuF_EIOzks591ENU_vEyy5Bw/edit?usp=sharing

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

让我知道！

推荐答案

如下所示？

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

这篇关于重复单元格从一年到一年的新列 - Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！