check for negative and change the value in next column

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
Hi every one ,

i am trying to search for negative value in column A and if negative value is found

then the value in next column(i.e, Column C ) should be checked and replaced if the value is 40 then to 50 or if the value is 50 then to 40 .

And even i need to change the negative value to positive and can we round off the decimal value .

Below is the sample workbook.

Regards,
Dhruva.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Upvote 0
If you wanted to replace you original data with the changes, you could try something like this. This code will change your data so please test on a backup copy of your data.

VBA Code:
Sub Neg_to_Pos()

    Dim lRow As Long, x As Long
    Dim dat
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    ReDim dat(1 To lRow, 1 To 3)
    dat = Range("A1:C" & lRow)
    For x = 1 To UBound(dat)
        dat(x, 1) = Round(dat(x, 1), 0)
        If Not dat(x, 1) > 0 Then
            If dat(x, 3) = 40 Then dat(x, 3) = 50 Else dat(x, 3) = 40
            dat(x, 1) = dat(x, 1) * -1
        End If
    Next
    Range("a1").Resize(UBound(dat, 1), UBound(dat, 2)) = dat
    
End Sub
 
Upvote 0
If you wanted to replace you original data with the changes, you could try something like this. This code will change your data so please test on a backup copy of your data.

VBA Code:
Sub Neg_to_Pos()

    Dim lRow As Long, x As Long
    Dim dat
   
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    ReDim dat(1 To lRow, 1 To 3)
    dat = Range("A1:C" & lRow)
    For x = 1 To UBound(dat)
        dat(x, 1) = Round(dat(x, 1), 0)
        If Not dat(x, 1) > 0 Then
            If dat(x, 3) = 40 Then dat(x, 3) = 50 Else dat(x, 3) = 40
            dat(x, 1) = dat(x, 1) * -1
        End If
    Next
    Range("a1").Resize(UBound(dat, 1), UBound(dat, 2)) = dat
   
End Sub
Thanks and can you please explain me the code, so that i can learn.

Regards,
Dhruva
 
Upvote 0
Hi, I changed the code to fit your new data. I also wrote comments in the code to tell you what is going on. If the comments are unclear then please post back for a further explanation.

Thanks for the feedback! We both were happy to help you.

VBA Code:
Sub Neg_to_Pos()

    Dim lRow As Long, x As Long
    Dim dat
    
' Find last row of data in Column C ("3")
    lRow = Cells(Rows.Count, 3).End(xlUp).Row

' Redimension the Array named "dat" to the size of your data range
    ReDim dat(1 To lRow, 1 To 5)

' Assign the values from your data to the array "dat"
    dat = Range("C1:G" & lRow)
    
' Use For/Next loop to go through the data to make the required changes
    For x = 1 To UBound(dat)
        dat(x, 5) = Round(dat(x, 5), 0)  'Round Column G to no decimal places
        
' Use IF/THEN logic to determine the correct value for Column C
        If Not dat(x, 5) > 0 Then   'If Column G is negative then change, if not then skip
            If dat(x, 1) = 40 Then dat(x, 1) = 50 Else dat(x, 1) = 40   'Make change to value
            dat(x, 5) = dat(x, 5) * -1  'Change the negative to positive, you could also use the ABS function.
        End If
    Next
    
'Write the array Dat to your worksheet starting at Cell C1
    Range("C1").Resize(UBound(dat, 1), UBound(dat, 2)) = dat
    
End Sub
 
Upvote 0
@DanteAmor Sorry sir again i forgot inform that i required in VBA
Regards,
Dhruva
No problem, here I put the macro for what you need.
Basically it does the same, in the same column C, it changes to 50 if the value of G is negative, and in the same column G puts the positive value rounded.

VBA Code:
Sub check_negative()
  Dim lr As Long
  lr = Range("C" & Rows.Count).End(xlUp).Row
  With Range("C1:C" & lr)
    .Value = Evaluate("=IF({1},IF(" & .Offset(, 4).Address & "<0,50," & .Address & "))")
  End With
  With Range("G1:G" & lr)
    .Value = Evaluate("=IF({1},ABS(ROUND(" & .Address & ",0)))")
  End With
End Sub
 
Upvote 0
Here is a completely different way to write the macro...
VBA Code:
Sub check_negative()
  Columns("G").Replace "-", "X", xlPart, , , , False, False
  On Error GoTo NoMinusValues
  With Columns("G").SpecialCells(xlConstants, xlTextValues)
    .Offset(, -4) = 50
    .Replace "X", ""
  End With
NoMinusValues:
End Sub
If you KNOW that you will NEVER run the code when there are no minus values to find, you can remove the error trap and compact the code slightly...
Code:
Sub check_negative()
  Columns("G").Replace "-", "X", xlPart, , , , False, False
  With Columns("G").SpecialCells(xlConstants, xlTextValues)
    .Offset(, -4) = 50
    .Replace "X", ""
  End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top