我有一个Primary Sheet来做计算,这些取决于年份。
我有VBA代码,使用正确的范围(在'工作表#2'命名为"_2022")从同一个工作簿。
是否可以根据我在"主工作表"中的特定单元格中键入的年份,在此工作簿中使用相同的范围,但来自不同的工作表?
下面是我的Range的基本代码:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Honoraires As Double
Dim Revenu As Variant
Dim LastValues As Variant
Dim k As Double
Dim drow As Double
Dim DValue As Double
Dim CountRow As Integer
If Target.Address = "$B$20" Then
Honoraires = Worksheets("HONORAIRES VS. SALAIRE").Range("B19").Value
LastValues = 0
CountRow = 4
For Each Cell In Worksheets("_2022").Range("B5:B102")
If Cell.Value > Honoraires Then
我想修改的是——>工作表中的每个单元格("_2022">.Range("B5: B102")——比;范围将保持不变。
- 适用于我的"主要工作表"中的单元格(让我们使用"E15"),名为"HONORAIRE VS. SALAIRE"这将切换并使用sheet "_2018"或";_2019"或者当我输入它的名字(或者从下拉选择)
像这样:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Honoraires As Double
Dim Revenu As Variant
Dim LastValues As Variant
Dim k As Double
Dim drow As Double
Dim DValue As Double
Dim CountRow As Integer, SheetName As String, wsHVS As Worksheet '<<
If Target.Address = "$B$20" Then
Set wsHVS = ThisWorkbook.Worksheets("HONORAIRES VS. SALAIRE") 'use a worksheet reference...
Honoraires = wsHVS.Range("B19").Value
SheetName = wsHVS.Range("E15").Value '<<<
LastValues = 0
CountRow = 4
For Each Cell In ThisWorkbook.Worksheets(SheetName).Range("B5:B102").Cells
If Cell.Value > Honoraires Then