I have a procedure below which checks for occurences of a text value in a named range
"mynewset" as it occurs in another range. It then checks the values of particular cells
offset from any matching text and runs a counter to find the occurence of one cell having
a greater value than the other. It then finishes with a message box indicating the number
of occurences of this situation. This works perfectly and does all that is asked of it. I
know that I could highlight the rows where this occurs but what I would ideally like to
do is for the message box to list the row numbers where this occurs. ie instead of saying
"the set-aside is ineligible in m fields" I would like it to list where the rows are. I
really just do not know and cannot find how to do this, and would value any help

Regards
Graham Haughs
Turriff
Scotland

Sub Setaside()

Dim N As Long
Dim C As Variant
Dim Rng As Range
Dim FirstAddress As String
Dim m As Integer
Dim n As Integer
Dim Wng As Range

ActiveWorkbook.Names.Add Name:="mynewset", RefersToR1C1:= _
"=CSVEditor.xls!FULLSET"
Set Rng = Range("mynewset")
Set Wng = Range("h15:h400")
m = WorksheetFunction.CountIf(Wng, "*")

With Rng
n = 0
For N = 15 To m + 14
Set C = .Find(Cells(N, 14).Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If (Cells(N, 14).Offset(0, 1).Value Cells(N, 14).Offset(0, -3)) Then
n = n + 1
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
End If
Next
End With
If n 0 Then
MsgBox ("The set-aside is ineligible in " & m & " field"), , "Set-aside"
End If
End Sub

Dim mesage as string
mesage=""

and then after the:

Set C = .FindNext(C)

insert something like:

mesage=mesage & C.row & Chr(10)

and near the end just MsgBox(mesage)
--
Gary''s Student - gsnu2007h


"Graham H" wrote:

I have a procedure below which checks for occurences of a text value in a named range
"mynewset" as it occurs in another range. It then checks the values of particular cells
offset from any matching text and runs a counter to find the occurence of one cell having
a greater value than the other. It then finishes with a message box indicating the number
of occurences of this situation. This works perfectly and does all that is asked of it. I
know that I could highlight the rows where this occurs but what I would ideally like to
do is for the message box to list the row numbers where this occurs. ie instead of saying
"the set-aside is ineligible in m fields" I would like it to list where the rows are. I
really just do not know and cannot find how to do this, and would value any help

Regards
Graham Haughs
Turriff
Scotland

Sub Setaside()

Dim N As Long
Dim C As Variant
Dim Rng As Range
Dim FirstAddress As String
Dim m As Integer
Dim n As Integer
Dim Wng As Range

ActiveWorkbook.Names.Add Name:="mynewset", RefersToR1C1:= _
"=CSVEditor.xls!FULLSET"
Set Rng = Range("mynewset")
Set Wng = Range("h15:h400")
m = WorksheetFunction.CountIf(Wng, "*")

With Rng
n = 0
For N = 15 To m + 14
Set C = .Find(Cells(N, 14).Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If (Cells(N, 14).Offset(0, 1).Value Cells(N, 14).Offset(0, -3)) Then
n = n + 1
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
End If
Next
End With
If n 0 Then
MsgBox ("The set-aside is ineligible in " & m & " field"), , "Set-aside"
End If
End Sub

Many thanks for that and for coming back so quickly.

Graham

Gary''s Student wrote:
Dim mesage as string
mesage=""

and then after the:

Set C = .FindNext(C)

insert something like:

mesage=mesage & C.row & Chr(10)

and near the end just MsgBox(mesage)