Printing equation into cell

[tms_]

I'm trying to print an equation into a cell. But when I add the = character, it gives runtime-error 1004. What is this about?

nimim = ThisWorkbook.Name

Range("B8").Value = "=HLOOKUP([" + nimim + "]Esitietolista!A$1;[" + nimim + "]Kielet!D:H;Telat!A109;FALSE)"

or

Range("B8").Value = Chr(61) + "HLOOKUP([" + nimim + "]Esitietolista!A$1;[" + nimim + "]Kielet!D:H;Telat!A109;FALSE)"

[p45cal]

try:
[VBA]Range("B8").Formula = "=HLOOKUP('[" + nimim + "]Esitietolista'!A$1;'[" + nimim + "]Kielet'!D:H;Telat!A109;FALSE)"
[/VBA]

[tms_]

It gives the same error...

Edit. Please note, that nimim = ThisWorkbook.Name
The code is meant to work on embedded worksheet and link to "base worksheet"

[p45cal]

Please note, that nimim = ThisWorkbook.Name
The code is meant to work on embedded worksheet and link to "base worksheet"

1. ..and these worksheets, which workbooks are they in?
2. Which worksheet and workbook is the cell B8 in?
3. Which workbook is the code in? (this is important for "nimim = ThisWorkbook.Name")
4. What kind of module is the code in? (Standard code module/sheet code module/Thisworkbook code module/userform code module/class code module)
5. Are all worksheet names (a) correctly spelt and (b) do they all exist?
6. Have you saved at least once, the workbook referred to in "nimim = ThisWorkbook.Name"?

Please explicitly answer each of the above 1 to 6 (a and b)

One thing you can do is when you get the error, choose debug and in the Immediate pane, type:
[vba]?"=HLOOKUP('[" + nimim + "]Esitietolista'!A$1;'[" + nimim + "]Kielet'!D:H;Telat!A109;FALSE)"[/vba](or whatever your code is at the time) and press Enter. Does it look as you expect? If so, copy it and try to paste it directly into the cell. (If all you see is the string when you've done that, edit the cell without changing anything and press Enter.) What does it complain about?

And one more question, you did try the whole line I suggested earlier, not just a change from .value to .formula?

[rbrhodes]

Hi tms_

Well I don't know about your use of + vs & and your use of ; vs , that I am used to. However, you're trying to do a lookup in two sheets? Can't be done as far as I know

=HLOOKUP( [THISWORKBOOK]Esitietolista!A$1;[THISWORKBOOK]Kielet!D:H;[SOMEOTHERWORKBOOK_cause_the_name_was_left_out]Telat!A109;FALSE)"

is not going to work.

Try putting in the variable for the last sheet. At least it worked for me...

Call me crazy but the Excel Lookups return a value form the table you specify, not from somewhere else...

I opened book1.xls and put this code in to write to Book4.xls

[VBA]

Option Explicit

Sub PutFormula()

Dim nimim As String
Dim WhereFormulaGoes As Worksheet

nimim = ThisWorkbook.Name

Set WhereFormulaGoes = Workbooks("Book4.xls").Sheets(1)

WhereFormulaGoes.Range("B8") = "=HLOOKUP([" & nimim & "]Esitietolista!A$1,[" & nimim & "]Kielet!D:H,[" & nimim & "]Telat!A109,FALSE)"

End Sub

[/VBA]

worked for me