Referencing between sheets with formulas generated in VBA

Question

I'm trying to populate a cell with a basic formula that references both the current worksheet and another worksheet.

The formula I am trying to input into a cell is basically... ='Sheet2'A(j) / B(i) with the following code:

For i = 1 To 5
    For j = 1 To 5
        With shOut.Cells(i,j)
            .formula = "='Sheet2'" & Cells(j,1)).Address & "/" & Cells(i,2).Address
        End With
    Next j
Next i

The part I am having trouble with is the sheet referencing at the start; when I run this in a .formula, it triggers an error.

However, when I remove the '=' sign and make it output in a .Value, it prints out what I want it to, except it's not a formula.

Answer 1

You shouldn't have to construct the address of any range yourself - you'll only leave yourself open to potential problems by doing so.

For example, in cularis' (perfectly good) answer, there are no single-quotes around the sheet name. They're not required in this case, but if the sheet name had a space in it, then you do need the quotes. (My point is that you shouldn't have to know - and cater for - all this stuff yourself).

Instead, you can specify External:=True in the call to Address(), which will give you an address that includes the sheet name. I'd do something like this:

Dim oWorksheet1 As Worksheet
Dim oWorksheet2 As Worksheet

...

.Formula = "=" & oWorksheet1.Cells(j,1).Address(External:=True) & "/" & oWorksheet2.Cells(i,2).Address(External:=True)

Note that in your case you probably only need to use the External parameter for one of the worksheets, but doing it that extra time won't hurt, as Excel will simplify the formula for you anyway.

Answer 2

1. You have a ) too much in your line that begins with .formula.

2. You are missing the ! after the sheet:

   .formula = "=Sheet2!" & Cells(j,1).Address & "/" & Cells(i,2).Address