My formula, including an UDF to convert a Column number to a Column Name works when placed on the sheet but returns #VALUE! error when named ("Test")in the Name Manager
in cell AO205, Test=INDIRECT(ConvertToLetter(COLUMN(Build!AO204))&Build!$AH205)
=Test returns #VALUE!
=INDIRECT(ConvertToLetter(COLUMN(Build!AO204))&Build!$AH205) returns the text value in cell AO154 because Build!$AH205 holds the value 154
Owing to the size of the application, the formulas are stored in the name manager and changed to values only using VBA which is limited to 255 characters in any formula applied.
I know this is being caused by the Column Function being unable to function from the Name Manager but I am looking for a work around.
Any ideas welcome?
Brendan
Code follows
UDF
Public 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
in cell AO205, Test=INDIRECT(ConvertToLetter(COLUMN(Build!AO204))&Build!$AH205)
=Test returns #VALUE!
=INDIRECT(ConvertToLetter(COLUMN(Build!AO204))&Build!$AH205) returns the text value in cell AO154 because Build!$AH205 holds the value 154
Owing to the size of the application, the formulas are stored in the name manager and changed to values only using VBA which is limited to 255 characters in any formula applied.
I know this is being caused by the Column Function being unable to function from the Name Manager but I am looking for a work around.
Any ideas welcome?
Brendan
Code follows
UDF
Public 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