[Solved] Using quotes in macro issue

[sokolowitzky]

I need to insert indirect(address()) into some cells in some sheets. If I can not find a solution probably I will use some kind of copy cell into another one.
But before that I would like to learn if what I'm trying to do is technically impossible.

Since quotes are used for strings in macros, OO basic does not let me insert this code below.
I tried to declare even a variable. But no, still no joy. Anyone have any idea?

Code: Select all

Sub Main
Dim ABS as string
ABS = CHAR(34)&"E1"&CHAR(34)
cell=Thiscomponent.Sheets(1).getcellrangebyname("ax1")
cell.string="=INDIRECT(ADDRESS(1;1;;;"+""+ABS+""+"))"
cell.formula=cell.string
End Sub

[FJCC]

I'm not entirely sure what you want your formula output to be, but try this

Code: Select all

cell=Thiscomponent.Sheets(1).getcellrangebyname("ax1")
cell.formula= "=INDIRECT(ADDRESS(1;1;1;" & """E1""" & "))" 

Some comments:
It is enough to set the cell.formula, you do not have to also set the cell.string
ABS is the name of a function in Basic. Don't name a variable ABS, it can lead to trouble.

[sokolowitzky]

I use variable Indirect(address()) a lot for my own tables. But I could not figure out the problem with quote marks.
That answer will help me alot. It works, many thanks.

[Villeroy]

You don't have to put quotes around the sheet name.
=INDIRECT(ADDRESS(1;1;;;E1)) works even if the sheet name in E1 contains spaces.