Identify Negative value

[Krishnab4u]

Dear All gurus,
I am working on a excel file, which a bit large.
I have to scroll left right to identify any negative values.
Instead i am thinking if it is possible by Clicking on a button to
>identify the cell having the negative value
>Select the cell
>Show it in message box (a message box not hiding the selected cell).

After i correct the negative value, i may click the button to identify next negative value.

Kindly help me.
Thank You.

 

[Krishnab4u]

Dear Sir,
thank you. It works.
Please help me what to do if i want this search only in specifies RANGES.


​Thank You.

 

[Joe4]

Please help me what to do if i want this search only in specifies RANGES.

You replace the "Cells" in the "Cells.Find..." part of your command with the range variable.

 

[Krishnab4u]

sir, what if there are multiple ranges? and are not continuous.

 

[Joe4]

You can set up a range variable with multiple non-continuous ranges.
See: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/refer-to-multiple-ranges

 

[Krishnab4u]

Sir,
thank you for your patience support. I get syntax error. Please help.

Private Sub CommandButton1_Click()
  On Error GoTo exit_loop
    Worksheets("Summary").Range("AB22:BH28").Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    On Error GoTo 0
    Exit Sub
    
exit_loop:
    If Err.Number = 91 Then
        MsgBox "No negatives found", vbOKOnly, "COMPLETE!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
End Sub

Please help.

 

[Joe4]

Try this:

Private Sub CommandButton1_Click()
    On Error GoTo exit_loop
    Worksheets("Summary").Activate
    Range("AB22:BH28").Select
    Selection.Find(What:="-", After:=Range("AB22"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    On Error GoTo 0
    Exit Sub
    
exit_loop:
    If Err.Number = 91 Then
        MsgBox "No negatives found", vbOKOnly, "COMPLETE!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
End Sub

 

[Krishnab4u]

Thank You.
But, this code just selects the entire range and shows No negative found though there are cells with -ve values.
It would be helpful if the selection stops at the first cell with -ve value on first click. On 2nd click the selection will move to the next -ve value.
Is it possible.

 

[Joe4]

Right, forgot about that part, so we don't want to hard-code the "After" argument.
I think this will do it:

Private Sub CommandButton1_Click()

    Dim rng As Range
    Set rng = Worksheets("Summary").Range("AB22:BH28")
    
    On Error GoTo exit_loop
    rng.Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    On Error GoTo 0
    Exit Sub
    
exit_loop:
    If Err.Number = 91 Then
        MsgBox "No negatives found", vbOKOnly, "COMPLETE!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
End Sub

 

[Krishnab4u]

No Luck sir..... how to share the picture?. I think this is my office laptop hence not able to share any pic.
Current scenario is, when i select some cell inside the range and click on the button, it says "No Negative found" even though there is a cell with -ve value (obtained by formula) right after it. When i select some cells outside the range, it says "13:type mismatch".

 

[Joe4]

Try this. This work whether or not you start from somewhere inside your range.

Private Sub CommandButton1_Click()

    Dim rng As Range
    Set rng = Range("AB22:BH28")
    
'   Check to make sure cursor is inside range
    If Intersect(ActiveCell, rng) Is Nothing Then
        rng(1, 1).Select
    End If
    
    On Error GoTo exit_loop
    rng.Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    On Error GoTo 0
    Exit Sub
    
exit_loop:
    If Err.Number = 91 Then
        MsgBox "No negatives found", vbOKOnly, "COMPLETE!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
End Sub