Code required for Offset, to find max values

Dear all experts,

Need a code,Kindly find the sample output excel file for offset and max value finder task.

Thanking you all,

Best regards.

Though it can be easily done with the help of the formulas, try the below code to see if that is what you are trying to achieve...

Sub GetMaxValueAndHeader()
Dim lr As Long, lc As Long, i As Long, j As Long
Dim rng As Range
Dim mVal As Double
Dim Header As String

lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 3 To lr
   For j = 1 To lc
      If Abs(Cells(i, j)) > mVal Then
         mVal = Abs(Cells(i, j))
         Header = Cells(2, j)
      End If
   Next j
   Cells(i, 5) = Header
   Cells(i, 6) = mVal
   Header = ""
   mVal = 0
Next i
End Sub

If you need a formula based solution, try these Array Formulas which require confirmation with Ctrl+Shift+Enter instead of Enter alone.

In F3

=MAX(ABS(A3:D3))

In F3

=INDEX($A$2:$D$2,MATCH(H3,ABS(A3:D3),0))

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

Please try this....

Sub GetMaxValueAndHeader()
Dim lr As Long, lc As Long, i As Long, j As Long
Dim rng As Range
Dim vVal As Double,mVal As Double
Dim Header As String

lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = 4
For i = 3 To lr
   For j = 1 To lc
      If Abs(Cells(i, j)) > vVal Then
         vVal = Abs(Cells(i, j))
         mVal = Cells(i, j)
         Header = Cells(2, j)
      End If
   Next j
   Cells(i, 5) = Header
   Cells(i, 6) = mVal
   Header = ""
   vVal = 0
   mVal = 0
Next i
End Sub

You're welcome. Glad to help.

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.