Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row in a particular sheet / workbook
I have a UDF to return the last row in a particular sheet:
Function Get_LastRow(Sheet As String) As Long Application.Volatile True Get_LastRow = Sheets(Sheet).UsedRange.Rows.Count End Function Now here is the problem: * This UDF is stored in a module in workbook WB1. * WB1 Sheet1 has 10 rows. WB1 Sheet2 has 9 rows. I call the UDF from WB1 Sheet3 twice to return the last rows of WB1 Sheet1 (10) and WB1 Sheet2 (9). * At the same time I have workbook WB2 open. WB2 Sheet1 has 6 rows, WB2 Sheet2 has 3 rows. WB2 uses some results from WB1. WB2 has the focus - it is displayed on top. * When I do a Full Recalculate (F9) with WB2 on top, then switch to WB1 I find that the UDF has put 6 and 3 in WB1. I have tried renaming Sheet1 and Sheet2 in WB2 to something else, then the recalculate results in #VALUE in WB1. (When I recalculate with WB1 on top I receive the expected results 10 and 9.) How can I get the last row of the sheet from the workbook that calls the UDF? Thanks for your help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row in a particular sheet / workbook
I assume from you example the the Sheet is the SheetName. You need to pas
something like this "[book1.xls]Sheet1" You could do this set WB1 = workbooks("Book1.xls") LastRow = Get_LastRow("[" & WB1.name & "]" & "Sheet1") or set WB1 = workbooks("Book1.xls") set sht = WB1.sheets("Sheet1") LastRow = Get_LastRow("[" sht.parent.name & "]" & sht.name) "Michael R" wrote: I have a UDF to return the last row in a particular sheet: Function Get_LastRow(Sheet As String) As Long Application.Volatile True Get_LastRow = Sheets(Sheet).UsedRange.Rows.Count End Function Now here is the problem: * This UDF is stored in a module in workbook WB1. * WB1 Sheet1 has 10 rows. WB1 Sheet2 has 9 rows. I call the UDF from WB1 Sheet3 twice to return the last rows of WB1 Sheet1 (10) and WB1 Sheet2 (9). * At the same time I have workbook WB2 open. WB2 Sheet1 has 6 rows, WB2 Sheet2 has 3 rows. WB2 uses some results from WB1. WB2 has the focus - it is displayed on top. * When I do a Full Recalculate (F9) with WB2 on top, then switch to WB1 I find that the UDF has put 6 and 3 in WB1. I have tried renaming Sheet1 and Sheet2 in WB2 to something else, then the recalculate results in #VALUE in WB1. (When I recalculate with WB1 on top I receive the expected results 10 and 9.) How can I get the last row of the sheet from the workbook that calls the UDF? Thanks for your help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row in a particular sheet / workbook
Joel,
Thanks for your suggestions. 2 points though: 1) The UDF is called directly from a cell on WB1 Sheet3, Thus I suspect that we would need to determine the workbook name out there. =Cell("filename") does not work because it returns the name of the active workbook which in my example is WB2. 2) I rather not hardcode the workbook names "Joel" wrote: I assume from you example the the Sheet is the SheetName. You need to pas something like this "[book1.xls]Sheet1" You could do this set WB1 = workbooks("Book1.xls") LastRow = Get_LastRow("[" & WB1.name & "]" & "Sheet1") or set WB1 = workbooks("Book1.xls") set sht = WB1.sheets("Sheet1") LastRow = Get_LastRow("[" sht.parent.name & "]" & sht.name) "Michael R" wrote: I have a UDF to return the last row in a particular sheet: Function Get_LastRow(Sheet As String) As Long Application.Volatile True Get_LastRow = Sheets(Sheet).UsedRange.Rows.Count End Function Now here is the problem: * This UDF is stored in a module in workbook WB1. * WB1 Sheet1 has 10 rows. WB1 Sheet2 has 9 rows. I call the UDF from WB1 Sheet3 twice to return the last rows of WB1 Sheet1 (10) and WB1 Sheet2 (9). * At the same time I have workbook WB2 open. WB2 Sheet1 has 6 rows, WB2 Sheet2 has 3 rows. WB2 uses some results from WB1. WB2 has the focus - it is displayed on top. * When I do a Full Recalculate (F9) with WB2 on top, then switch to WB1 I find that the UDF has put 6 and 3 in WB1. I have tried renaming Sheet1 and Sheet2 in WB2 to something else, then the recalculate results in #VALUE in WB1. (When I recalculate with WB1 on top I receive the expected results 10 and 9.) How can I get the last row of the sheet from the workbook that calls the UDF? Thanks for your help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row in a particular sheet / workbook
In VBA help see : Caller Property
If you are passing a parameter as a range object then you have to the infor you need function Myfunction(target as range) MyAddress = target.address(external:=true) end function Or the parent of the range is the sheet and the sheet parent is the workbook set sht = target.parent shtName = sht.name set bk = sht.name bkname = bk.name end function "Michael R" wrote: Joel, Thanks for your suggestions. 2 points though: 1) The UDF is called directly from a cell on WB1 Sheet3, Thus I suspect that we would need to determine the workbook name out there. =Cell("filename") does not work because it returns the name of the active workbook which in my example is WB2. 2) I rather not hardcode the workbook names "Joel" wrote: I assume from you example the the Sheet is the SheetName. You need to pas something like this "[book1.xls]Sheet1" You could do this set WB1 = workbooks("Book1.xls") LastRow = Get_LastRow("[" & WB1.name & "]" & "Sheet1") or set WB1 = workbooks("Book1.xls") set sht = WB1.sheets("Sheet1") LastRow = Get_LastRow("[" sht.parent.name & "]" & sht.name) "Michael R" wrote: I have a UDF to return the last row in a particular sheet: Function Get_LastRow(Sheet As String) As Long Application.Volatile True Get_LastRow = Sheets(Sheet).UsedRange.Rows.Count End Function Now here is the problem: * This UDF is stored in a module in workbook WB1. * WB1 Sheet1 has 10 rows. WB1 Sheet2 has 9 rows. I call the UDF from WB1 Sheet3 twice to return the last rows of WB1 Sheet1 (10) and WB1 Sheet2 (9). * At the same time I have workbook WB2 open. WB2 Sheet1 has 6 rows, WB2 Sheet2 has 3 rows. WB2 uses some results from WB1. WB2 has the focus - it is displayed on top. * When I do a Full Recalculate (F9) with WB2 on top, then switch to WB1 I find that the UDF has put 6 and 3 in WB1. I have tried renaming Sheet1 and Sheet2 in WB2 to something else, then the recalculate results in #VALUE in WB1. (When I recalculate with WB1 on top I receive the expected results 10 and 9.) How can I get the last row of the sheet from the workbook that calls the UDF? Thanks for your help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row in a particular sheet / workbook
Thanks a lot, Joel,
That works perfectly. "Joel" wrote: In VBA help see : Caller Property If you are passing a parameter as a range object then you have to the infor you need function Myfunction(target as range) MyAddress = target.address(external:=true) end function Or the parent of the range is the sheet and the sheet parent is the workbook set sht = target.parent shtName = sht.name set bk = sht.name bkname = bk.name end function "Michael R" wrote: Joel, Thanks for your suggestions. 2 points though: 1) The UDF is called directly from a cell on WB1 Sheet3, Thus I suspect that we would need to determine the workbook name out there. =Cell("filename") does not work because it returns the name of the active workbook which in my example is WB2. 2) I rather not hardcode the workbook names "Joel" wrote: I assume from you example the the Sheet is the SheetName. You need to pas something like this "[book1.xls]Sheet1" You could do this set WB1 = workbooks("Book1.xls") LastRow = Get_LastRow("[" & WB1.name & "]" & "Sheet1") or set WB1 = workbooks("Book1.xls") set sht = WB1.sheets("Sheet1") LastRow = Get_LastRow("[" sht.parent.name & "]" & sht.name) "Michael R" wrote: I have a UDF to return the last row in a particular sheet: Function Get_LastRow(Sheet As String) As Long Application.Volatile True Get_LastRow = Sheets(Sheet).UsedRange.Rows.Count End Function Now here is the problem: * This UDF is stored in a module in workbook WB1. * WB1 Sheet1 has 10 rows. WB1 Sheet2 has 9 rows. I call the UDF from WB1 Sheet3 twice to return the last rows of WB1 Sheet1 (10) and WB1 Sheet2 (9). * At the same time I have workbook WB2 open. WB2 Sheet1 has 6 rows, WB2 Sheet2 has 3 rows. WB2 uses some results from WB1. WB2 has the focus - it is displayed on top. * When I do a Full Recalculate (F9) with WB2 on top, then switch to WB1 I find that the UDF has put 6 and 3 in WB1. I have tried renaming Sheet1 and Sheet2 in WB2 to something else, then the recalculate results in #VALUE in WB1. (When I recalculate with WB1 on top I receive the expected results 10 and 9.) How can I get the last row of the sheet from the workbook that calls the UDF? Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merge 7 files to 1 workbook/7Sheets and then 1 sheet of the new workbook | Excel Programming | |||
Help needed: Getting all sheet setup data from one workbook toanother workbook | Excel Programming | |||
Copy rows from multiple workbook into a different workbook (sheet) | Excel Programming | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions |