How to input formula into cells while citing different worksheets

Question

I have been using VBA for a while and now I am stuck on this issue:

Say I have A,B,C,D,E... in each cell as worksheets' names&&the name matches 100% each worksheet's name(worksheet A, B, C...)

So how do I use VBA to input formula into COL2, as the form like ='sheetname!'$B$2(Cell address)? The sheetname inside formula corresponds to COL1(&&each worksheet's name)

Usually the VBA returns the value only, but I need REFERENCE by printing the formula this time.

----COL1----COL2

R1-- A ＝'A!'$B$2

R2-- B ＝'B!'$B$2

R3-- C ＝'C!'$B$2

R4-- D ＝'D!'$B$2

R.-- ... ＝'...!'$B$2

In real case the worksheet name is very messy,not like structured letter&num,

Your advice is much appreciated! Thank you!

I guess two pictures will illustrate my question better:

overview worksheets

seems I cant embed image yet..but anyway... so my goal is: 1.Get the name from Name(C) col. 2.Write the formula in Supplier(D) col. ='(name in Col.C)'!$B$2

Note:The names in col.C correspond to worksheets.(picture2)

*My original plan is:

For Each wrksheet In Worksheets

    If wrksheet.Index >= 10  Then

        sheetname = wrksheet.Name #1 pass wrksheet's name into sheetname variable
        sum.Cells(i, 4).formula = "='sheetname!'$B$2" #2 input current worksheet's cell(B2) and print into worksheet sum's cell(i,4)

    End If
    i=i+1

But, obviously, this trick (#2) doesn't work.... So how do I auto-write formula after getting worksheet's name? Hope this is clear for you guys?

========================================== Sub formula()

Dim s As Worksheet, w As Worksheet
Set s = Worksheets("Summary")
Dim i, stname

i = 1
For Each w In Worksheets

    If w.Index > 1 Then

        stname = w.Name
        s.Cells(i, 4).formula = "=" & stname & "!$B$2"


    End If
    i = i + 1
Next w

End Sub

but the code reports problem...why is that?

Answer 1

It is difficult to understand what you are trying to ask, but I'll do my best to solve your problem. In future, it is advisable to include code along with your question.

To input formula into a cell, you can use the Range.Formula method. There is a good explanation on how this works here:- https://msdn.microsoft.com/en-us/library/office/ff838835.aspx

It is my understanding, however, that you are looking to input the formula as text, so that you can refer to it later on. The simplest way to do this is to use the Range.Value method. This is described here:- https://msdn.microsoft.com/en-us/library/office/ff195193.aspx

However, if you simply insert a formula in the format =FORMULA into Range.Value you will find that Excel will interpret this as a formula and thus attempt to display the value for its solution. The simplest way to overcome this is to include an apostrophe before the equals sign in your formula like so:- '=FORMULA

This forces Excel to interpret the formula as text only. The cell will display your chosen formula in a readable format. Therefore, your solution will look something like so:-

Cells(1,2).Value = "'='" & Cells(1,1).Value & "!'$B$2"

Whereby Cells(1,1) contains the name of your worksheet, and Cells(1,2) will become your reference formula.

To later refer to this cell and use it as a formula you will need to remove the apostrophe we have just added. There are a number of ways you could do this; if you need additional help just ask.