Solved: Row reference through selection in custom Function

[FhM]

** Title should be column reference through selection in custom Function**

I have a custom function which references data in a column on a different sheet. At the moment I refer to the column number by it's number and not it's alpha numeric reference eg A:A, B:B etc. I find myself updating the the source data all the time so all the column numbers shift and I have to redo the whole summary sheet again.
Is there a standard way to covert the A:A reference to a column number so I dont have to change the whole sheet every time I add/remove a column? Also how can I handle a reference to a cell etc in another sheet eg Sheet1!X110 .

I think the range function may do this but I dont know if it fits in with the way I am working things out at the moment:

[vba]For i = 2 To LastRow ' Skip the header Row
If .Cells(i, intFilterCol).Value = strFilterText Then
If Not IsEmpty(.Cells(i, intLevelCol)) And Not IsEmpty(.Cells(i, intTargetCol)) Then
colSum = colSum + LevelToPoints(.Cells(i, intLevelCol).Value) - LevelToPoints(.Cells(i, intTargetCol).Value)

numPupils = numPupils + 1
End If
End If

Next i[/vba]

I just want the intFilterCol and intLevelCol to be populated in the functions parameters from my column reference, from what I clicked on to populate the formula in the result cell.

Any help greatly appreciated. This is for Office 2003.

[Bob Phillips]

Just use the numbers, the Cells property can accept letter or number column references. But I am not clear how that will help with inserted/deleted columns.

[FhM]

Lets take an easy example just using cells and SUM.

I have column A with values in 3 rows in column B I want the total so

[vba]=SUM(A1,A2,A3)[/vba]

If I was to add a column before column A the excel would change the formula to

[vba]=SUM(B1,B2,B3)[/vba]

So I want to take a column reference and and convert it to it's number or some other method. Basically I want the function parameters to update their column numbers when I add/remove colums to the worksheet with the data on it like the example above. At the moment they do not change as they are hard coded pretty much. So I want to know how to handle this eg translate absolute references etc to work with my code.


So I should be able to do?

[vba]LevelToPoints(.Cells(i, "B:B").Value[/vba]

or were you thinking of something else?

[mdmackillop]

As you have a header column, search the headers to detemine the column numbers
[vba]
Dim c As Range, Col As Long
Set c = Sheets(2).Rows(1).Find("Data1")
If Not c Is Nothing Then Col = c.Column
MsgBox Col

[/vba]

You could also use Range Names

[Bob Phillips]

Use a defined name, then this will update when you add/delete columns.

You access it in VBA like so

[vba]

LevelToPoints(.Cells(i, .Range("dataColumn").Column).Value
[/vba]

[FhM]

Thank you both for your answers. I will definately use one of the above methods to make my life a little easier. I wasn't aware of defined names but as they say "you learn something new every day" and that's what keeps the job interesting.

[SamT]

[vba]
Function ColumnCharacters(ByVal Column_Number As Long) As String
'
'Since the Method "Cell.Column" converts a base26 counting system to a base10 number
' system, this procedure reconverts the base10 system back to a base26 system,
' using the facts that the Sqrt of Base^n is Base^(n-1) and Z+1 = AA.

' The Largest Column_Number this will handle is 999,999,999, or Column CFDGSXK.
' Excel can perform math up to 99,999,999,999,which would be Column LKRMVRBC.
' Weird.

'Column Heads are not a base 26 system, rather they are an integer based counting _
system where the integers are represented by glyphs made up of alpha-characters. _
i.e. 26 base 10 = 10 base 26. In the column head counting system, the value 26 is _
represented by the glyph Z. The number 1 greater than Z is the glyph AA, and one _
greater than AZ is BA, Therefore, ZZ + 1 = AAA.
'When the placeholder is Z, Incrementing by one means Incrementing both that placeholder _
and the next left placeholder by one.

'In a Base26 Numbering system using alpha digits, the digit "A" = 26^n, and the value of Z
' is held by the placeholder "0". AZ (counting) = B0 (base26 numbering) = 52 (base10).
' In any numbering system, the value of any particular digit can be represented by
' counting its position from the right, subtracting 1, and raising the Base to that value,
' then multiplying the result by the digits face value. Ex: 2000 base10 = 2 x 10^3 where
' "2" is in the 4th from right position. The Sqrt of Base^n is Base^(n-1) except where n=1.

Dim n As Long
Dim MyValue As Long
Dim MyCharacters As String
Dim MyFlag As Boolean
Dim MyNumber As Long
MyNumber = Column_Number
If MyNumber = 1 Then GoTo Special_Case
Dim Glyph_In_Position(1 To 8) As String
Dim MyGlyphs(1 To 26) As String
Dim Glyph_Index As Long
MyGlyphs(1) = "A"
MyGlyphs(2) = "B"
MyGlyphs(3) = "C"
MyGlyphs(4) = "D"
MyGlyphs(5) = "E"
MyGlyphs(6) = "F"
MyGlyphs(7) = "G"
MyGlyphs(8) = "H"
MyGlyphs(9) = "I"
MyGlyphs(10) = "J"
MyGlyphs(11) = "K"
MyGlyphs(12) = "L"
MyGlyphs(13) = "M"
MyGlyphs(14) = "N"
MyGlyphs(15) = "O"
MyGlyphs(16) = "P"
MyGlyphs(17) = "Q"
MyGlyphs(18) = "R"
MyGlyphs(19) = "S"
MyGlyphs(20) = "T"
MyGlyphs(21) = "U"
MyGlyphs(22) = "V"
MyGlyphs(23) = "W"
MyGlyphs(24) = "X"
MyGlyphs(25) = "Y"
MyGlyphs(26) = "Z"

Dim Factor_Value(1 To 8) As Long
Factor_Value(1) = 7
Factor_Value(2) = 6
Factor_Value(3) = 5
Factor_Value(4) = 4
Factor_Value(5) = 3
Factor_Value(6) = 2
Factor_Value(7) = 1
Factor_Value(8) = 0

Dim This_n As Long
For n = 1 To 8
If MyNumber > (26 ^ Factor_Value(n)) Then '(26 ^ Factor_Value(n)+1) = minimum count for Position n
MyFlag = True
Glyph_Index = MyNumber \ (26 ^ Factor_Value(n)) 'same as dividing MyNumber by Sqrt of (Minimum value - 1)
If Glyph_Index > 26 Then ' (26 ^ Factor_Value(n)) = maximum count of next right Position in String
Glyph_In_Position(n) = "A" ' Counting to (MaxCount ^ 2 + 1) adds a Position to the left.
Else
Glyph_In_Position(n) = MyGlyphs(Glyph_Index)
End If
MyNumber = MyNumber - (26 ^ Factor_Value(n)) * Glyph_Index 'Glyph_Index is also the number of times the value "A"
ElseIf MyFlag Then ' Has been counted to.
Glyph_In_Position(n) = "A" 'There are no zeros, we're counting by glyph Integers
End If
Next n
For n = 1 To 8 'Ignore empty leading positions
If Not Glyph_In_Position(n) = "" Then ColumnCharacters _
= ColumnCharacters & Glyph_In_Position(n)
Next n
GoTo Done
Special_Case:
If MyNumber = 1 Then ColumnCharacters = "A"
Done:
End Function
[/vba]

[Bob Phillips]

[vba]

Function ColumnLetter(Col As Long)
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function
[/vba]

[SamT]

XLD,

Thank you,sir.

SamT