VBA code to reference cell

Amanda_Jones81

New Member
Joined
Nov 11, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

fadee2

Active Member
Joined
Nov 7, 2020
Messages
335
Office Version
  1. 2019
Platform
  1. Windows
hi,
try using below code

VBA 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
1605939364325.png


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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum
Another one for you
VBA Code:
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

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
VBA Option
VBA Code:
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
ABCDEFGHIJKLMNOPQ
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
3421345608910111213
Sheet2
Cell Formulas
RangeFormula
E22:Q22E22=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

New Member
Joined
Nov 11, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
hi,
try using below code

VBA 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!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,441
Messages
5,624,797
Members
416,054
Latest member
Ariel2219

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top