因此,如果我在宏中计算了value1、value2和value3,那么在随后的单元格计算中如何使用例如value1?
Sub GetPrice()
Dim wb As Workbook
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim Rnge As Range
Dim rRange As Range
Dim rng As Range
Dim catRange As Range
Dim rCell As Range
Dim sdate As Date
Dim name As String
Dim discount As Integer
Set ws = Sheets("Sheet3")
Set ws1 = Sheets("Report")
Set rng = ws1.Range("B22")
Set catRange = ws1.Range("W21:Z21")
sdate = rng
name = rng.Offset(0, -1).Value
discount = 12 ' ActiveCell.Offset(0, 25).Value
Set rRange = ws.ListObjects("Pricing").Range
ws.ListObjects("Pricing").AutoFilter.ShowAllData
If name = "SomeName" Then
With rRange
.AutoFilter Field:=2, Criteria1:="AA"
.AutoFilter Field:=6, Operator:=xlFilterValues, Criteria2:=Array(2, Format(sdate, "yyyy-mm-dd"))
.AutoFilter Field:=13, Criteria1:=discount
For Each rCell In catRange.Cells
If rCell = "SomeValue" Then
.AutoFilter Field:=11, Criteria1:="AA"
Set Rnge = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
Price1 = Rnge.Cells(1, 14).Value
ElseIf rCell = "SomeName2" Then
.AutoFilter Field:=11, Criteria1:="=AA", Operator:=xlOr, Criteria2:="=AA"
Set Rnge = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
Price2 = Application.WorksheetFunction.Min(Rnge.Columns(14))
ElseIf rCell = "SomeName3" Then
.AutoFilter Field:=11, Criteria1:="=AA", Operator:=xlOr, Criteria2:="=AA"
Set Rnge = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
Price3 = Application.WorksheetFunction.Min(Rnge.Columns(14))
ElseIf rCell = "SomeName4" Then
.AutoFilter Field:=11, Criteria1:="=AA", Operator:=xlOr, Criteria2:="=AA"
Set Rnge = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
Price4 = Application.WorksheetFunction.Min(Rnge.Columns(14))
End If
Next rCell
End With
End If
ws.ListObjects("Pricing").AutoFilter.ShowAllData
End Sub
