Can I insert a variable into a string?

Question

I'm trying to make a program in the Excel VBA that inserts a formula into a column of cells. This formula changes based on the contents of the cell directly to the left. This is the code I have written so far:

Sub Formula()
Dim colvar As Integer
colvar = 1
Dim Name As String
Name = "Sample, J."
Do While colvar <= 26
    colvar = colvar + 1
    Name = Range("B" & colvar).Value
    Range("C" & colvar).Value = "='" & Name & "'!N18"

Loop
End Sub

As you can see, I want to insert the variable Name between the formula strings, but Excel refuses to run the code, giving me a "application-defined or object-defined error."

Is there a way to fix this?

Answer 1

You will need some error checking in case the sheets don't actually exist in the workbook.

it looks like you are looping through column B that has a list of sheet names and want range N18 to display next to it.

Something like

    Sub Button1_Click()
    Dim Lstrw As Long, rng As Range, c As Range
    Dim Name As String

    Lstrw = Cells(Rows.Count, "B").End(xlUp).Row
    Set rng = Range("B1:B" & Lstrw)

    For Each c In rng.Cells
        Name = c
        c.Offset(, 1) = "='" & Name & "'!N18"
    Next c

End Sub

Or you can just list the sheets and show N18 next to it, run this code in a Sheet named "Sheet1"

    Sub GetTheSh()
    Dim sh As Worksheet, ws As Worksheet

    Set ws = Sheets("Sheet1")

    For Each sh In Sheets

        If sh.Name <> ws.Name Then

            ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1) = sh.Name
            ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(0, 1) = sh.Range("N18")

        End If

    Next sh


End Sub

Answer 2

Thank you to everyone for your help! I actually found that I had just made a silly error: the line Do While colvar<=26 should have been Do While colvar<26. The cells were being filled, but the error manifested because one cell was being filled by a nonexistent object.

I did decide to use the .Formula modifier rather than .Value. Thank you to Jeeped for suggesting that.