Condition Statement Help??
 

Hello I'm familiar with other code but I'm trying to learn the lingo o
Excel/ VBA. I'm trying to write a condition statement that would allo
me to scan column B2 down and search for "SEL Book" if it finds thos
words I would like it to change the corresponding number in Column C t
a negative number. Logically put it would look like this.

If B2 through B etc. = "SEL Book" then C etc. = negative number...


Thanks for helping a Newbie

--
Message posted from http://www.ExcelForum.com

Condition Statement Help??
 

Hi
try
sub foo()
dim rng as range
dim cell as range
set rng = Range("B2:B100")
for each cell in rng
if cell.calue = "SEL Book" then
cell.offset(0,1).value = -1*cell.offset(0,1).value
end if
next
end sub

--
Regards
Frank Kabel
Frankfurt, Germany
"CWit " schrieb im Newsbeitrag
...
Hello I'm familiar with other code but I'm trying to learn the lingo
of
Excel/ VBA. I'm trying to write a condition statement that would
allow
me to scan column B2 down and search for "SEL Book" if it finds those
words I would like it to change the corresponding number in Column C
to
a negative number. Logically put it would look like this.

If B2 through B etc. = "SEL Book" then C etc. = negative number...


Thanks for helping a Newbie!


---
Message posted from http://www.ExcelForum.com/

Condition Statement Help??
 

One way:

Public Sub NegateSELBook()
Dim rFound As Range
Dim sfoundAddr As String
With Columns(2)
Set rFound = .Find( _
What:="SEL Book", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rFound Is Nothing Then
Do
sfoundAddr = rFound.Address
With rFound(1, 2)
.Value = -Abs(.Value)
End With
Set rFound = .FindNext(After:=rFound)
Loop Until rFound.Address = sfoundAddr
End If
End With
End Sub

Note: this will make the number always a negative number, as you
specified. If you'd rather have it just toggle between negative and
positive, change

.Value = -Abs(.Value)

to

.Value = -.Value


In article ,
CWit wrote:

Hello I'm familiar with other code but I'm trying to learn the lingo of
Excel/ VBA. I'm trying to write a condition statement that would allow
me to scan column B2 down and search for "SEL Book" if it finds those
words I would like it to change the corresponding number in Column C to
a negative number. Logically put it would look like this.

If B2 through B etc. = "SEL Book" then C etc. = negative number...

Condition Statement Help??
 

Thanks for the quick response, the code works but it only allows on
number in column C to be negative at a time so say I have 25 rows i
Column C with that condition only one converts to a negative number
I'm trying to add all those numbers in Column C up so I need al
corresponding cells to turn negative so my total is correct. Thank

--
Message posted from http://www.ExcelForum.com

Condition Statement Help??
 

Hi
not sure I understand you but the code would change all entries in
column C to a negative number if the adjacent cell in column B contains
your criteria?

--
Regards
Frank Kabel
Frankfurt, Germany
"CWit " schrieb im Newsbeitrag
...
Thanks for the quick response, the code works but it only allows one
number in column C to be negative at a time so say I have 25 rows in
Column C with that condition only one converts to a negative number.
I'm trying to add all those numbers in Column C up so I need all
corresponding cells to turn negative so my total is correct. Thanks


---
Message posted from http://www.ExcelForum.com/

Condition Statement Help??
 

Frank,

Its not that all the number do not turn negative they do, but if
click somewhere on the sheet it toggles Positive then if I clic
somewhere again back to negative and so on. Is there anyway to make i
stay negative at all times

--
Message posted from http://www.ExcelForum.com

Condition Statement Help??
 

Hi
just clicking somethere in you sheet should not affect the entries (as
you have to invoke this macro manually). But you may use the following
to leave the numbers negative

sub foo()
dim rng as range
dim cell as range
set rng = Range("B2:B100")
for each cell in rng
if cell.calue = "SEL Book" then
if cell.offset(0,1).value 0
cell.offset(0,1).value = -1*cell.offset(0,1).value
end if
end if
next
end sub



--
Regards
Frank Kabel
Frankfurt, Germany
"CWit " schrieb im Newsbeitrag
...
Frank,

Its not that all the number do not turn negative they do, but if I
click somewhere on the sheet it toggles Positive then if I click
somewhere again back to negative and so on. Is there anyway to make
it
stay negative at all times?


---
Message posted from http://www.ExcelForum.com/

Condition Statement Help??
 

perfect thank

--
Message posted from http://www.ExcelForum.com