| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
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... |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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 |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
pzisson
Newbie
Joined: 23 Dec 2005
Posts: 3
Location: Cordova, TN
|
| Posted: Mon Dec 26, 2005 10:02 am Post subject: |
|
|
Many thanks to all who answered. Villeroy had the answer I was looking for.
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 |
|
| Back to top |
|
|
|
|
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
|
Powered by phpBB © 2001, 2005 phpBB Group
|
