Condition Statement Help??

  • Thread starter Thread starter CWit
  • Start date Start date
C

CWit

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
 
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
 
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
 
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
 
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?
 
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
 
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
 
Back
Top