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

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

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

Middling length but it doesn't destry formulas

Sub test()
Dim myrange As Range
LastRow = Cells(Cells.Rows.Count, "L").End(xlUp).Row
Set myrange = Range("L2:L" & LastRow)
For Each c In myrange
If c.Value = "abc" Then
If Not c.Offset(0, -9).HasFormula Then
c.Offset(0, -9).Value = c.Offset(0, -9).Value * (-1)
End If
End If
Next
End Sub

Mike

"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

I should have included this to catch text

For Each c In myrange
On Error Resume Next

Mike

"Mike H" wrote:

Middling length but it doesn't destry formulas

Sub test()
Dim myrange As Range
LastRow = Cells(Cells.Rows.Count, "L").End(xlUp).Row
Set myrange = Range("L2:L" & LastRow)
For Each c In myrange
If c.Value = "abc" Then
If Not c.Offset(0, -9).HasFormula Then
c.Offset(0, -9).Value = c.Offset(0, -9).Value * (-1)
End If
End If
Next
End Sub

Mike

"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