i have 2 list of numbers in excel

Year and Profit
1 1000
2 2000
3 5000
4 8000
5 9000

here's my line of code in VBA

i'm wondering why this part is not working, i just want to show the year when max profit occured

Calling Worksheetfunction.Max() does not change the ActiveCell, it simply returns the highest value in an array. Before suggesting an alternative approach, please can you tell us what you would want the message to be if the highest profit corresponds to more than one year? eg

1 1000
2 9000
3 9000
4 8000
5 9000

The first parameter in Offset is RowOffset, the second is ColOffset. Also, to offset to the left, use a negative integer. To offset to the right, use a positive integer.

I think you need:

ActiveCell.Offset(0,-1).Value

This assumes that your years are in the column to the left of your profits.

Raysadude

The code you need is:

Sub Test2()
Dim varMax

Sheets("sheet1").Select
Range("B1").Select

varMax = Application.WorksheetFunction.Max(Range("B1:B5"))

Do Until ActiveCell.Value = varMax
ActiveCell.Offset(1, 0).Select
Loop

MsgBox "The max profit is: " & varMax & " it happens in year" & ActiveCell.Offset(0, -1).Value


End Sub

A few tips:

Notice that you don't need to "Dim as" when you declare your variables

You store the MAX value in a variable. Notice that I use addresses as arguments for WorksheetFunction.Max. IT makes the code easier to read. Named fields and variables are cool but they tend to make the code obscure.

Agreeing with Colin you need to select the cell containing the max and the year is in the cell on the left: Activecell.Offset(0,-1).value.

Offset is the VBA word that you will use the most. Learn all about it and also about CurrentRegion in my tutorial at

Free Help on VBA for Excel (Macros)

Enjoy