Find Search with Msg PopUp

[parttime_guy]

Hi All,

Having a problem in controlling the Msg Popup

Column A has two scenarios:
1. If the word "NEW" is available in a CELL of the column and there is no other information in other cells there should be "no Msg pop-up"
2. If the word "NEW" is available in a CELL of the column along with any other information, there should be a Msg pop-op

The code/sample file is giving Msg pop-up in both the scenarios.:banghead:

Kindly assist.

Best Regards

[GTO]

Try:



Option Explicit
'
Sub Find_others()
Dim LastCell As Range
Dim Cell As Range
Dim MyRange As Range
Dim bolAllNews As Boolean
'
Set LastCell = RangeFound(Sheet1.Range("A:A"))
'
If LastCell Is Nothing Then
'All blank cells, just exit
Exit Sub
Else
'We found something, let us see if we found all "NEW". First, we'll set a range of
'cells, from A1 to the last cell in column A with any value.
Set MyRange = Sheet1.Range(Sheet1.Cells(1), LastCell)

For Each Cell In MyRange.Cells
If Not Cell.Value = "NEW" And Not Cell.Value = vbNullString Then
MsgBox "FOR YOUR ATTENTION" & Chr(13) & Chr(13) & "THIS FILE HAS AMENDMENTS" & Chr(13) & "PLEASE CHECK AND REVIEW" & Chr(13) & Chr(13) & "THEN PROCEED........"
'We only need to find one non-blank cell with a value other than "NEW", so quit processing...
Exit Sub
End If
Next
'
'If we make it to here, we found all "NEW" values (along with any blanks), so here is where you would do something else.
MsgBox "Do something else?"
'
End If
'
End Sub


Does that help?

Mark

[parttime_guy]

Hi Mark :thumb

Thanks for your code works perfect (....just what I wanted).

I amended

Set LastCell = RangeFound(Sheet1.Range("A:A"))
(...was giving a Compile error)

to

Set LastCell = Sheet1.Range("A:A")

Thanks & Best Regards :clap: :friends:

[GTO]

ACK! My bad... I left out the function.



Function RangeFound(SearchRange As Range, _
Optional ByVal FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range
'
If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange.Cells(1)
End If
'
Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function



Change it back to: Set LastCell = RangeFound(Sheet1.Range("A:A"))

This will find the last row in Col A with any data in it.

[parttime_guy]

Thanks Mark for the update - appreciated.:hi: :yes
Thanks and Best Regards :beerchug: