使用VBA在excel表中每15行增加一次 [英] Increment every 15 rows in excel sheet using VBA

问题描述

我希望将每15行E列增加1.

例如。如果E15为1，则E30应为2，E45应为3，E60应为4等，最高为E200。

代码中可以给出什么？我尝试了很多代码，但似乎没有用。

你能帮我吗？

提前谢谢。

I want to increment every 15 rows of E column by 1.
For eg. if E15 is 1, then E30 should be 2, E45 should be 3, E60 should be 4 etc., upto E200.
What can be given in the code? I tried many codes but it doesn't seem to work.
Can you please help me ?
Thank you in advance.

推荐答案

ROW（）将为您提供当前行，您可以将其除以15，然后使用ROUNDUP（）将剩余数字四舍五入到最接近的整数。 Roundup的第二个参数是小数位数。



所以：

ROW() will give you the current row, and you can divide that by 15, then round the remaining number up to the nearest whole integer with ROUNDUP(). The second parameter of Roundup is the number of decimal places.

So:

=ROUNDUP(ROW()/15,0)

我知道已经过去两周了你发布了，但是fwiw，我认为这可能会有所帮助。



在for循环中使用步长值15并在每次迭代中递增起始值应该轻松照顾你需要的东西。第一个代码使用您提供的值进行硬编码，应该最容易遵循。

I know it has been two weeks since you posted, but fwiw, I think this could help.

Using a step value of 15 in a for loop and incrementing the starting value in each iteration should take care of what you need easily. This first code is hard coded with the values you provided and should be easiest to follow.

Sub IncrementEveryFifteenRows()

'Declare startValue to capture the value of E15
Dim startValue
startValue = Cells(15, 5).Value

'Loop to jump to every fifteenth row, incrementing startValue each iteration
For i = 15 To 200 Step 15
    Cells(i, 5).Value = startValue
    startValue = startValue + 1
Next i

End Sub

下一个代码通过递增所选的每第15行来增加抽象级别范围。这样，你可以开始和结束你喜欢的任何一行。例如，如果选择B3：B100且B3的值为5，则将B18设置为6，B33设置为7，B48设置为8，B63设置为9，B78设置为10，B93设置为11。

This next code adds a level of abstraction by incrementing every fifteenth row of the selected range. This way, you could begin and end at whichever row you like. For instance, if you select B3:B100 and the value of B3 is 5, then you will set B18 as 6, B33 as 7, B48 as 8, B63 as 9, B78 as 10, and B93 as 11.

Sub IncrementEveryFifteenRows()

Dim startValue
Dim selectedRows

'Using Selection, cell references are relative to the selected range 
'and not the worksheet as a whole
startValue = Selection.Cells(1, 1).Value
selectedRows = Selection.Rows.Count

'Loop to jump to every fifteenth row, incrementing startValue each iteration
With Selection
    For i = 1 To selectedRows Step 15
        .Cells(i, 1).Value = startValue
        startValue = startValue + 1
    Next i
End With

End Sub

此代码可以通过用户提示收集值进一步抽象例如步骤值，增量值，开始和结束的特定行以及应该应用于哪个列。这只需要在第一个例子中声明更多的变量代替硬编码的数字。



希望这会有所帮助，祝你好运。

This code can be abstracted further with user prompts to gather values such as step value, increment value, specific rows on which to begin and end, and which column this should apply to. That would just require declaring more variables to put in place of the hard coded numbers in the first example.

Hope this helps, and good luck.

这篇关于使用VBA在excel表中每15行增加一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！