Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Arguments
Hi
I have a routine that calls a function to determine the last row in a range. I need the function to return the last row number to the calling routine which will place the number in the active cell. Hope someone can help. Sub LastRow() FindLastRow ("A") ActiveCell.Formula = ??????? End Sub Public Function FindLastRow(cell) As String Dim LastRow LastRow = Range(cell & Rows.Count).End(xlUp).Row End Function Many Thanks - Grant |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Arguments
Sub LastRow()
val = FindLastRow ("A") ActiveCell.Formula = val End Sub Public Function FindLastRow(cell) As String Dim LastRow LastRow = Range(cell & Rows.Count).End(xlUp).Row FindLastRow = LastRow End Function -- Regards, Tom Ogilvy "Grant Reid" wrote in message ... Hi I have a routine that calls a function to determine the last row in a range. I need the function to return the last row number to the calling routine which will place the number in the active cell. Hope someone can help. Sub LastRow() FindLastRow ("A") ActiveCell.Formula = ??????? End Sub Public Function FindLastRow(cell) As String Dim LastRow LastRow = Range(cell & Rows.Count).End(xlUp).Row End Function Many Thanks - Grant |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Arguments
can't use val as a variable - my typo - i meant it to be vVal
Sub LastRow() Dim vVal as Long vVal = FindLastRow ("A") ActiveCell.Formula = vVal End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub LastRow() val = FindLastRow ("A") ActiveCell.Formula = val End Sub Public Function FindLastRow(cell) As String Dim LastRow LastRow = Range(cell & Rows.Count).End(xlUp).Row FindLastRow = LastRow End Function -- Regards, Tom Ogilvy "Grant Reid" wrote in message ... Hi I have a routine that calls a function to determine the last row in a range. I need the function to return the last row number to the calling routine which will place the number in the active cell. Hope someone can help. Sub LastRow() FindLastRow ("A") ActiveCell.Formula = ??????? End Sub Public Function FindLastRow(cell) As String Dim LastRow LastRow = Range(cell & Rows.Count).End(xlUp).Row End Function Many Thanks - Grant |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Arguments
Grant,
Sub LastRow() ActiveCell.Formula = FindLastRow("A") End Sub Public Function FindLastRow(ColLetter As String) As Long FindLastRow = Range(ColLetter & Rows.Count).End(xlUp).Row End Function HTH, Bernie MS Excel MVP "Grant Reid" wrote in message ... Hi I have a routine that calls a function to determine the last row in a range. I need the function to return the last row number to the calling routine which will place the number in the active cell. Hope someone can help. Sub LastRow() FindLastRow ("A") ActiveCell.Formula = ??????? End Sub Public Function FindLastRow(cell) As String Dim LastRow LastRow = Range(cell & Rows.Count).End(xlUp).Row End Function Many Thanks - Grant |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Arguments
Hi
To all who responded, many thanks for the help. Much appreciated. Kind Regards - Grant |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Arguments
Hi
As a variation on my previous question. Assume that I have button on Sheet1. I want to attach the LastRow macro to this button. What I'm attempting to establish is to find the last row in a range but on a different sheet, so I'll need to pass both the column letter and the sheet name. How can the following code be modified to accomplish this? Sub LastRow() Dim val As Integer val = FindLastRow("H") ActiveCell.Formula = val End Sub Public Function FindLastRow(cell) As String Dim LastRow LastRow = Range(cell & Rows.Count).End(xlUp).Row FindLastRow = LastRow End Function Many Thanks - Grant |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Arguments
Grant,
Like this: Sub LastRow() ActiveCell.Formula = FindLastRow("H","Sheet1") End Sub Public Function FindLastRow(cell As String, mySh As String) As String FindLastRow = Worksheets(mySh).Range(cell & Rows.Count).End(xlUp).Row End Function HTH, Bernie MS Excel MVP "Grant Reid" wrote in message ... Hi As a variation on my previous question. Assume that I have button on Sheet1. I want to attach the LastRow macro to this button. What I'm attempting to establish is to find the last row in a range but on a different sheet, so I'll need to pass both the column letter and the sheet name. How can the following code be modified to accomplish this? Sub LastRow() Dim val As Integer val = FindLastRow("H") ActiveCell.Formula = val End Sub Public Function FindLastRow(cell) As String Dim LastRow LastRow = Range(cell & Rows.Count).End(xlUp).Row FindLastRow = LastRow End Function Many Thanks - Grant |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Arguments
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing Arguments in Excell | Excel Worksheet Functions | |||
Passing Arguments in Excell function | Excel Discussion (Misc queries) | |||
Passing Variable Number of Arguments to a Sub | Excel Discussion (Misc queries) | |||
Passing arguments to a sub routine... | Excel Programming | |||
Passing ARGUMENTS between event procedures of a USERFORM | Excel Programming |