Home>

### calculation of the value corresponding to the vba min value

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.

• 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