Identifying rows in a message box
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 |
Identifying rows in a message box
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 |
Identifying rows in a message box
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) |
All times are GMT +1. The time now is 04:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com