Improve VBA

[Kazdima]

Hello,

would you please improve the macro? The issue arises when I have numbers in both columns (A and B) in Debit and Credit.
For example In Debit 1,000 and in Credit 1,200. So, in column A should be (200) after running a Macro.

Sub MoveCredits_v3()
With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
.Value = Evaluate(Replace(Replace("if(#="""","""",if(#=0,-^,#))", "#", .Address), "^", .Offset(, 1).Address))
.Offset(, 1).Clear '<- Optional to clear Credit column
End With
End Sub

 

[Fluff]

I haven't supplied a formula, I have only stated what you code is doing!
If you simply want to deduct col A from col B then try

Sub Kazdima()
   With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate("if({1}," & .Offset(, 1).Address & "-" & .Address & ")")
   End With
End Sub

 

[Kazdima]

Not deduct, but add. Column A is always "+", column "B" is always "-". So whichever number is higher that sign prevails. Exactly, like I explained in my first posting

 

[Fluff]

Exactly, like I explained in my first posting

Whilst you may think that this is true, please bear in mind that I cannot see your worksheet, not can I read your mind.
It was not clear to me what you were asking for & the code you supplied was doing nothing like what you wanted. That is why I tried to get clarification from you, before wasting my time doing something you did not want.
So, simply put, you want to add column A & column B without any criteria, with the result in column A. Is that correct?

 

[Kazdima]

Please see an example, may it is more clear-
[TABLE="width: 321"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B[/TD]
[TD] Should be in Column A [/TD]
[/TR]
[TR]
[TD]500.00
[/TD]
[TD]200.00[/TD]
[TD] 300.00[/TD]
[/TR]
[TR]
[TD]20,000.00[/TD]
[TD]25,313.93[/TD]
[TD]- 5,313.93[/TD]
[/TR]
[TR]
[TD]300.00[/TD]
[TD]800.00[/TD]
[TD]- 500.00[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]90,000.00[/TD]
[TD]- 90,000.00[/TD]
[/TR]
[TR]
[TD]0.00
[/TD]
[TD]28,865.00[/TD]
[TD]- 28,865.00[/TD]
[/TR]
[TR]
[TD]50,000.00[/TD]
[TD]0.00[/TD]
[TD] 50,000.00
[/TD]
[/TR]
[TR]
[TD]150.00[/TD]
[TD]180.00[/TD]
[TD]- 30.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

Try this:

Sub Results()
'Modified 4/30/2019 2:15:32 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
Cells(i, 1).Value = Cells(i, 1).Value - Cells(i, 2).Value
Next
Application.ScreenUpdating = True
End Sub

 

[Fluff]

That example shows that you want to subtract col B from col A, not add.
In which case try

Sub Kazdima()
   With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate("if({1}," & .Address & "-" & .Offset(, 1).Address & ")")
   End With
End Sub

However if col B numbers are all negative try

Sub Kazdima()
   With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate("if({1}," & .Address & "+" & .Offset(, 1).Address & ")")
   End With
End Sub

 

[Kazdima]

Thank you, Fluff. The first one is working-

 

[My Aswer Is This]

In Post 12 you said:

Column A is always "+"

But in Post 14 your showing the results in column A will be a negative.
​

 

[Fluff]

Thank you, Fluff. The first one is working-

You're welcome & thanks for the feedback