如何从公式VBA获取行引用 [英] How to get row references from a formula VBA

问题描述

您好，我得到了一张包含一些公式的工作表，例如:

Hi I have been given a sheet with some formulas in them for example:

= SUM(D4:D1051)-可以假装这是在cell(1,1)

=SUM(D4:D1051) - can pretend this is in cells(1,1)

如何使用VBA拔出行首和行尾?

With VBA how can I pull out the row start and row end?

理想情况下，我会使用 lRowStart = 4 和 lRowEnd = 1051 ，但是我不确定要使用的语法.

Ideally i would have lRowStart = 4 and lRowEnd = 1051 but I am not sure of the syntax to use to get this.

推荐答案

您可以使用 .Precedents

Dim rng As Range
Dim rowStart As Long
Dim rowEnd As Long

On Error Resume Next 'in case there are no precedents
Set rng = Cells(1, 1).Precedents
On Error GoTo 0

If Not rng Is Nothing Then
    rowStart = rng.Row                    'or rng.Areas(1).Row (see edit)
    rowEnd = rng.Row + rng.Rows.Count - 1 'or rng.Areas(1).Row and rng.Areas(1).Rows.Count
Else
    rowStart = 0
    rowEnd = 0
End If

---

编辑，有些情况比较棘手.如果公式包含多个引用，例如 = SUM(B1:B2)+ SUM(D3:D4)，您将获得范围的并集.如果所引用的单元格本身具有对其他单元格的引用，则同样如此.

---

Edit there are a few cases that are tricky. If the formula contains multiple references, e.g. =SUM(B1:B2) + SUM(D3:D4) you will get a union of ranges. The same is true if the cells that are referenced have references to other cells themselves.

在这种情况下，您可以使用 .Areas 来获取范围所包含的各个区域.我不确定它们的确切排序方式，但似乎顶级"引用是第一个.示例:

In these cases, you can use .Areas to get the individual areas the range consists of. I'm not sure how they are ordered exactly but it seems that the "top-level" references are first. Example:

Dim rng As Range
Dim ar As Range

Range("A1").Formula = "=sum(B5:B7) + B1"
Range("B6").Formula = "=B3"

Set rng = Range("A1").Precedents

For Each ar In rng.Areas
    Debug.Print ar.Address
Next ar

输出:

$B$5:$B$7
$B$1
$B$3

但是要小心，因为如果区域彼此相邻，则区域将合并在一起.

However be careful as areas will be combined if the are next to each other.

似乎还不能很好地处理对其他工作表的引用.

It also seems that it can't handle references to other sheets very well.

这篇关于如何从公式VBA获取行引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！