Hi,
Can somebody help with a macro doing the following:

If cell in column A = "Test"
Then turn positive values to negative values in column B+C in corresponding
row
If cell in column A <"Test" then do nothing
If cell in column B or/and cell in column C already has a negative value
then do nothing

Best regards,
Kaj Pedersen

Hi Kaj,

Am Mon, 19 Jun 2017 15:06:54 GMT schrieb :

If cell in column A = "Test"
Then turn positive values to negative values in column B+C in corresponding
row
If cell in column A <"Test" then do nothing
If cell in column B or/and cell in column C already has a negative value
then do nothing

try:

Sub Test()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
If rngC = "Test" Then
rngC.Offset(, 1) = IIf(rngC.Offset(, 1) 0, rngC.Offset(, 1) * -1, rngC.Offset(, 1))
rngC.Offset(, 2) = IIf(rngC.Offset(, 2) 0, rngC.Offset(, 2) * -1, rngC.Offset(, 2))
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus,
Your suggestion did exactly what I asked for. Thank you for that.
However, I have problems to figure out how to change the macro if I want the
following:

"Test" in column B
Values to turn into negative in columns F and H

Can you help once more?
Thank you.


Best regards,
Kaj Pedersen

Hi Kaj,

Am Mon, 19 Jun 2017 18:52:09 GMT schrieb :

"Test" in column B
Values to turn into negative in columns F and H

then try:

Sub Test()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(.Rows.Count, 2).End(xlUp).Row
For Each rngC In Range("B1:B" & LRow)
If rngC = "Test" Then
rngC.Offset(, 4) = IIf(rngC.Offset(, 4) 0, rngC.Offset(, 4) * -1, rngC.Offset(, 4))
rngC.Offset(, 6) = IIf(rngC.Offset(, 6) 0, rngC.Offset(, 6) * -1, rngC.Offset(, 6))
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus.

Thank you so much for your help. Everything is now to my full satisfaction.
Changing the columns also enabled me to understand how the macro is built,
so now I can easily change columns to what I want.

Regards,
Kaj Pedersen

Hi Kaj,

Am Mon, 19 Jun 2017 20:02:22 GMT schrieb :

Thank you so much for your help. Everything is now to my full satisfaction.
Changing the columns also enabled me to understand how the macro is built,
so now I can easily change columns to what I want.

you are welcome. I am always glad to help.


Regards
Claus B.
--
Windows10
Office 2016