Script to select only negative values

[theboyscout]

Hello forum,

I need a script that will select only the rows that contain a negative value to be used in a macro

TORRES <tbody> </tbody>							(420.00) <tbody> </tbody>
TORRES <tbody> </tbody>							(4.80) <tbody> </tbody>
MARSICO <tbody> </tbody>							(85.36) <tbody> </tbody>
KURHAN <tbody> </tbody>							(1167.12) <tbody> </tbody>
CONDE <tbody> </tbody>							(2927.40) <tbody> </tbody>
CONDE <tbody> </tbody>							(4445.34) <tbody> </tbody>
FROHNHOEFER <tbody> </tbody>	GA <tbody> </tbody>	H <tbody> </tbody>	80.7 <tbody> </tbody>	H <tbody> </tbody>	A <tbody> </tbody>	0 <tbody> </tbody>	1129.80 <tbody> </tbody>
FREDERICK <tbody> </tbody>	JA <tbody> </tbody>	H <tbody> </tbody>	73.53 <tbody> </tbody>	H <tbody> </tbody>	A <tbody> </tbody>	0 <tbody> </tbody>	1129.80 <tbody> </tbody>

<tbody>
</tbody>

Thank you in advance

 

[ZAX]

This Code Will Select The Rows That Contain a Negative Value.

Sub SelectNegative()
Set Data = Range("a1:" & Cells(Cells.End(xlDown).Row, 500).Address)
i = ""
For Each Cell In Data
    If InStr(1, Cell.Value, "-") > 0 Then
       If i = "" Then
          i = Cell.EntireRow.Address
       Else
          i = i & "," & Cell.EntireRow.Address
       End If
    End If
Next
If i <> "" Then
   Range(i).Select
Else
   MsgBox "The Current Worksheet Has No Negative Values"
End If
End Sub

ZAX

 

[ZAX]

Sorry The First One Is Wrong Here's The Correct One:

Sub SelectNegative()
Set Data = Range("a1:" & Cells(Cells.Find("*", , , , xlByRows, xlPrevious).Row, _
Cells.Find("*", , , , xlByColumns, xlPrevious).Column).Address)
i = ""
For Each Cell In Data
    If InStr(1, Cell.Value, "-") > 0 Then
       If i = "" Then
          i = Cell.EntireRow.Address
       Else
          i = i & "," & Cell.EntireRow.Address
       End If
    End If
Next
If i <> "" Then
   Range(i).Select
Else
   MsgBox "The Current Worksheet Has No Negative Values"
End If
End Sub

ZAX

 

[theboyscout]

Great job Zax!!!!

One thing, is it possible to stop at the first instance a positive number appears?

 

[ZAX]

You Mean If The Macro Meats A Positive Value Then It Will Stop ?
Here's a code for that

Sub SelectNegative()
Set Data = Range("a1:" & Cells(Cells.Find("*", , , , xlByRows, xlPrevious).Row, _
Cells.Find("*", , , , xlByColumns, xlPrevious).Column).Address)
i = ""
For Each Cell In Data
    If InStr(1, Cell.Value, "+") > 0 Then
       Exit Sub
    End If
    If InStr(1, Cell.Value, "-") > 0 Then
       If i = "" Then
          i = Cell.EntireRow.Address
       Else
          i = i & "," & Cell.EntireRow.Address
       End If
    End If
Next
If i <> "" Then
   Range(i).Select
Else
   MsgBox "The Current Worksheet Has No Negative Values"
End If
End Sub

ZAX

 

[ZAX]

Thx a LOT For The Reply 'cause After You've Showed Me What You Want Exactly,It Became Easy For Me To Write The Script!

Sub SelectNegatives()
Dim Cell As Range
i = ""
For Each Cell In Range("J1:J" & Cells(Rows.Count, 10).End(xlUp).Row)
    If InStr(1, Cell.Value, "-") > 0 Then
       If i = "" Then
          i = Cell.EntireRow.Address
       Else
          i = i & "," & Cell.EntireRow.Address
       End If
    ElseIf Abs(Cell.Value) = Cell.Value And IsEmpty(Cell) = False Then
       Exit For
    End If
Next
If i = "" Then
   MsgBox "Column J Has No Negative Values"
Else
   Range(i).Select
End If
End Sub

ZAX

 

[Rick Rothstein]

Thx a LOT For The Reply 'cause After You've Showed Me What You Want Exactly,It Became Easy For Me To Write The Script!

Sub SelectNegatives()
Dim Cell As Range
i = ""
For Each Cell In Range("J1:J" & Cells(Rows.Count, 10).End(xlUp).Row)
    If InStr(1, Cell.Value, "-") > 0 Then
       If i = "" Then
          i = Cell.EntireRow.Address
       Else
          i = i & "," & Cell.EntireRow.Address
       End If
    ElseIf Abs(Cell.Value) = Cell.Value And IsEmpty(Cell) = False Then
       Exit For
    End If
Next
If i = "" Then
   MsgBox "Column J Has No Negative Values"
Else
   Range(i).Select
End If
End Sub

ZAX

There is a possible problem that could occur with your code. The argument to the Range function (purple highlight) has a limit of 255 (or maybe it is 256) characters. Given that, you can delay running in to that limit by changing the red highlighted lines of code to these...

i = Cell.EntireRow.Address(0, 0)

i = i & "," & Cell.EntireRow.Address(0, 0)

The (0, 0) addition makes the Address property return the addresses without the $ signs... this will allow you to concatenate more addresses together before reaching the limit... still, even with this change, if the 'i' variable becomes longer than 255 total characters, the purple line of code will fail (VB will not highlight the failure line when it does fail, but that is the line that where the failure will occur at).

 

[ZAX]

Thanx For The Information I Learned Something New,But I Don't Think He Even Read This Post,Because Days Ago We Started Sending Private Messages But I Couldn't Send This One To Him So I Posted It Here.