如何通过VBA将动态范围从另一个工作表引用到活动工作表单元格

I am not getting dynamic range work sheet name in formula. Can anyone assist on this? I get the active sheet range in the formula as =COUNTIF(I3:I31,A3)

我没有在公式中获得动态范围工作表名称。任何人都可以协助吗?我得到公式中的活动工作表范围= COUNTIF(I3:I31,A3)

Sub Test_DaySumm()

Dim Rg As Range
wksMain.Select
Dim ws1 As Worksheet
Set ws1 = wksMain

If ws1.FilterMode = True Then ws1.ShowAllData
LastRow = ws1.Cells(Rows.Count, "I").End(xlUp).Row
Set MyRange = ws1.Range(Cells(3, 9), Cells(LastRow, 9))

wksDaySummary.Select
xRow = 3
Do Until wksDaySummary.Cells(xRow, 1) = ""
    Set Rg = wksDaySummary.Cells(xRow, 2)
    Rg.Formula = "=COUNTIF( " & MyRange.Address(0, 0) & "," & Rg.Offset(0, -1).Address(0, 0) & ")"
    Rg.Value = Rg.Value
    xRow = xRow + 1
DoEvents
Loop

End Sub

1 个解决方案

#1

Have a look at the Range.Address Property documentary

看看Range.Address Property纪录片

.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

and use the External:=True parameter get get the worksheet name in the address too:

并使用External:= True参数获取地址中的工作表名称:

MyRange.Address(RowAbsolute:=False, ColumnAbsolute:=False, External:=True)

So instead of eg I3:I31 the result would be like 'Main Sheet'!I3:I31

因此,而不是例如I3:I31,结果就像'主表'!I3:I31

#1