Find Method question...
 

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?

Find Method question...
 

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.)

wrote:

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

Find Method question...
 

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

Find Method question...
 

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



wrote:

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

--

Dave Peterson

Find Method question...
 

On Nov 25, 10:12 pm, Dave Peterson wrote:
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.

Find Method question...
 

Actually, this worked perfect! Thanks!!!

Find Method question...
 

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.

wrote:

On Nov 25, 10:12 pm, Dave Peterson wrote:
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.

--

Dave Peterson