none
Max (and VLOOKUP) function with Selection (x1up) not specific cells RRS feed

  • Question

  • Hi am a novice on VBA and have tried everything so I would be appreciative of any help. 

    I am looking to write a macro that will select a max value in a column of a select area. These areas (or column heights) are varying in height and therefore I need the "Selection(x1up)" selection rather than specific co-ordinate. I.e. some columns are 100 lines long others are 300. 

    So this is what I get when I record a macro: 


     ActiveCell.FormulaR1C1 = "=MAX(R[-197]C:R[-1]C)"
        ActiveCell.Offset(1, 0).Range("A1").Select

    I need something that simply uses the max function for the cell above the active cell and then the range should be up using "shift" + "ctrl" + "arrow up". 

    My second issue is that I am wanting to write a lookup in the cell below the max cell with the forumula where it will look for the max value in the column above it (two above the vlookup cell) and then connect it to another column that is X across. Like above I need it to have the "selection end up and right" functions like below 

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

    rather than having the set coordinates.  

        ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-1]C,R[-198]C:R[-2]C[15],15,FALSE)"
        ActiveCell.Offset(1, 0).Range("A1").Select

    ANY help would be really appreciated. 

    Thanks in advance! 

    Thursday, August 11, 2016 1:04 PM

Answers

  • >>Can you make it so that Max value is 5 decimal places

    Sub Max()

    Dim rng As Range

    Dim MaxResult As Integer

    ActiveCell.Select

    Set rng = Worksheets("Sheet1").Range(Selection, Selection.End(xlUp))

    MaxResult = Application.WorksheetFunction.Max(rng)

    ActiveCell.NumberFormat = "0.00000"

    ActiveCell.Value = MaxResult

    End Sub

     

    >>last thing is that though the code suggests the VLOOKUP should go to the last column it seems to go the penultimate one

    Which cell do you want to search and which cell value do you want to return in the range?

    I rewrite the Vlookup menthod according to your formula.

    You could see the picture below.

    When selecting C10 as ActiveCell, the Vlookup function I have written in VBA is actually search the C9 in row 8 and row 9, and then get the value of Q9.

    For more information about how to use vlookup method, please visit WorksheetFunction.VLookup method.

    Also you could see this similar thread to get some example and explanation.

    • Marked as answer by MKordi Saturday, August 13, 2016 10:33 AM
    Friday, August 12, 2016 8:38 AM
    Moderator

All replies

  • Hi,

    You could refer to the code below.

    Sub Max()
    Dim rng As Range
    Dim MaxResult As Integer
    ActiveCell.Select
    Set rng = Worksheets("Sheet1").Range(Selection, Selection.End(xlUp))
    MaxResult = Application.WorksheetFunction.Max(rng)
    MsgBox MaxResult
    End Sub
    Sub VLookup()
    Dim rng, rng1 As Range
    'get the two cells above the max cell
    Set rng = Application.Union(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-2, 0))
    'set the vlookup range
    Set rng1 = Worksheets("Sheet1").Range(rng, rng.End(xlToRight))
    'Use  "=VLOOKUP(R[-1]C,R[-198]C:R[-2]C[15],15,FALSE)" in VBA
    VLookupResult = Application.WorksheetFunction.VLookup(ActiveCell.Offset(-1, 0), rng1, 15, False)
    MsgBox VLookupResult
    End Sub


    Friday, August 12, 2016 5:55 AM
    Moderator
  • Hi

    Firstly THANK YOU VERY MUCH FOR THIS. It seems to work however, there are two issues that I hope you can help me with. 

    1) Can you make it so that Max value is 5 decimal places 

    2) Rather than have a message box come up can we have the value go into the active cell(s)? 

    Again, any help is much appreciated. 

    Thanks again

    Friday, August 12, 2016 7:57 AM
  • Also, last thing is that though the code suggests the VLOOKUP should go to the last column it seems to go the penultimate one?! 
    Friday, August 12, 2016 8:02 AM
  • >>Can you make it so that Max value is 5 decimal places

    Sub Max()

    Dim rng As Range

    Dim MaxResult As Integer

    ActiveCell.Select

    Set rng = Worksheets("Sheet1").Range(Selection, Selection.End(xlUp))

    MaxResult = Application.WorksheetFunction.Max(rng)

    ActiveCell.NumberFormat = "0.00000"

    ActiveCell.Value = MaxResult

    End Sub

     

    >>last thing is that though the code suggests the VLOOKUP should go to the last column it seems to go the penultimate one

    Which cell do you want to search and which cell value do you want to return in the range?

    I rewrite the Vlookup menthod according to your formula.

    You could see the picture below.

    When selecting C10 as ActiveCell, the Vlookup function I have written in VBA is actually search the C9 in row 8 and row 9, and then get the value of Q9.

    For more information about how to use vlookup method, please visit WorksheetFunction.VLookup method.

    Also you could see this similar thread to get some example and explanation.

    • Marked as answer by MKordi Saturday, August 13, 2016 10:33 AM
    Friday, August 12, 2016 8:38 AM
    Moderator
  • Perfect thank you I will def look up the VLOOKUP (pun very much intended!) - much appreciated. 

    Last thing, when I put the code in for the Max it just comes up with 0.0000 - can you help with that - sorry to be a pain but I am a bit in the dark on this thanks again. 

    Friday, August 12, 2016 9:18 AM
  • Hi,

    Since i couldnt reproduce your issue, you could try to add the following line before ActiveCell.Select to set range into General.

    ActiveSheet.Columns(ActiveCell.Column).NumberFormat = "General"


    Friday, August 12, 2016 9:48 AM
    Moderator