Macro writing formulas
 

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?

Macro writing formulas
 

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

" wrote:

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?

Macro writing formulas
 

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")


On Feb 4, 10:22*am, FSt1 wrote:
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



" wrote:
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 -

Macro writing formulas
 

$I$1 is not a R1C1 reference

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


wrote in message ...
| 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?

Macro writing formulas
 

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.

On Feb 4, 10:38*am, "Niek Otten" wrote:
$I$1 is not a R1C1 reference

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in ...

| 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?

Macro writing formulas
 

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.

wrote:

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")

On Feb 4, 10:22 am, FSt1 wrote:
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



" wrote:
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 -

--

Dave Peterson

Macro writing formulas
 

R1C9

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ...
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.

On Feb 4, 10:38 am, "Niek Otten" wrote:
$I$1 is not a R1C1 reference

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in ...

| 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?

Macro writing formulas
 

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

On Feb 4, 10:51*am, "Niek Otten" wrote:
R1C9

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in ...

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.

On Feb 4, 10:38 am, "Niek Otten" wrote:



$I$1 is not a R1C1 reference

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in ...

| 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 -