VBA Excel访问具有命名范围的索引 [英] VBA Excel accessing index with named range

问题描述

VBA Excel中的快速示例:

Quick example in VBA Excel:

我定义了两个具有相同尺寸的命名范围: MyNamedRangeA 和 MyNamedRangeB .
这些是列向量.

I have defined two named ranges with the same dimensions: MyNamedRangeA and MyNamedRangeB.
These are column vectors.

Dim cell As Range
For Each cell in .range(MyNamedRangeA)
   Call MyFunction(cell)
Next cell

如果 MyNamedRangeB 中具有相同索引的另一个单元格具有字符串值 x，我希望 MyFunction 隐藏 cell 的行

I want MyFunction to hide the row of cellif another cell with the same index in MyNamedRangeB has the string value x

Sub MyFunction(cell As Range)
index = GetIndex(cell)
If .range(MyNamedRangeB)(index) = "x" Then
   cell.EntireRow.Hidden = True
End If

我想获得完成此程序的帮助.

I would like to get help with completing this program.

具体来说，我需要找到一种执行"GetIndex"操作的方法，即提供单元格的行索引

Specifically, I need to find out a way to perform the "GetIndex" operation, i.e. to provide the row index of the cell

推荐答案

dim i as Integer
dim myRangeA as Range
dim myRangeB as Range

dim MyNamedRangeA as String
dim MyNamedRangeB as String

With MySheetReference    
    set myRangeA = .Range(MyNamedRangeA)
    set myRangeB = .Range(MyNamedRangeB)


    For i = 1 to myRangeA.Cells.Count
      If myRangeB.Cells(i).Value = "x" Then
         myRangeA.Cells(i).EntireRow.Hidden = True
      End If
    Next
End With

有帮助吗?

这篇关于VBA Excel访问具有命名范围的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！