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