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

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

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.

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)

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)

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

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

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