Value sign change automatically in Same Column Basis Criteria - VBA

[anaysha]

Hi Team,

Can you please support here, as we are getting data from different teams and we need to club in one file and all value initially we received as Positive and later we need change the value in same column basis its nature

Like : 1) if Category is "Opening" then value reflect as "Positive" 2) if category is "Revenue" then value reflect as "negative", etc. (refer below excel),

need your support here through VBA whenever we put the value data in same column change basis category defined.

Value change impact.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L
4
5		Category	Value		Legends
6		Opening	100		Opening	Value shuold be in Positive
7		OI	10		OI	Value shuold be in Positive
8		Revenue	-90		Revenue	Value shuold be in Negative
9		OI	89		Cancel	Value shuold be in Negative
10		Cancel	-8
11		Revenue	-98
12
13
14
15		Intially We will put "Value" (all positive) manually in Column "C" and later basis "Category" value automatically change as per legends defined in same value column.
16		-- Like if Category is "Opening" then value refelect as "Positive" if category is "Revenue" then value refelect as "negative", etc.
17
18
19
Sheet1

 

[anaysha]

Hi Team,

Can you please support here, as we are getting data from different teams and we need to club in one file and all value initially we received as Positive and later we need to change the value in same column basis its nature.

Like : 1) if Category is "Opening" then value reflect as "Positive" 2) if category is "Revenue" then value reflect as "negative", etc. (refer below excel),

also, append query by considering these facts:

a) if "value" column - value is already same as per legend then no change.
b) if "Category" is blank then value should reflect as positive.
c) if "Category" mentioned then value should reflect as per legend.

Please support here through VBA Script, whenever we put the value then value will automatically change in same column basis category defined.

Value change impact.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M
3
4
5		Category	Value		Legends
6		Opening	100		Opening	Value shuold be in Positive
7		OI	10		OI	Value shuold be in Positive
8		Revenue	-90		Revenue	Value shuold be in Negative
9		OI	89		Cancel	Value shuold be in Negative
10		Cancel	-8
11		Revenue	-98
12
13
14
15		Intially We will put "Value" (all positive) manually in Column "C" and later basis "Category" value automatically change as per legends defined in same value column.
16		-- Like if Category is "Opening" then value refelect as "Positive" if category is "Revenue" then value refelect as "negative", etc.
17
Sheet1

 

[anaysha]

I have tried to create the macro..but all other condition unable to fix, please help.. like if "Category" change from "Revenue" to OI then value will done "Positive" automatically.. but thru below code it is not working

Sub Change_value()
Dim i As Long
Dim j As Long
Dim lastrow As Long
Dim Sharr As Variant
Dim ci As Long
Sharr = Array("Revenue", "Cancel")
lastrow = Range("A" & Rows.Count).End(xlUp).Row

For ci = 0 To UBound(Sharr)

For i = 6 To lastrow

If ActiveSheet.Range("A" & i).Value = Sharr(ci) Then
If Sgn(ActiveSheet.Range("b" & i).Value) > 0 Then
ActiveSheet.Range("b" & i).Value = ActiveSheet.Range("b" & i).Value * -1

End If

End If

Next i

Next ci

end sub

 

[anaysha]

Hi Team, please help me out here. as i need to apply the same in my working.

 

[Anthony47]

Hummmm...
Do you mean that values in column C are all typed as positive numbers, then someone adds the Category and at that time column C needs to be adjusted, converting some numbers to negative and making sure that others be positive, depending on the "legend" in columns E:F?

 

[anaysha]

Hummmm...
Do you mean that values in column C are all typed as positive numbers, then someone adds the Category and at that time column C needs to be adjusted, converting some numbers to negative and making sure that others be positive, depending on the "legend" in columns E:F?

Yes you are correct, but only one thing that I would like to add...both column data will be plotted in same time and the output will change in same column of i.e. "Value" as per the legend defined once data plotted, legend can be 2 or more, so that flexibility also required, please support.

 

[bebo021999]

Below code put in worksheet events, trigger change in column B & C
(How to set: right click tab's name, View Code, then paste code into)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, i&, cat As String, sign
lr = Cells(Rows.Count, "B").End(xlUp).Row 'last used cell in column B
If Intersect(Target, Range("B6:C" & lr)) Is Nothing Then Exit Sub ' trigger B6:C last row change only
Application.EnableEvents = False ' swith off event, to prevent change event active
For i = 6 To lr
    With Cells(i, "C")
        cat = .Offset(0, -1).Value
        Select Case UCase(cat)
            Case "REVENUE", "CANCEL"
                sign = -1
            Case Else
                sign = 1
        End Select
        .Value = Abs(.Value) * sign
    End With
Next
Application.EnableEvents = True ' swith on event
End Sub

Now change value in B:C to see number in C changed.

 

[anaysha]

Below code put in worksheet events, trigger change in column B & C
(How to set: right click tab's name, View Code, then paste code into)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, i&, cat As String, sign
lr = Cells(Rows.Count, "B").End(xlUp).Row 'last used cell in column B
If Intersect(Target, Range("B6:C" & lr)) Is Nothing Then Exit Sub ' trigger B6:C last row change only
Application.EnableEvents = False ' swith off event, to prevent change event active
For i = 6 To lr
    With Cells(i, "C")
        cat = .Offset(0, -1).Value
        Select Case UCase(cat)
            Case "REVENUE", "CANCEL"
                sign = -1
            Case Else
                sign = 1
        End Select
        .Value = Abs(.Value) * sign
    End With
Next
Application.EnableEvents = True ' swith on event
End Sub

Now change value in B:C to see number in C changed.

Thank you so much, this is really appreciable...once again thank you so much...