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.

 

[Joe4]

Welcome to the Board!

Try this VBA procedure:

Sub FixNegatives()

    Dim ibox As Variant

    On Error GoTo exit_loop
    Do
        Range("A1").Select
        Cells.Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
        ibox = InputBox("Enter value to replace the value of " & ActiveCell & " in cell " & ActiveCell.Address(0, 0))
        ActiveCell = ibox
    Loop
    
    Exit Sub
    
exit_loop:
    If Err.Number = 91 Then
        MsgBox "No more negatives found", vbOKOnly, "COMPLETE!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If

End Sub

 

[Krishnab4u]

Dear Sir,
thank you for the quick help.
but i dont understand how to add this in button click event. Please help.

 

[Joe4]

See here: https://support.office.com/en-us/ar...l-button-d58edd7d-cb04-4964-bead-9c72c843a283

Just paste the code into a general VBA module in your workbook.

If you are using a Form control, you should be able simply assign that code, "as-is" to your button.

If you are using an ActiveX control, it will set up the shell of the code. Then just call the code from that, i.e.

Private Sub CommandButton1_Click()
    Call FixNegatives
End Sub

 

[Krishnab4u]

Dear sir,
i checked. this did not help me.
Actually I am not going to enter value right away on the same sheet as prompted by the code shared.
Correction will be done in another sheet. Later i will revisit the subject sheet, click the button to identify any more -ve values.
Please help.

 

[Joe4]

Actually I am not going to enter value right away on the same sheet as prompted by the code shared.
Correction will be done in another sheet. Later i will revisit the subject sheet, click the button to identify any more -ve values.

It is not clear on how you are doing this "correction".
Please give us a DETAILED explanation of what you are trying to do, and walk us through an actual example.

 

[Krishnab4u]

Dear Sir,
i am not able to upload / paste picture.
what to do.

 

[Joe4]

While you cannot upload files to this site, there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[Krishnab4u]

Sir,
still i am not able to share the pic.
Just help me out with code to select the first cell with -ve value in a given range using Button click event on first click.
when i click second time, selection should move the second cell with -ve value in the given range.
Thank you.

 

[Joe4]

Drop this scaled-down code into your On Click procedure:

    On Error GoTo exit_loop
    Cells.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