How to split the Amount in excel formula or Excel VBA Code?

Report
Posts
28
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
May 25, 2021
-
Posts
2749
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 31, 2021
-
Dear All,

Hope you are doing well!.

I would like to copy the value in cell based on user input value. Below is the sample screenshot.

The user will enter the value in "User Input" table. Based on input value the value and the code 9999 will update the other table. Like "Expectation" table as in screen shot.

Note: The Cr amount always minus sign (-) in front of the value.



Thanks in advance,
Raj

1 reply

Posts
2749
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 31, 2021
462
Hi Raj,

The code below will change the input into expectation:
Sub RunMe()
Dim lRow, x As Integer

lRow = Range("B" & Rows.Count).End(xlUp).Row

For x = lRow To 3 Step -1
    Rows(x + 1).Insert
    Range("B" & x + 1).Value = 9999
    If Range("C" & x) <> vbNullString Then
        Range("D" & x + 1).Value = Range("C" & x).Value * -1
    End If
    If Range("D" & x) <> vbNullString Then
        Range("C" & x + 1).Value = Range("D" & x).Value
        Range("D" & x) = Range("D" & x) * -1
    End If
    Range(Cells(x + 1, "B"), Cells(x + 1, "D")).Interior.ColorIndex = 6
Next x
End Sub


Not sure if you wanted to create a separate table and if the inputted values in column D could also be negative.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!