将excel公式加1 [英] Increment excel formula by 1

问题描述

我正在尝试复制并粘贴一些单元格，保持格式并将它们链接到表格.

I'm trying to copy and paste a few cells keeping the format and them ebing linked to a table.

目前我有一张表格，但我正在从另一张表格中引用它例如:

Currently i have a table but i am referencing it from another sheet e.g:

=Sheet2!A1  =Sheet2!B1  =Sheet2!C1
=Sheet2!D1  =Sheet2!E1  

我试图将它一次复制 4 行并粘贴多次，这些值会一直转到当前的单元格 ID，但我想每次都将它们增加 1，例如当我粘贴时，我希望它们是:

I'm trying to copy it down 4 lines at a time and paste multiple times, the values keep going to the current cell ID but i'm wanting to increment them by 1 each time, e.g. when i paste i would like them to be:

=Sheet2!A2  =Sheet2!B2  =Sheet2!C2
=Sheet2!D2  =Sheet2!E2

有没有比手动将所有单元格 ID 更改为 +1 更快的方法?

Is there any quicker way than manually changing all of the cell ids to +1?

谢谢！

推荐答案

选择你想要的单元格并在 VBA 中运行以下:

Select the cells you want and run the following in VBA:

Public Sub TestMe()

    Dim myCell  As Range

    For Each myCell In Selection
        If myCell.HasFormula Then myCell.Formula = myCell.Formula & "+1"
    Next myCell

End Sub

<小时>

如果你按太多次了，最后一个+1是这样去掉的:

Public Sub UnTestMe()

    Dim myCell As Range

    For Each myCell In Selection
        If myCell.HasFormula Then myCell.Formula = Left(myCell.Formula, _
                                                Len(myCell.Formula) - 2)
    Next myCell

End Sub

<小时>

关于@SJR的评论，如果你想改变单元格的引用地址，这是一种可能的解决方法:

---

Concerning the comment of @SJR, if you want to change the reference address of the cell, this is one possible workaround:

Public Sub TestMe()

    Dim myCell As Range

    For Each myCell In Selection
        If myCell.HasFormula Then myCell.Formula = Left(myCell.Formula, _
                                                Len(myCell.Formula) - 1) & 2
    Next myCell

End Sub

只需将 &2 更改为您要引用的数字即可.

Simply change the &2 to the number you want to refer to.

这篇关于将excel公式加1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！