Prefix with "-" sign

[sindhuja]

Hi,
I need to prefix with “-“ sign for the visible cells in column Z.
Eg : 1234.00 should be as -1234.00

I need this because if I find a negative value in column S then the corresponding value in column Z should be negative. Else no need to change of sign.

Can this be done using coding.

-Sindhuja

[Kenneth Hobs]

Right click the sheet tab, View Code, and paste. Cut and paste the current range to update all. After that, any change will automatically happen.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range, nFormat As String
Set r = Intersect(Range("Z2", Range("Z" & Rows.Count).End(xlUp)), Target)
If r Is Nothing Then Exit Sub

Application.EnableEvents = False

For Each c In r
With c
If .Hidden = False And .Value > 0 Then .Value = .Value * -1
NextC:
End With
Next c

Application.EnableEvents = False
End Sub[/VBA]

[sindhuja]

Thank you so much for the quick response.

The results are not expected. The values in column Z are still the same though the value in column S is negative.

i tried in the sheet change event as well as sperate code. Instead of sheet change event can this be done seperately as i need to add the new sheet with different name (sheet not constant).

[Kenneth Hobs]

Why do you need code for that? Just use an IF formula.

Change events are only change based on your cell value changing manually, not by formula. If don't really want to use a formula, then the intersect range would be by column S and then set the value in column Z. The code would be similar to what I posted earlier with minor changes.

[CodeNinja]

You could use something like this:

[VBA]Sub test()
Dim i As Integer
For i = 1 To Sheet1.Range("S65536").End(xlUp).Row
If Sheet1.Cells(i, "S") < 0 Then
Sheet1.Cells(i, "Z") = -1 * Abs(Sheet1.Cells(i, "Z"))
End If
Next i
End Sub
[/VBA]

[sindhuja]

I got an error message in the below line

Sheet1.Cells(i, "AR") = -1 * Abs(Sheet1.Cells(i, "AR"))

i tried changing the sheet1 as Details but also i didnt work out. Can you please assist.

'Coding to be applied only for the filtered rows

[Kenneth Hobs]

Your first post referenced column S and Z. Your 2nd post referenced column AR.

For the first post, if column S is the manually entered data then use this code as I explained first.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range, nFormat As String
Set r = Intersect(Range("S2", Range("S" & Rows.Count).End(xlUp)), Target)
If r Is Nothing Then Exit Sub

Application.EnableEvents = False

For Each c In r
With c
If .Hidden = False And .Value < 0 Then Range("Z" & .Row).Value = Range("Z" & .Row).Value * -1
NextC:
End With
Next c

Application.EnableEvents = False
End Sub[/VBA]

[CodeNinja]

I got an error message in the below line

Sheet1.Cells(i, "AR") = -1 * Abs(Sheet1.Cells(i, "AR"))

i tried changing the sheet1 as Details but also i didnt work out. Can you please assist.

try Sheets("Details").cells blah blah blah

[mikerickson]

Perhaps
[VBA]Sheet1.Cells(i, "Z") = -1 * Abs(Val(CStr(Sheet1.Cells(i, "Z").Value)))[/VBA]

[sindhuja]

Still am facing issues with the formula. Can i get a guidance. Though the values are negative the column value Z is not changing.

[Kenneth Hobs]

What formula? What values? You have been given two vba solutions.

Try posting an example workbook if you are having problems explaining what you need.

[CodeNinja]

Sindhuja,
You are giving us conflicting information, but we are doing our best to help you. It is unclear if you want column Z to be changed to negative or if you want column AR to be changed or both. It is unclear if you want a VBA solution or a formula solution.

I will make the following assumptions as an additional effort to help you.

1- You want a formula solution now.
2- You want column Z to be the end result
3- You will have Column S be the number on which the negative value is based.
4- You want column Z to be an independent number from column S

To accomplish this without any code (just formulas) you will need a dummy column. A formula without VBA cannot manipulate data in its own cell.. the cell will either have a formula or data, but not both. ***If I am wrong about that Kenneth, please educate me*** Lets use column Y for your dummy column. This is the column the user will enter the data into, and the result will be displayed in column Z.

So... Column S has our negative determinant, column Y is our user input, and column Z is our End result...

Copy this formula into Z2 and then copy and paste down the remainder of the z column:
=IF(S2<0,ABS(Y2) *-1,Y2)

Attached is a spreadsheet as an example.

Hope this is of help.

then copy that formula down column z

[sindhuja]

Sorry for the confusion. It was actually column S in which we need to find for the negative value and change the value of Z accordingly.

Formula was working fine. My problem here is am usingng various codes into single project
[vba]
My_Range.AutoFilter Field:=9, Criteria1:=Array("Test"), Operator:=xlFilterValues
My_Range.AutoFilter Field:=19, Criteria1:="<0", Operator:=xlFilterValues
Dim lastrw As Long
lastrw = Sheets("Details").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Details").Range("Y2:Y" & lastrw).FormulaR1C1 = "=IF(RC[-6]<0,ABS(RC[-1]) *-1,RC[-1])"
[/vba]

This worked fine. Results are as expected.
My problem here is I want to apply this formula to the visible cells alone.

But i need the results in the column Y only that too for the filtered values. All other categories in the column 9 differs with the column Y values.

[CodeNinja]

Try something like this:

[vba]
Dim lastrw As Long
Dim rng As Range

My_Range.AutoFilter Field:=9, Criteria1:=Array("Test"), Operator:=xlFilterValues
My_Range.AutoFilter Field:=19, Criteria1:="<0", Operator:=xlFilterValues

lastrw = Sheets("Details").Range("A" & Rows.Count).End(xlUp).Row

Set rng = Sheets("Details").Range("Y2:Y" & lastrw).SpecialCells(xlCellTypeVisible)
rng.FormulaR1C1 = "=IF(RC[-6]<0,ABS(RC[-1]) *-1,RC[-1])"
'Sheets("Details").Range("Y2:Y" & lastrw).FormulaR1C1 = "=IF(RC[-6]<0,ABS(RC[-1]) *-1,RC[-1])"

[/vba]