excel 将命名区域的行分配给范围变量

2skhul33 于 2023-03-20 发布在其他

关注(0)|答案(2)|浏览(190)

我想将工作表的一个区域指定为一个名称，然后将该命名区域的每一行指定为VBA中的一个范围变量。
我可以将命名区域赋给一个范围变量，但必须传递整个区域。如果我尝试使用.Rows（）只将特定的Row保存到一个范围变量，我将无法访问任何应该在命名变量中的值。
示例：

Dim FirstRow as range: set FirstRow = Worksheets("worksheetname").Range("NamedArea").Rows(1)

'Trying to get out any value:
Debug.Print FirstRow 'Runtime Error 13: types incompatible
Debug.Print FirstRow(1) 'Runtime Error 13: types incompatible
Debug.Print FirstRow(1,1) 'Runtime Error 13: types incompatible

'Trying to iterate through values:
For each i in FirstRow
   Debug.Print "hello" ' Output: "hello" 'only once
   Debug.Print "i" 'Runtime Error
next i

excel

来源：https://stackoverflow.com/questions/75734197/assign-a-row-of-a-named-area-to-a-ranged-variable

2条答案

按热度按时间

a8jjtwal1#

'the code must be in the sheets module

Private Sub example()
   Dim i As Range, FirstRow As Range, cc As Long
   
   Set FirstRow = Me.Range("NamedArea").Rows(1)
   
   Debug.Print FirstRow.Address
   Debug.Print FirstRow.Cells(1, 1)

   For Each i In FirstRow.Cells
      cc = cc + 1
      Debug.Print cc & ". " & i.Address & " = " & i.Value
   Next i
   
End Sub

'if you want to set this sub in a module out of any sheet's module, 'the better is to call it with two parameters:

Public Sub example(ws As Worksheet, rngName As String)
   Dim i As Range, FirstRow As Range, cc As Long
   
   Set FirstRow = ws.Range(rngName).Rows(1)
   
   Debug.Print FirstRow.Address
   Debug.Print FirstRow.Cells(1, 1)

   For Each i In FirstRow.Cells
      cc = cc + 1
      Debug.Print cc & ". " & i.Address & " = " & i.Value
   Next i
   
End Sub

'and from a sheet's module call it like this:

Private Sub callExample()
   Call example(Me, "NameOfAnyNamedRange")
End Sub

赞(0）回复(0）举报 2023-03-20

mf98qq942#

您可以尝试：

Sub Question()
Dim FirstRow As Range
Dim myRange As Range
Dim i As Range
Set myRange = Worksheets("Sheet1").Range("MyRegion")

Dim count As Integer: count = 1
For Each i In myRange
   Debug.Print count
   count = count + 1
Next i
End Sub

赞(0）回复(0）举报 2023-03-20

我来回答

excel 将命名区域的行分配给范围变量

2条答案

相关问题

热门标签

最新问答