如何使用另一列中的值引用来增加一列中的值

i want to add n cells in column C and D, where n is value in column B

我想在C列和D列中添加n个单元格,其中n是B列中的值

My Code is:

我的代码是:

Sub mycode()
Dim lastrow1 As Long
    lastrow1 = Range("A" & Rows.Count).End(xlUp).Row

Dim lastrow2 As Long
    lastrow2 = Range("C" & Rows.Count).End(xlUp).Row

For h = 2 To lastrow1
    For i = 2 To lastrow2
        If Sheet1.Cells(h, 1).Value = Sheet1.Cells(i, 3).Value Then
            P = 10
            t = i + 1
                For j = 1 To Cells(i, 2).Value
                    Sheet1.Cells(t, 3).Insert shift:=xlDown
                    Sheet1.Cells(t, 4).Insert shift:=xlDown
                    Sheet1.Cells(t, 3).Value = Sheet1.Cells(i, 3).Value
                    Sheet1.Cells(t, 4).Value = Sheet1.Cells(i, 4).Value + P
                    P = P + 10
                    t = t + 1
                Next j
        End If
     Next i
Next h
End Sub

Sample Input 1 :

样本输入1:

After running above code, output is

运行上面的代码后,输出是

Problem: Now when i add second entry in row 3 and run code something is going wrong:

问题:现在,当我在第3行添加第二个条目并运行代码时出现问题:

Input:

Wrong output generated by code:

代码生成错误的输出:

Correct output should be:

正确的输出应该是:

I am unable to get where my code is going wrong. Please help.

我无法得到我的代码出错的地方。请帮忙。

EDITED: sample 2: Input:

编辑:样本2:输入:

Output:

1 个解决方案

#1

I edited your code and added comments.

我编辑了你的代码并添加了评论。

Updated code:

Sub mycode()
Application.ScreenUpdating = False 'Optional speedup code
Dim lastrow1 As Long
lastrow1 = Range("B" & Rows.Count).End(xlUp).Row
Dim Inputs() As Variant         'Create an array to hold positions
ReDim Inputs(lastrow1 - 1)      'Make it big enough to hold all positions
For x = 2 To lastrow1           'For each Position
    Inputs(x - 1) = Cells(x, 4) 'Store The Position
Next x
OutRow = 2      'Row to output to
Increment = 10  'Increment
For Each c In Range("B2:B" & lastrow1) 'For each "count"
    For j = 0 To c.Value
        Cells(OutRow, 3).Value = c.Offset(0, -1).Value 'Put the letter in column C
        If j = 0 Then  'If it's the first new letter, start at the position
            Cells(OutRow, 4).Value = Inputs(c.Row - 1)
        Else 'Otherwise, just add our increment to the number above
            Cells(OutRow, 4).Value = Cells(OutRow - 1, 4).Value + Increment
        End If
        OutRow = OutRow + 1
    Next j
Next c
Application.ScreenUpdating = True 'Optional speedup code
End Sub

Final output:

#1