How to use cell contents as inputs for range formulas in Excel VBA?

Question

Excel VBA range formula is something like Sheet2.Range("A1").value. You can, for example, refer to this address and change the value in that cell. Now what I am looking for is to build this formula by using the contents of two cells; one cell says "Sheet2" and the other says "A1". How can I refer to these two cells in a formula so that it works the same way?

I have tried various dims and now end up without an error but not with the desired result. The current formula simply returns "Sheet2.Range("A1").value instead of its contents.

Sub AddressInCells()
    'This is just for one reference. In reailty there is a table of references to loop through.
    'For this example cell A1 in Sheet2 = "Text to take to sheet1"

    Dim SheetSel As String
    Dim CellSel As String
    Dim ReferSel As String

    With Sheet1

        SheetSel = Cells(1, 1).Value
        CellSel = Cells(1, 2).Value

        ReferSel = SheetSel & ".range(" & Chr(34) & CellSel & Chr(34) & ").value"

        MsgBox ReferSel

        Sheet1.Range("D1").Value = ReferSel 'Results in "Sheet2.range("A1").value
        Sheet1.Range("D2").Value = Sheet2.Range("A1").Value 'Results in Text to take to sheet1'

    End With

End Sub

The expected result is that the formula works the "usual" way. It now simply returns a string.

Answer 1

I believe, instead you want to do:

ReferSel = Sheets(SheetSel).range(CellSel).value

Answer 2

You could try:

Option Explicit

Sub test()

    Dim ws As Worksheet
    Dim rng As Range

    'With statement where the data are store. Change name if needed
    With ThisWorkbook.Worksheets("Sheet1")
        Set ws = ThisWorkbook.Worksheets(.Range("A1").Value)
        Set rng = ws.Range(.Range("A2").Value)
    End With

    Debug.Print rng.Address
    Debug.Print rng.Worksheet.Name

End Sub

Answer 3

You're trying to build a formula string, which follows this convention:

'<sheet name>!'<address>

The single-quotes are only strictly required if the sheet name contains a space, so this would be a valid formula:

=Sheet1!$A$1

But it's just good habit to always include them, so, you need to build your string from the values in those cells:

' offset the sheet name in single quotes, separate the sheet & address with !
ReferSel = "='" & SheetSel "'!" & CellSel

Sheet1.Range("D1").Formula = ReferSel