How to get value from VBA Sub to cell?

Question

How can I use a value calculated in the Sub macro in a cell?

So if I have calculated value1, value2 and value3 in a macro, how can I use e.g value1 in calculations in a cell afterwards?

EDIT:

Here is my code, which runs fine and prints the correct values when i use Debug.Print, but how can I use the values Price1, ..., Price4 in further calculations?

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

Are you sure you don't mean to have a function instead, that returns a value and that's invoked with a cell formula, e.g. =MyAwesomeFunction(A1,42)? — Mathieu Guindon
Yes, I think that is what I want @Mat'sMug, I have added my VBA code now, if you could take a look. — joddm

Salek Salek · Accepted Answer · 2017-07-10T21:28:01

Include in macro:

Sheet.Cells(1,"A").Value = value1
Sheet.Cells(2,"A").Value = value2

etc Set formula for cell B2, where

=A1*A2

Or, include formula in Macro:

result = value1*value2
Sheet1.Cells(1,"A").Value = result

How to get value from VBA Sub to cell?

1 Answers