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

[Solved]
Report
Posts
31
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
August 5, 2021
-
Posts
2811
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 27, 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
2811
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 27, 2021
484
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