Find Method question...

[bramnizzle]

I found this bit of code on the internet.

Copy all the rows that have the value 1000 in column D and paste to
Sheet2:
Find_Range(1000, Columns("D"), xlFormulas, xlWhole).EntireRow.Copy
Range("Sheet2!A1")

This finds the value 1000 in column D. I want to be able to find a
negative value in column D.

Any clue?

 

[Dave Peterson]

If your data is all numeric and is formatted to show negatives using minus
signs, maybe you could look for -.

If both those things aren't true, then I think you'll have to loop through the
cells.

You may be able to reduce the cells to loop through by looking at just the
numeric cells in column D.

If your data is all constants (no formulas):

dim myRng as range
dim myCell as range

set myrng = nothing
on error resume next
set myrng = worksheets("sheet999").range("d:d") _
.cells.specialcells(xlcelltypeconstants, xlnumbers)
on error goto 0

if myrng is nothing then
msgbox "No numbers!
else
for each mycell in myrng.cells
if mycell.value < 0 then
'found it, do what you want
end if
next mycell
end if

(Untested, uncompiled. Watch for typos.)

 

[bramnizzle]

What if they are not all constants (some have formulas)?

 

[Dave Peterson]

You could build a giant range (a union of the constants and formulas) or just
look through all the cells.

dim myRng as range
dim myCell as range

with worksheets("Somesheetnamehere")
'headers in Row 1???
set myrng = .range("d2",.cells(.rows.count,"D).end(xlup))
end with

for each mycell in myrng.cells
if mycell.value < 0 then
'found it, do what you want
end if
next mycell

 

[bramnizzle]

You could build a giant range (a union of the constants and formulas) or just
look through all the cells.

dim myRng as range
dim myCell as range

with worksheets("Somesheetnamehere")
'headers in Row 1???
set myrng = .range("d2",.cells(.rows.count,"D).end(xlup))
end with

for each mycell in myrng.cells
if mycell.value < 0 then
'found it, do what you want
end if
next mycell

What I meant was, what if the negative numbers I want to find are
formulas? In other words, the formula produces a negative number.

 

[bramnizzle]

Actually, this worked perfect! Thanks!!!

 

[Dave Peterson]

If all the numbers were the results of formulas, you could use this

xlcelltypeformulas
instead of
xlcelltypeconstants

in that previous post.

The problem occurs when you have a mixture of both.

But glad you got it working ok.