Results 1 to 4 of 4

Thread: using offset?

  1. #1
    Join Date
    Sep 2009

    Unanswered: using offset?

    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

    Sub Test2()
        Dim varArray As Variant
        varArray = Range("ValArray")
         MsgBox "the max profit is" & ": " & WorksheetFunction.Max(varArray) & " it happens in year" & ActiveCell.Offset(1, 0).Value
    End Sub
    " it happens in year" & ActiveCell.Offset(1, 0).Value
    i'm wondering why this part is not working, i just want to show the year when max profit occured

  2. #2
    Join Date
    Sep 2008
    London, UK
    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

  3. #3
    Join Date
    Oct 2009
    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:


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

  4. #4
    Join Date
    Dec 2003
    Ottawa, Canada

    The code you need is:

    Sub Test2()
    Dim varMax


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

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

    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)

    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts