Find last populated cell in a row.

[RJG]

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

 

[Don Guillett]

one way is to look for a number larger than possible
=INDEX(D5:w5,MATCH(999999999,D5:w5,1))

 

[natanz]

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.

 

[Guest]

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

 

[Bob Phillips]

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)

 

[RJG]

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)

 

[Guest]

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()

 

[Bob Phillips]

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)