Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Method question...
What if they are not all constants (some have formulas)?
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Method question...
Actually, this worked perfect! Thanks!!!
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xls vba find method to find row that contains the current date | Excel Programming | |||
Question using the excel find method in vba? | Excel Programming | |||
Question on Find method in vba | Excel Programming | |||
Using variables to make a date and using find method to find that. | Excel Programming | |||
Find Method question | Excel Programming |