Results 1 to 4 of 4

Thread: using offset?

  1. #1
    Join Date
    Sep 2009
    Location
    edmond,ok
    Posts
    4

    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

    Code:
    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
    Code:
    " 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
    Location
    London, UK
    Posts
    511
    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
    Posts
    8
    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.

  4. #4
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    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
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

Posting Permissions

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