WorkSheetFunction.Match To Find Position of Max Value

  • new at writing macros and trying to muddle my way through this.

    ive constructed a workbook with two sheets, the first shows the dimensions for certain transformers sold by a variety of vendors, the second is the maximum possible dimensions for specific transformers based on the previously mentioned specs. so the second sheet simply looks at the range on the first and identifies the max.

    im trying to write a macro that will display a message box with the vendors name when a specific dimension on the second sheet is selected, but i cant figure out how to identify what position the max value is in. the problem may be easier to understand by looking at the code (posted below). I was thinking it would be best to use MATCH, but i can't seem to get the syntax right

  • Re: HELP! Specifying range that might be read as text? and finding its max

    Please note the change I have made to your thread title and in future threads take more care to develop a more carefully worded title. The title shoue be as concise as possible while accurately describing your need/question. The key to proper thread titles is to think "search friendly", which means a search using your title as the search terms would yeild relevant results

    You don't need to use quotes around a variable. Also, you don't need to wrap the range with paranthesis.

    Replace as follows

    rngPosition = Replace(rngArr(1), ")", "")


    maxPosition = Application.WorksheetFunction.Match(maxVal, ThisWorkbook.Sheets("FULL DATA SET").Range(rngPosition), 0)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!