Macro writing formulas

[SanFranGuy06]

Hi, so I'm having some issues writing a line in a macro that I'm
working on. I'm not sure why the below formula doesn't work:


ActiveCell.FormulaR1C1 = "=bds(RC[-8]&"" equity"",$i
$1,""cols=7;rows=10"")"

which should get copied into the Excel sheet like this:

=BDS(A2&" equity",$I$1,"cols=7;rows=2")

Can anyone help me out here?

 

[FSt1]

hi
=bds???????
that is not a buit in function for 2003 and earlier nor is it part of the
analysis tool pack. is it an UDF? if so post your UDF code.

Regards
FSt1

 

[SanFranGuy06]

Sorry, this is an outside function. If you're familiar with Bloomberg
at all, it's a bulk data function. I've seen a few posts where other
posters were bringing in custom functions as well. I essentially want
to bring in a variable target cell into the equation:

i.e.
=BDS(A2&" equity",$I$1,"cols=7;rows=2")
=BDS(A12&" equity",$I$1,"cols=7;rows=2")
=BDS(A(insert activerow's number here)&" equity",$I$1,"cols=7;rows=2")

hi
=bds???????
that is not a buit in function for 2003 and earlier nor is it part of the
analysis tool pack. is it an UDF? if so post your UDF code.

Regards
FSt1

Hi, so I'm having some issues writing a line in a macro that I'm
working on. I'm not sure why the below formula doesn't work:

ActiveCell.FormulaR1C1 = "=bds(RC[-8]&"" equity"",$i
$1,""cols=7;rows=10"")"

which should get copied into the Excel sheet like this:

=BDS(A2&" equity",$I$1,"cols=7;rows=2")

Can anyone help me out here?- Hide quoted text -

- Show quoted text -

 

[Niek Otten]

$I$1 is not a R1C1 reference

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hi, so I'm having some issues writing a line in a macro that I'm
| working on. I'm not sure why the below formula doesn't work:
|
|
| ActiveCell.FormulaR1C1 = "=bds(RC[-8]&"" equity"",$i
| $1,""cols=7;rows=10"")"
|
| which should get copied into the Excel sheet like this:
|
| =BDS(A2&" equity",$I$1,"cols=7;rows=2")
|
| Can anyone help me out here?

 

[SanFranGuy06]

Wow, thanks Niek! Would you know how I could anchor the $i$1 cell in
the R1C1 reference scheme? The row of the active cell that this
formula will go into will be constantly changing.

 

[Dave Peterson]

Dim myForm As String
ActiveCell.FormulaR1C1 _
= "=BDS(A" & ActiveCell.Row & "&"" equity"",$I$1,""cols=7;rows=2"")"

or

ActiveCell.FormulaR1C1 = "=bds(RC1&"" equity"",R1C9,""cols=7;rows=2"")

RC1 (same row, column 1)
R1C9 (row 1, column 9 = $i$9)

If you use A1 reference style, you use .formula
If you use R1C1 reference style, you use .formulaR1C1

But you don't want to mix A1 and R1C1 styles in your formula.

Sorry, this is an outside function. If you're familiar with Bloomberg
at all, it's a bulk data function. I've seen a few posts where other
posters were bringing in custom functions as well. I essentially want
to bring in a variable target cell into the equation:

i.e.
=BDS(A2&" equity",$I$1,"cols=7;rows=2")
=BDS(A12&" equity",$I$1,"cols=7;rows=2")
=BDS(A(insert activerow's number here)&" equity",$I$1,"cols=7;rows=2")

hi
=bds???????
that is not a buit in function for 2003 and earlier nor is it part of the
analysis tool pack. is it an UDF? if so post your UDF code.

Regards
FSt1

Hi, so I'm having some issues writing a line in a macro that I'm
working on. I'm not sure why the below formula doesn't work:

ActiveCell.FormulaR1C1 = "=bds(RC[-8]&"" equity"",$i
$1,""cols=7;rows=10"")"

which should get copied into the Excel sheet like this:

=BDS(A2&" equity",$I$1,"cols=7;rows=2")

Can anyone help me out here?- Hide quoted text -

- Show quoted text -

 

[Niek Otten]

R1C9

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Wow, thanks Niek! Would you know how I could anchor the $i$1 cell in
the R1C1 reference scheme? The row of the active cell that this
formula will go into will be constantly changing.

 

[SanFranGuy06]

Great! Thanks guys for all the help! Got it working!!!

R1C9

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


Wow, thanks Niek! Would you know how I could anchor the $i$1 cell in
the R1C1 reference scheme? The row of the active cell that this
formula will go into will be constantly changing.

$I$1 is not a R1C1 reference

Niek Otten
Microsoft MVP - Excel

<[email protected]> wrote in messagenews:d22679c6-3337-4020-9b5a-e249c2075c83@z17g2000hsg.googlegroups.com...

| Hi, so I'm having some issues writing a line in a macro that I'm
| working on. I'm not sure why the below formula doesn't work:
|
|
| ActiveCell.FormulaR1C1 = "=bds(RC[-8]&"" equity"",$i
| $1,""cols=7;rows=10"")"
|
| which should get copied into the Excel sheet like this:
|
| =BDS(A2&" equity",$I$1,"cols=7;rows=2")
|
| Can anyone help me out here?- Hide quoted text -

- Show quoted text -