我在公式中没有获得动态范围工作表名称。有人可以协助吗?我在公式中得到的活动工作表范围为=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
最佳答案
.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
并使用
External:=True
参数获取地址中的工作表名称:MyRange.Address(RowAbsolute:=False, ColumnAbsolute:=False, External:=True)
因此,结果而不是例如
I3:I31