多个工作表上的增量值 [英] Increment value on multiple worksheets

问题描述

有一个excel工作表，其中有几个工作表，所有工作表的标签都不同，根本没有模式(即Sheet1，Sheet2，Sheet3等)在单元格 B2 的第一个工作表中，我的值是 90 ，并且希望其他工作表将值自动加1.希望第二个工作表在 B2 上具有 91 的值，依此类推，我知道我可以繁琐地添加 = SUM('WorksheetName'！B2 +1)来执行此操作，但是这将花费我很多时间，因为我必须在每个实例中手动键入工作表名称有什么方法可以添加一些VBA吗?或其他公式?

Have an excel worksheet that has several worksheets that are all labeled differently, no pattern at all (ie; Sheet1, Sheet2, Sheet3 etc) In the first worksheet on cell B2 I have a value of 90 and would like every other worksheet to auto increment value by 1. Would want second worksheet to have a value of 91 on B2 and so on,, I know i could tediously add =SUM('WorksheetName'!B2 +1) to do this but would take forever as i would have to manually type the sheet name each instance Any way to add some VBA? or another formula?

为帮助澄清，我的第一个工作表被标记为 101 ，在单元格B2上的值为 90

To help clarify, My first worksheet is labeled 101 and has a value of 90 on cell B2

推荐答案

如果我正确理解，这应该可以做你想要的.

If I understand correctly, this should do what you want.

Sub x()

Dim i As Long, n As Long

n = 90

For i = 1 To Worksheets.Count
    Sheets(i).Range("B1").Offset(i - 1).Value = n + i - 1
Next i

End Sub

这篇关于多个工作表上的增量值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！