Turn +ve to -ve & vice versa.
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 |
Turn +ve to -ve & vice versa.
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 |
Turn +ve to -ve & vice versa.
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 |
Turn +ve to -ve & vice versa.
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 |
Turn +ve to -ve & vice versa.
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 |
All times are GMT +1. The time now is 08:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com