Using INDIRECT Function in VBA

[eoinymc]

Hi,

I have the following function in my workbook:

=INDIRECT("'Sheet 1'!J"&$N$1)

Cell N1 has the number of the row that the data is on that I want to display.

My issue is that the Column "J" is dynamic on a monthly basis.

So, I was trying to do something like this in VBA ( I have a lot of code that splits data and does a lot of calculations and data manipulation, so this would just be an extension of the code):

CurrMth = 10

Ind.Range("H3").FormulaR1C1 = "=INDIRECT("'Sheet 1'!" & [B]CurrMth [/B]& "&$N$1)"

But it obviously won't work..

Any ideas on how I can make this indirect function dynamic?

Cheers,

Eoin

 

[afndst]

Hi,

I have the following function in my workbook:

=INDIRECT("'Sheet 1'!J"&$N$1)

Cell N1 has the number of the row that the data is on that I want to display.

My issue is that the Column "J" is dynamic on a monthly basis.

So, I was trying to do something like this in VBA ( I have a lot of code that splits data and does a lot of calculations and data manipulation, so this would just be an extension of the code):

CurrMth = 10

Ind.Range("H3").FormulaR1C1 = "=INDIRECT("'Sheet 1'!" & [B]CurrMth [/B]& "&$N$1)"

But it obviously won't work..

Any ideas on how I can make this indirect function dynamic?

Cheers,

Eoin

I think the indirect function cannot be used in VBA. When you type Worksheetfuntion. it does not appear in the list.

You can use a mixed method:

1. Use the work sheet to define dinamically the range

InCell M15:

=ADDRESS(ROW(),3) &":"&ADDRESS(ROW()+$N$4-1,3+$N$4-1)

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

2. Get the address in VBA

MCorr = Range("Aux!M15")

Selection.FormulaArray = _

"=MCholesky(" & MCorr & ")"

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>