Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last populated cell in a row.
I need to keep a summary of our latest prices plus the previous price ,
so on sheet1 cell C5 is the previous price while cell D5 is the current price. I want these prices to update automatically whenever a price change occures. Each time a price change occures the new price is added to row 16, currently the last price is X16 while the previous price is W16. How do I get cell D5 to lookup the last item in row 16 and C5 to find the last but one item in row 16. With thanks Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last populated cell in a row.
one way is to look for a number larger than possible
=INDEX(D5:w5,MATCH(999999999,D5:w5,1)) -- Don Guillett SalesAid Software "RJG" wrote in message oups.com... I need to keep a summary of our latest prices plus the previous price , so on sheet1 cell C5 is the previous price while cell D5 is the current price. I want these prices to update automatically whenever a price change occures. Each time a price change occures the new price is added to row 16, currently the last price is X16 while the previous price is W16. How do I get cell D5 to lookup the last item in row 16 and C5 to find the last but one item in row 16. With thanks Bob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last populated cell in a row.
are you sure you can assume the latest price is the highest price.
i have used this code to select all the items in a row, starting at whatever cell "selection" refers to. I Range(Selection, Selection.End(xlDown)).Select i know this is not a complete answer, but i hope it points you in the right direction. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last populated cell in a row.
LastRow = CSVData.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
I use that code to find the last row of data in a column (column A in this case). It should be fairly easy to modify to find the last column of data in a row..... but I can't do it. The following is slightly different, but does work. Sub test() Dim lastcolumn As integer lastcolumn = ThisWorkbook.Sheets(1).Cells(16, Columns.Count).SpecialCells(xlCellTypeLastCell) MsgBox lastcolumn End Sub "RJG" wrote: I need to keep a summary of our latest prices plus the previous price , so on sheet1 cell C5 is the previous price while cell D5 is the current price. I want these prices to update automatically whenever a price change occures. Each time a price change occures the new price is added to row 16, currently the last price is X16 while the previous price is W16. How do I get cell D5 to lookup the last item in row 16 and C5 to find the last but one item in row 16. With thanks Bob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last populated cell in a row.
And the previous item is then
=INDEX(D5:w5,MATCH(999999999,D5:w5,1)-1) -- HTH RP (remove nothere from the email address if mailing direct) "Don Guillett" wrote in message ... one way is to look for a number larger than possible =INDEX(D5:w5,MATCH(999999999,D5:w5,1)) -- Don Guillett SalesAid Software "RJG" wrote in message oups.com... I need to keep a summary of our latest prices plus the previous price , so on sheet1 cell C5 is the previous price while cell D5 is the current price. I want these prices to update automatically whenever a price change occures. Each time a price change occures the new price is added to row 16, currently the last price is X16 while the previous price is W16. How do I get cell D5 to lookup the last item in row 16 and C5 to find the last but one item in row 16. With thanks Bob |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last populated cell in a row.
No, the last cell is not always the highest price. Am I not better
trying to find the first empty cell in the row and then coming in 1 (or2) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last populated cell in a row.
Enter the following UDFs:
Function FTZ1() As Double Dim i As Integer FTZ1 = 0 For i = 256 To 1 Step -1 If Cells(16, i).Value = "" Then Else FTZ1 = Cells(16, i).Value Exit For End If Next End Function -------------------------------------------------------------------------- Function FTZ2() As Double Dim i As Integer FTZ2 = 0 For i = 256 To 1 Step -1 If Cells(16, i).Value = "" Then Else FTZ2 = Cells(16, i - 1).Value Exit For End If Next End Function Then in D5 put =FTZ1() and in C5 put =FTZ2() -- Gary's Student "Marcotte A" wrote: LastRow = CSVData.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row I use that code to find the last row of data in a column (column A in this case). It should be fairly easy to modify to find the last column of data in a row..... but I can't do it. The following is slightly different, but does work. Sub test() Dim lastcolumn As integer lastcolumn = ThisWorkbook.Sheets(1).Cells(16, Columns.Count).SpecialCells(xlCellTypeLastCell) MsgBox lastcolumn End Sub "RJG" wrote: I need to keep a summary of our latest prices plus the previous price , so on sheet1 cell C5 is the previous price while cell D5 is the current price. I want these prices to update automatically whenever a price change occures. Each time a price change occures the new price is added to row 16, currently the last price is X16 while the previous price is W16. How do I get cell D5 to lookup the last item in row 16 and C5 to find the last but one item in row 16. With thanks Bob |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last populated cell in a row.
That does not find the highest price, but because it seeks a number that it
won't find, it returns an index into the last cell. -- HTH RP (remove nothere from the email address if mailing direct) "RJG" wrote in message oups.com... No, the last cell is not always the highest price. Am I not better trying to find the first empty cell in the row and then coming in 1 (or2) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I find last populated cell in a row (or column) | Excel Programming | |||
how can I find last populated cell in a row (or column) | Excel Programming | |||
how can I find last populated cell in a row (or column) | Excel Programming | |||
how can I find last populated cell in a row (or column) | Excel Programming | |||
how can I find last populated cell in a row (or column) | Excel Programming |