Turn +ve to -ve & vice versa.

[Sinner]

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

 

[cht13er]

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

 

[Mike H]

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

 

[Mike H]

I should have included this to catch text

For Each c In myrange
On Error Resume Next

Mike