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,
I appreciate your patience support. This time "13: Type Mismatch" message is not coming, while it still cant identify -ve values. It says "NO Negative values".
May be we have to change this

rng.Find(What:="-",

Please enlighten

 

[Joe4]

I appreciate your patience support. This time "13: Type Mismatch" message is not coming, while it still cant identify -ve values. It says "NO Negative values".
May be we have to change this

That is telling you that it cannot find any negative values in your range.
Are you sure that there are negative values in the range you have designated in your query?

I tested it out myself, and had this same behavior if there were no negatives in the range. If there were, it found them and stopped at those cells.

 

[Krishnab4u]

sir, as i mentioned -ve values are as result of formula.
i tried the code in a separate sheet where in i had cells with -ve values just typed as well as cells with -ve values as result of formula.
the code is able to identify cells with -ve values typed but not the cells with -ve values obtained as result of formula.
kindly help.

 

[Joe4]

The last code I posted works fine for me, even when the amounts being returned are formulas.
What exactly do these formulas look like?
Can you post one?

 

[Krishnab4u]

in the given range formula used is =+AD7+AD15 (this particular cell i.e AD23 has -ve value but not identified by the code)
In other ranges formula is =IFERROR(IF($Z$3="ALL INDIA",SUMIFS(INDIRECT(INDEX('Model Master'!$I$2:$I$9,MATCH($G102,'Model Master'!$I$2:$I$9,0))),DSD,"="&AB$3,ALLOCWEEK,"<="&$Z$2,MODEL,"="&$B102)-AB102-AC102,""),"")

 

[Joe4]

Try changing this argument in the FIND function in the VBA code:

LookIn:=xlFormulas

to this:

LookIn:=xlValues

 

[Krishnab4u]

a big thumps up sir. Thank you

 

[Joe4]

You are welcome.
Glad we finally got is solved for you.