VBA code to reference cell

[Amanda_Jones81]

I am trying to use VBA code to reference the minimum value in a range of cell across a row and then reference another cell corresponding to that minimum value and then display in a msg box.
example:

find minimum value in range E34:Q34
once minimum value is found in above range- identify the coressponding specific cell with string value in range E22:Q22 that is in the same column as that minimum value that was found.

then the string value in range E22:Q22 is displayed in a msgbox statement.

 

[fadee2]

hi,
try using below code

Dim findminvalue As Range
Set findminvalue = Range("e2:q2").Find(What:=Application.WorksheetFunction.Min(Range("e2:q2")))
    s = findminvalue.Offset(-1).Value
    MsgBox s

Results will be like

P.S. change the range of cells to your likings.

 

[Yongle]

Welcome to the forum
Another one for you

Sub ReturnMessage()
    Dim rng As Range
    Set rng = Range("E34:Q34")
    MsgBox Cells(22, WorksheetFunction.Match(WorksheetFunction.Min(rng), rng, 0) + 4).Value
End Sub

 

[Dossfm0q]

VBA Option

Public Sub Find_Min()
Dim Min As Long, C As Long, Cl As Long
Min = WorksheetFunction.Min(Range("E34:Q34"))
C = WorksheetFunction.Match(Min, Range("E34:Q34"))

Cl = Range("E34:Q34").Column - 1 + C
Range("E22:Q22").ClearComments
Cells(22, Cl) = "Min Range " & Cells(34, Cl).Address & ":=" & Min
MsgBox "Min Range " & Cells(34, Cl).Address & ":=" & Min
End Sub

Formula Option

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22											$G$34
23
24
25
26
27
28
29
30
31
32
33
34					2	1	3	4	5	6	0	8	9	10	11	12	13
Sheet2

Cell Formulas
Range	Formula
E22:Q22	E22	=IF(MATCH(MIN($E$34:$Q$34),$E$34:$Q$34,0)=COLUMN()-COLUMN($E$22)+1,ADDRESS(34,MATCH(MIN($E$34:$Q$34),$E$34:$Q$34,0)),"")

 

[Amanda_Jones81]

hi,
try using below code

Dim findminvalue As Range
Set findminvalue = Range("e2:q2").Find(What:=Application.WorksheetFunction.Min(Range("e2:q2")))
    s = findminvalue.Offset(-1).Value
    MsgBox s

Results will be like
View attachment 26490

P.S. change the range of cells to your likings.

That worked great thank you!!!!

 

[fadee2]

you are welcome