Passing Arguments
Grant,
Assuming you want the column letters, like "AA":
Sub LastColumn()
Dim strLastCol As String
strLastCol = FindLastCol(10, "Sheet 1")
ActiveCell.Formula = strLastCol
End Sub
Public Function FindLastCol(myR As Long, mySh As String) As String
FindLastCol = ColLet(Worksheets(mySh).Cells(myR,
256).End(xlToLeft).Column)
End Function
Function ColLet(ColNum As Integer) As String
If ColNum 26 Then ColLet = Chr((ColNum - 1) \ 26 + 64)
ColLet = ColLet & Chr(((ColNum - 1) Mod 26) + 65)
End Function
--
HTH,
Bernie
MS Excel MVP
"Grant Reid" wrote in message
...
Hi
I need to apply a similar methodology to determine the last column in a
given worksheet. Once again, assume that I have button on Sheet1.
I want to attach the LastColumn macro to this button. What I'm attempting
to
establish is to find the last column in a range but on a different sheet,
so
I'll need to pass both the starting cell address and the sheet name eg;
Sub LastColumn
Dim strLastCol As String
strLastCol = FindLastCol("B10", "Sheet 1")
ActiveCell.Formula = strLastCol
End Sub
Its the called function FindLastCol, that I'm battling with. Can anyone
help
fill in the blanks?
Public Function FindLastCol(??????????, mySh As String) As String
FindLastCol = Worksheets(mySh).??????????????
End Function
Many Thanks - Grant
|