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

 

[Momentman]

This function from Microsoft converts numbers into column names e.g 1 to A, 8 to H e.t.c

I obtained the code from How to convert Excel column numbers into alphabetical characters

Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

You can incorporate it to get the column alphabet which can then be passed into the Indirect function e.g

 ConvertToLetter(currmonth)

will give J when currmonth is 10

 

[eoinymc]

Hi Momentman,

thanks for that, but even if I have a letter in there, it doesn't work..

[FONT=monospace]CurrMth = "J"

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

This doesn't work either...

Any other ideas?

Eoin

 

[Momentman]

Nope,not like that

Set currmth = 10

then in your indirect formula, use ConvertToLetter(currmth)

Ind.Range("H3").FormulaR1C1 = "=INDIRECT("'Sheet 1'!" & ConvertToLetter(CurrMth) & "&$N$1)"

 

[eoinymc]

Ok, so when I do this in VBA:

Ind.Range("H28").FormulaR1C1 = "=INDIRECT(""'Sheet 1'!"" & ConvertToLetter(CurrMthCell) &R1C14)"

I get this output in my work sheet:

=INDIRECT("'C-S3'!" & ConvertToLetter(CurrMthCell) &$N$1)

Any idea why it's not converting the ConvertToLetter(CurrMthCell) part to J?

Thanks,

Eoin

 

[ukmikeb]

Hi

Try :-

Ind.Range("H28").FormulaR1C1 = "=INDIRECT(" & Chr(34) & "'Sheet1'!" & Mid(Cells(1, CurrMth).Address, 2, 1) & Chr(34) & Chr(38) & "$N$1)"

hth

 

[eoinymc]

Thanks Mike..

that works a treat...after I changed $N$1 to R1C14

Why does using the mid and .address functions make a difference?

Cheers,

Eoin

 

[Rick Rothstein]

This function from Microsoft converts numbers into column names e.g 1 to A, 8 to H e.t.c

Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

Here is a much more compact function that you can use in the future...

Function ConvertToLetter(iCol As Integer) As String
   ConvertToLetter = Split(Cells(1, iCol).Address, "$")(1)
End Function

 

[ukmikeb]

Hi Eoin

Thanks for the feedback.

Pleased to have helped solve your problem.

The purpose of Cells(1, CurrMth).Address is to return "$J$1" when CurrMth is 10. Then, Mid(Cells(1, CurrMth).Address, 2, 1) returns "J" being the Column letter.

Good luck with your project.

 

[danhansmoleman]

Let's say Sheet1!A1 has the text value "Sheet1!B1" and Sheet1!B1 has the value "1234". The following code will use the range address stored as text in A1 as an input and copy the range B1 to A2:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">Sub Tester()  

    Sheet1.Range(Range("A1")).Copy  

    Sheet1.Range("A2").PasteSpecial xlPasteAll

End Sub</code>