convert +/- on text value

[gossamer]

Hi ng
Using xl xp pro

Need to "flip" sign on value in column c if adjacent cell in col b
4199 TOTAL SALES AND REVENUES or
7110 TOTAL OPERATING PROFIT CONTRIBUTION

Value begin row 3
End row is unknown.
Can someone push me in the right direction?

tia
goss

 

[keepitcool]

Goss,
if I understand you correctly..

make a range of those accountnumbers you want flipped.
name that range: flipped

then use this will give you the flipped amount of c
=C3*(2*ISERROR(MATCH(A3,flipped,0))-1)

just copy it down and hide the original column 3


cheerz!

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[goss]

Thanks "cool"
Not quite it.
Want to change from neg to pos or pos it neg if either
condition is met. Namely, val in col b = "Sales" or "OPC"
Then convert neg/pos on adjacent cell col c
Otherwise leave value in col c intact

Ex:
=================================================
Begin Data-
A B C
1 Sales 90500

End Data-
A B C
1 Sales -90500
=================================================

"Sales" encountered in col b so adjacent cell col c, converted to neg

Prefer VBA if possbile so store in "personal.xls" in xlstart
This code would have multiple applications for me.

 

[keepitcool]

OH!!...

if you had been more specific in the first place
then both of use would have been done sooner!

Swap commant marker if you want values,
as is this will keep formulas


Option Explicit
Sub Flip()
Dim c As Range, cFirst As String, s As Variant, lCalc As Long
With Application
lCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
For Each s In Array( _
"sales and revenues", _
"operating profit")

With Range("b:b")
Set c = .Find(s, , xlValues, xlPart, xlByColumns, xlNext, False)
If Not c Is Nothing Then
cFirst = c.Address
Do
With c(1, 2)
If .Formula <> "" Then
.Formula = "=-1*" & Mid(.Formula, InStr(.Formula, "=") + 1)
'.Formula = -1 * .Value
End If
End With
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> cFirst
End If
End With
Next s
With Application
.ScreenUpdating = True
.Calculation = lCalc
End With
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >