PDA

View Full Version : Solved: Writing formula into cell with Macro. Inverted commas problem.



vanhunk
08-24-2012, 03:58 AM
Good day to you,
I need to have a macro write the following formula into a cell, but with all the inverted commas (") it does not work and I can't get it to work. Where do I need to insert what for it to work? Please help.

=IF(OR($N$43="",$N$44="",$N$45=""),"",INDIRECT(" '" & $N$43 & "[" & $N$44 & "]" & $N$45 & "'!" & "E6"))



Dim ws As Worksheet
ws.Range("M49").Formula = "=IF(OR($N$43="",$N$44="",$N$45=""),"",INDIRECT(" '" & $N$43 & "[" & $N$44 & "]" & $N$45 & "'!" & "E6"))"

snb
08-24-2012, 04:04 AM
You can easily check this using:
MsgBox "=IF(OR($N$43="""",$N$44="""",$N$45=""""),"""",INDIRECT("" '"" & $N$43 & ""["" & $N$44 & ""]"" & $N$45 & ""'!"" & ""E6""))"

If the result in the messagebox is identical to what the formula should look like in the cell, it is correct.

Bob Phillips
08-24-2012, 05:02 AM
That formula looks a tad odd. What is in N43?

vanhunk
08-24-2012, 07:05 AM
Thanks a lot,

Great tip. I had to remove one space for it to work.

Final code:

MsgBox "=IF(OR($N$43="""",$N$44="""",$N$45=""""),"""",INDIRECT(""'"" & $N$43 & ""["" & $N$44 & ""]"" & $N$45 & ""'!"" & ""E6""))"

It is the path of a workbook file.

Thanks again.

D:\PSM\PSIM Model\

Bob Phillips
08-24-2012, 07:23 AM
You do realise that INDIRECT does not work with a closed file don't you?

vanhunk
08-24-2012, 07:31 AM
Hi snb,

I have spoken too soon. I now get an error message of "Run-time error '91': Object variable or with block variable not set."

Thanks again.

vanhunk
08-24-2012, 07:32 AM
Thanks xld,

Yes I know.