Trouble using WorksheetFunction.Min

[XcelThug]

I am trying to get min value from the rows using VBA Application.Worksheet.Function.Min . I am able to get the min value but when i try to autofill it downwards, it is not happening.
Please refer the File i had attached, that is exactly how my data is going to be. I am trying to get min values in the J column.
-Please do not delete the I column.
-Please note while referring the arguments in Min wrksheet.Function please do not give something like "Range("A2:H2"). I want that to be dynamic.

Looking forward for your replies..

 

[jindon]

Is this how you wanted?

Sub test()
    Dim x As String
    With Sheets("sheet3").[a1].CurrentRegion
        With .Offset(1).Resize(.Rows.Count - 1)
            x = .Rows(1).Address(0, 0)
            With .Offset(, .Columns.Count + 1).Columns(1)
                .Formula = "=min(" & x & ")"
                .Value = .Value
            End With
        End With
    End With
End Sub

 

[XcelThug]

Hi Jindon, that is an amazing code and it meets all the specifications i had mentioned. You are amazing.
Thanks a ton.
Also if you dont mind would you be able to explain the code ? briefly. I know i am asking a little too much. Please

 

[jindon]

   With Sheets("sheet3").[a1].CurrentRegion
       With .Offset(1).Resize(.Rows.Count - 1)

CurrentRegion gets data area isolated by blank column/row.
So Cells(1).CurrentRegion will get A2:H5 in your example.

       With .Offset(1).Resize(.Rows.Count - 1)
            x = .Rows(1).Address(0, 0)

Offsetting 1 row and resize by rows.count -1 gets data area to exclude header.
Assign .Rows(1).Address(0,0) to variable x, which is A2:H2.

           With .Offset(, .Columns.Count + 1).Columns(1)
                .Formula = "=min(" & x & ")"
                .Value = .Value
           EndWith

.Offset(, .Columns.Count + 1).Columns(1) will get 2 columns after end of currentregion.
Input formula in the column,
Since x is relative referenced(not absolute) address, it gets all the formula in the right places.
Convert Formula to the value.

 

[XcelThug]

Thanks a lot for the explanation. Appreciate it totally.