[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
pzisson
Newbie

Joined: 23 Dec 2005
Posts: 3
Location: Cordova, TN

 Posted: Fri Dec 23, 2005 9:50 pm    Post subject: Finding last row number in a column of entries Given that I have a set of columns with Cols A,B, and C manually entered, and Col D a function that acts on A, B, and C, how do I determine the last row to have an entry in Col A and extract the value in D for that row? I have no way of knowing how many rows might exist in the sheet - I just need to grab the value in Dx where x is the last row for which an entry of any kind exists in A (Ax<>""). _________________Peter Cordova, TN GPG Public Key: AF616B33
gpl_racer
Power User

Joined: 08 Sep 2005
Posts: 83
Location: Finland

Posted: Sat Dec 24, 2005 1:14 am    Post subject:

I donīt know if that is possible without using a macro... So I created one:

 Code: Sub LastEntry Dim oDocument as object, oSheet as object, oCell as object Dim columnA as integer, rowA as integer, columnD as integer, columnDValue as integer oDocument = ThisComponent oSheet = oDocument.Sheets.getByIndex(0) 'select the sheet by changing this number (0 = sheet1, 1 = sheet2, 2 = sheet3, ...) rowA = 0 'select the starting row (0 = row1, 1 = row2, 2 = row3, ...) columnA = 0 'select the column (0 = column A, 1 = column B, 2 = column C, ...) columnD = 3 'select the column where the function is (like above) oCell = oSheet.getCellByPosition(columnA,rowA) while not (oCell.getString = "") rowA = rowA + 1 oCell = oSheet.getCellByPosition(columnA,rowA) wend oCell = oSheet.getCellByPosition(columnD,rowA-1) columnDValue = oCell.getValue() msgbox ("Value of D" + rowA + " is " + columnDValue) 'if you donīt want this macro to give the number in a messagebox, put "REM" in front of this line REM oCell = oSheet.getCellByPosition(0,5)    'if you want the value to be written to a cell, delete the "REM"s in front of these two lines REM oCell.setValue(columnDValue)         'the cell is selected with the two numbers in parentheses, first number being the column and second number being the row end sub

You can get it to work with Tools -> Macros -> Organize macros... Just paste the above code to the macro editor, save, and then run the macro...
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
Location: Germany

 Posted: Sat Dec 24, 2005 3:12 am    Post subject: gpl_racer's macro stops searching at first empty cell in col \$A. Following formulas depend on col A having no gaps. If B:B is a column without gaps, use B in the CountA-function. =INDEX(\$D\$1:\$D\$32000;COUNTA(\$A\$1:\$A\$32000)) Where 32000 is the max. row count in OOo1. The same: =OFFSET(\$D\$1;COUNTA(\$A\$1:\$A\$32000)) Get a reference to the whole column: =OFFSET(\$D\$1;0;0;COUNTA(\$A\$1:\$A\$32000);1) May be this is another solution: A reference to the cells above *this* cell(XY32000) in column D: =\$D\$1:\$D31999
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
Location: Germany

 Posted: Sun Dec 25, 2005 4:35 pm    Post subject: Just posted some starbasic-code: http://www.oooforum.org/forum/viewtopic.phtml?p=115702#115702 I tried my best to get used cell ranges from some (multiple) range. Can be used with whole columns or rows or even multiple selections.
pzisson
Newbie

Joined: 23 Dec 2005
Posts: 3
Location: Cordova, TN

Posted: Mon Dec 26, 2005 10:02 am    Post subject:

The sheets are Jan - Dec. I wanted to take the last entry from Col G in Jan and copy it into the top of Col G in Feb (and so on through the year), but I didn't know in advance what line would be last in Jan. Since Col A always has an entry for each active line my entry for Feb.G3 is:
 Code: =INDEX(Jan.G\$3:G\$200;COUNTA(Jan.\$A\$3:\$A\$200))

Note that I did not lock the sheet name or Col G because I needed to copy the formula into Col H and then copy the sheet to each subsequent month.

It's all so simple when you have the answer, but I was unfamiliar with the INDEX and COUNTA functions before this. Thanks again.
_________________
Peter
Cordova, TN

GPG Public Key: AF616B33
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum