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
Sub showVendor()
'
' Macro1 Vendor Identification
'
' Keyboard Shortcut: Ctrl+q
'
' Identify the max val and the range that for which the cell was finding a maximum
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
rngPosition = ActiveCell.Formula
rngArr = Split(rngPosition, "!")
rngPosition = "(" + rngArr(1)
maxVal = ActiveCell.Value
' Locate address of cell on FULL DATA SET sheet where max is located
' It will be a number 1-5, which will correspond to a vendor
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
maxPosition = Application.WorksheetFunction.Match(maxVal, ThisWorkbook.Sheets("FULL DATA SET").Range("rngPosition")) <---NOT WORKING
' Identify the vendor with max value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Select Case maxPosition
'Case 1
'Vendor = "Square D"
'Case 2
'Vendor = "Eaton"
'Case 3
'Vendor = "GE"
'Case 4
'Vendor = "Siemens"
'Case 5
'Vendor = "Acme"
'Case Else
'Vendor = "Something went wrong. Maybe you should try again"
'End Select
' Display vendor
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
MsgBox Vendor
End Sub
Display More