Hi,

A bit shorter

Sub test()
Dim cl As Range
For Each cl In Range("L2", Range("L2").End(xlDown))
If cl.Value = "abc" Then cl.Offset(0, -9).Value = cl.Offset(0, -9).Value *
(-1)
Next cl
End Sub

Regards
Jean-Yves

"cht13er" wrote in message
...
On Mar 27, 8:32 am, Sinner wrote:
Hi,

How do I do that in VB?
For each value 'abc' is found in columnL starting from cell L2 then
turn values in columnE starting C2 positive to negative (vice versa)

Thanks

Try pasting this in a new module and hitting F5:


Private Sub SwitchSign()
Dim iLastRow As Integer
Dim strAddress As String

ActiveCell.SpecialCells(xlLastCell).Select
strAddress = ActiveCell.Address

Do Until IsNumeric(strAddress) = True And Left(strAddress, 1) <
"$"
strAddress = Mid(strAddress, 2)
Loop

iLastRow = CInt(strAddress)

Cells(1, 12).Select

For iCounter = 1 To iLastRow - 1
If ActiveCell.Offset(iCounter, 0).Value = "abc" Then
ActiveCell.Offset(iCounter, -9).Value = (-1) *
ActiveCell.Offset(iCounter, -9).Value
End If
Next iCounter
End Sub



Hope this helps, let me know if you have any problems!
Chris