Newbie - Finding maximum in a variable range

Hi guys,

I am a newbie in excel and vba. Apology in advance if the question look so stupid. I have read through some of the post but I could not find the answer I want.

I am now working on an excel spreadsheet which will automatically update every day so that one row will be added on the top of the sheet everyday.

i.e.

A1 Date Price

A2 (New row added,date) (New row added,price)

A3 16/1/2013 5.5

A4 15/1/2013 5.6

A5 14/1/2013 5.45

... ...

Assuming that I am looking for maximum value of the column "Price", since max() cannot be applied to variable range, is there other method I could useto look for the most updated maximum value of column "price"?

Thank you

Re: Newbie - Finding maximum in a variable range

Would someone please help? Pretty urgent

Re: Newbie - Finding maximum in a variable range

Assuming Price Column start at Column B --> =LOOKUP(90000000000,B:B) Input The Formula at Colum A or C, and don't input at Column B

Another Formula to retrieve the last occurence :

=VLOOKUP(90000000000,B:B,1)

or

=LOOKUP(2,1/(B:B<>""),B:B)

Just try it at your case

Regards

Azumi

Re: Newbie - Finding maximum in a variable range

The formula is for numeric only bro....

Re: Newbie - Finding maximum in a variable range

Quote:

Originally Posted by

**azumi**
Assuming Price Column start at Column B --> =LOOKUP(90000000000,B:B) Input The Formula at Colum A or C, and don't input at Column B

Another Formula to retrieve the last occurence :

=VLOOKUP(90000000000,B:B,1)

or

=LOOKUP(2,1/(B:B<>""),B:B)

Just try it at your case

Regards

Azumi

Thanks for your reply Azumi.

Correct me if I am wrong. Formulas =VLOOKUP(90000000000,B:B,1) or =LOOKUP(2,1/(B:B<>""),B:B) will only recall the new data created everyday right?

Maybe let me clarify a bit on my question. I am looking for Maximum value of column B, which originally achieved by Max(B1:B100) function. However, New data will be inserted to B1 everyday such that original B1 will become B2 on next day, so as the other cell Bx becoming B(x+1) next day.

This means B1 itself is a variable (I am not sure if I am correct to describe it in this way). Max() function cannot be applied.

So i am looking for any way to determine the maximum value of column B, which is ever-changing day after day.

Re: Newbie - Finding maximum in a variable range

Quote:

Originally Posted by

**tcheung**
Thanks for your reply Azumi.

Correct me if I am wrong. Formulas =VLOOKUP(90000000000,B:B,1) or =LOOKUP(2,1/(B:B<>""),B:B) will only recall the new data created everyday right?

Maybe let me clarify a bit on my question. I am looking for Maximum value of column B, which originally achieved by Max(B1:B100) function. However, New data will be inserted to B1 everyday such that original B1 will become B2 on next day, so as the other cell Bx becoming B(x+1) next day.

This means B1 itself is a variable (I am not sure if I am correct to describe it in this way). Max() function cannot be applied.

So i am looking for any way to determine the maximum value of column B, which is ever-changing day after day.

Maybe make it simpler:

How to find a maximum value in a column(let say "B") which contains variable (in this case, new data will be added to B1 everyday and pushes all the data down by 1 row), i.e. a replacement which works as worksheetfunction MAX(....) but work for range with variables

Note: the data adding function is determined by a terminal connected to the computer, and thus cannot be changed.

Re: Newbie - Finding maximum in a variable range

Quote:

Originally Posted by

**tcheung**
hi guys,

i am a newbie in excel and vba. Apology in advance if the question look so stupid. I have read through some of the post but i could not find the answer i want.

I am now working on an excel spreadsheet which will automatically update every day so that one row will be added on the top of the sheet everyday.

I.e.

A1 date price

a2 (new row added,date) (new row added,price)

a3 16/1/2013 5.5

a4 15/1/2013 5.6

a5 14/1/2013 5.45

... ...

Assuming that i am looking for maximum value of the column "price", since max() cannot be applied to variable range, is there other method i could useto look for the most updated maximum value of column "price"?

Thank you

=MAX(B:B)

should suffice.

Re: Newbie - Finding maximum in a variable range

Hi

Try this solution :

Range("A1").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlToRight)).Select

This code selects cell A1, then goes to the last row of column A, then all the way right to the last column

You can determine the Row and column of the active cell as follows:

Dim MaxRow,MaxColumn As Double

ActiveCell.Row = MaxRow

ActiveCell.Column = MaxColumn

Kind Regards

Gerry

Re: Newbie - Finding maximum in a variable range

Ok my bad, i'm misunderstood with your expecting results, the solution is easy bro, =MAX(B2:B1000) , then u can add up the new row for data until u reach the 1000th row

Re: Newbie - Finding maximum in a variable range

Quote:

Originally Posted by

**azumi**
Ok my bad, i'm misunderstood with your expecting results, the solution is easy bro, =MAX(B2:B1000) , then u can add up the new row for data until u reach the 1000th row

OK Good luck with it. Should you have more than 1000 rows, you could use my solution.

Greetings

G.