Home>

I have something I would like to do to improve work efficiency, but I can't think of a way to do it, so I have a question.

【Concrete example】

Column A: January 2000-December 2020
Column B and beyond: Corresponding sales

[]

①: Find the minimum sales value for each column after column B.
(2): I want to find the value in column A for the minimum sales.

[Trouble]
Of course, ① can be calculated, but I couldn't come up with the code for ②.

[Code up to now (up to ①)]

Sub minimum value ()

Dim wave As Worksheet
Dim thicknenn As Worksheet
Dim i As Long

Set wave = Worksheets ("text")
Set thickness = Worksheets ("Results")

For i = 1 To thickness.Cells (1, 5) .Value
wave.Cells (4, i + 1) .Value = WorksheetFunction.Min (Range (Cells (307, i + 1), Cells (407, i + 1)))
Next i
End Sub

I asked this question because I would like to teach you the code corresponding to ② after this.
I would appreciate it if you could give me an idea. I look forward to working with you.

vba
  • Answer # 1

    How about changing the code of the For Next statement to the following?

    Dim rng As Range
    Dim minValue As Long
    For i = 1 To thickness.Cells (1, 5) .Value
        Set rng = Range (Cells (307, i + 1), Cells (407, i + 1))
        minValue = WorksheetFunction.min (rng)
        wave.Cells (4, i + 1) .Value = minValue
        wave.Cells (5, i + 1) .Value = Cells (WorksheetFunction.Match (minValue, rng, 0) + 1, 1)
    Next i

    If you use mathematical formulas

    Set the following formula in the first cell where you want to display the minimum value
    = MIN (Result! B2: B6)
    Fill drag to the right by the number of data

    I want to display the year and month First, set the following formula in the cell
    = INDEX (Result! $A2: $A6, MATCH (Result! B8, Result! B2: B6,0))
    Fill drag to the right by the number of data

Related articles