VBA Select cells if

[george hart]

Hi all

The code below works great in that it pops up a message showing the min value in column Q, but I need to search through the remaining column (Q) and if the min value is found anywhere within that column the message to advise on all those with the same value as the min value.

In short, find min value and if duplicated or not a message advising of all min values in column Q.

Something like ive ighlighted in bold perhaps?

Dim MaxVal As Double
Dim Row As Long
MinVal = Application.WorksheetFunction. _
Min(Range("Q:Q"))
For Row = 1 To Cells(Rows.Count, "Q").End(xlUp).Row

If Range("Q3" & X).Value = MinVal Then
Range("Q3" & X).Select
If Range("Q3" & X).Value = Selection Then Range("Q3" & X).Select
MsgBox "The next procedure up for peer review is " & Selection.Offset(0, -14).Value & " on the " & ActiveCell
Exit For
End If
Next Row
End Sub


Many thanks in advance

 

[Norie]

Can't you just remove the Exit For?

 

[george hart]

That doesn't appear to work as it just loops and if I have say 3 cells the same with the min value I get three messages but all the same.

Ideally I'd like the message to list all three for example

procedure next for review is Test1
procedure next for review is Test2
procedure next for review is Test3
etc.........

 

[Norie]

What's X?

Why aren't you using the loop variable Row?

(Row, isn't a good name for a variable by the way)

 

[george hart]

Got it now. Changed the whole thing as below and it works fine. Many thanks for you help

Dim rngA As Range
Dim cell As Range
MinVal = Application.WorksheetFunction. _
Min(Range("Q:Q"))

Set rngA = Range("Q1", Range("Q65536").End(xlUp))
For Each cell In rngA
If cell.Value = MinVal Then
MsgBox cell.Offset(0, -14).Value 'replace with yr code here
End If
Next cell
End Sub