Move data from Debit to Credit side

Panoos64 · Nov 21, 2018

Hi all, kindly advice me for a VBA code so that to run through col. “E” and where find the codes which are in below extract, should transfer them from “Credit” side col.”J” to “Debit” col. “I”. Please see below original data and expected result.

Thanking you in advance

Original data
 [TABLE="class: MsoNormalTable, width: 472"]
<tbody>[TR]
[TD="width: 87"][/TD]
[TD="width: 132"]

[FONT=&quot]E[/FONT]

[/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 225"]

[FONT=&quot]H[/FONT]

[/TD]
[TD="width: 125"]

[FONT=&quot]I[/FONT]

[/TD]
[TD="width: 122"]

[FONT=&quot]J[/FONT]

[/TD]
[/TR]
[TR]
[TD="width: 87"]

[FONT=&quot]1[/FONT]

[/TD]
[TD="width: 132"]

[FONT=&quot]Account[/FONT]

[/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 225"]

[FONT=&quot]Description[/FONT]

[/TD]
[TD="width: 125"]

[FONT=&quot]Debit[/FONT]

[/TD]
[TD="width: 122"]

[FONT=&quot]Credit[/FONT]

[/TD]
[/TR]
[TR]
[TD="width: 87"]

[FONT=&quot]2[/FONT]

[/TD]
[TD="width: 132"]

[FONT=&quot]405010805[/FONT]

[/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 225"]

[FONT=&quot]FOOD COST [/FONT]

[/TD]
[TD="width: 125"][/TD]
[TD="width: 122"]

[FONT=&quot]25,120.38[/FONT]

[/TD]
[/TR]
[TR]
[TD="width: 87"]

[FONT=&quot]3[/FONT]

[/TD]
[TD="width: 132"]

[FONT=&quot]405010830[/FONT]

[/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 225"]

[FONT=&quot]BEVERAGE COST[/FONT]

[/TD]
[TD="width: 125"][/TD]
[TD="width: 122"]

[FONT=&quot]15,452.35[/FONT]

[/TD]
[/TR]
[TR]
[TD="width: 87"]

[FONT=&quot]14[/FONT]

[/TD]
[TD="width: 132"]

[FONT=&quot]505010805[/FONT]

[/TD]
[TD="width: 48"]

[FONT=&quot] [/FONT]

[/TD]
[TD="width: 48"]

[FONT=&quot] [/FONT]

[/TD]
[TD="width: 225"]

[FONT=&quot]OTHER COST BEVERAGE[/FONT]

[/TD]
[TD="width: 125"]

[FONT=&quot] [/FONT]

[/TD]
[TD="width: 122"]

[FONT=&quot]5,658.25[/FONT]

[/TD]
[/TR]
[TR]
[TD="width: 87"]

[FONT=&quot]15[/FONT]

[/TD]
[TD="width: 132"]

[FONT=&quot]505010830[/FONT]

[/TD]
[TD="width: 48"]

[FONT=&quot] [/FONT]

[/TD]
[TD="width: 48"]

[FONT=&quot] [/FONT]

[/TD]
[TD="width: 225"]

[FONT=&quot]OTHER COST FOOD[/FONT]

[/TD]
[TD="width: 125"]

[FONT=&quot] [/FONT]

[/TD]
[TD="width: 122"]

[FONT=&quot]4,786.48[/FONT]

[/TD]
[/TR]
</tbody>[/TABLE]

Expected result

[TABLE="class: MsoNormalTable, width: 472"]
<tbody>[TR]
[TD="width: 87"][/TD]
[TD="width: 132"]

[FONT=&quot]E[/FONT]

[/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 225"]

[FONT=&quot]H[/FONT]

[/TD]
[TD="width: 125"]

[FONT=&quot]I[/FONT]

[/TD]
[TD="width: 122"]

[FONT=&quot]J[/FONT]

[/TD]
[/TR]
[TR]
[TD="width: 87"]

[FONT=&quot]1[/FONT]

[/TD]
[TD="width: 132"]

[FONT=&quot]Account[/FONT]

[/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 225"]

[FONT=&quot] Description[/FONT]

[/TD]
[TD="width: 125"]

[FONT=&quot]Debit[/FONT]

[/TD]
[TD="width: 122"]

[FONT=&quot]Credit[/FONT]

[/TD]
[/TR]
[TR]
[TD="width: 87"]

[FONT=&quot]2[/FONT]

[/TD]
[TD="width: 132"]

[FONT=&quot]405010805[/FONT]

[/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 225"]

[FONT=&quot]FOOD COST [/FONT]

[/TD]
[TD="width: 125"]

[FONT=&quot]25,120.38[/FONT]

[/TD]
[TD="width: 122"][/TD]
[/TR]
[TR]
[TD="width: 87"]

[FONT=&quot]3[/FONT]

[/TD]
[TD="width: 132"]

[FONT=&quot]405010830[/FONT]

[/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 225"]

[FONT=&quot]BEVERAGE COST[/FONT]

[/TD]
[TD="width: 125"]

[FONT=&quot]15,452.35[/FONT]

[/TD]
[TD="width: 122"][/TD]
[/TR]
[TR]
[TD="width: 87"]

[FONT=&quot]14[/FONT]

[/TD]
[TD="width: 132"]

[FONT=&quot]505010805[/FONT]

[/TD]
[TD="width: 48"]

[FONT=&quot] [/FONT]

[/TD]
[TD="width: 48"]

[FONT=&quot] [/FONT]

[/TD]
[TD="width: 225"]

[FONT=&quot]OTHER COST BEVERAGE[/FONT]

[/TD]
[TD="width: 125"]

[FONT=&quot]5,658.25[/FONT]

[/TD]
[TD="width: 122"]

[FONT=&quot] [/FONT]

[/TD]
[/TR]
[TR]
[TD="width: 87"]

[FONT=&quot]15[/FONT]

[/TD]
[TD="width: 132"]

[FONT=&quot]505010830[/FONT]

[/TD]
[TD="width: 48"]

[FONT=&quot] [/FONT]

[/TD]
[TD="width: 48"]

[FONT=&quot] [/FONT]

[/TD]
[TD="width: 225"]

[FONT=&quot]OTHER COST FOOD[/FONT]

[/TD]
[TD="width: 125"]

[FONT=&quot]4,786.48[/FONT]

[/TD]
[TD="width: 122"]

[FONT=&quot] [/FONT]

[/TD]
[/TR]
</tbody>[/TABLE]

My Aswer Is This · Nov 21, 2018

The only different's I see in these two images is that all values have been moved From Debit to credit.

What was the determining factor that made them move.
Other wise if there is none why can you not just copy and paste.

Panoos64 · Nov 21, 2018

Hi My Asw, apologies for any missing information on my extract. Between rows there are several codes in col. "E" and in adjacent cells in col. "J" there are the respectively amounts. I would like the VBA code to run through col. "E", selecting only the below codes and any amount which is in adjacent cells in col. "J", Credit side, it should move them in col. "I" Debit side. The codes which vba should select are follows:

-405010805
-405010830
-505010805
-505010830

Thank you for your support

My Aswer Is This · Nov 21, 2018

So again your wanting to move them all over.
You only have four and you want to move four correct

Row 1 2 3 14 and 15

So will it always be the same rows if not how will we know what rows next time?

Fluff · Nov 21, 2018

Maybe

Code:

Sub MoveDebit()
   Dim Cl As Range
   
   For Each Cl In Range("E2", Range("E" & Rows.Count).End(xlUp))
      Select Case Cl.Value
         Case 405010805, 405010830, 505010805, 505010830
            Cl.Offset(, 4).Value = Cl.Offset(, 5).Value
            Cl.Offset(, 5).ClearContents
      End Select
   Next Cl
End Sub

My Aswer Is This · Nov 21, 2018

So each time you run the script these same numbers will always be in column E for each row you want affected?

Panoos64 · Nov 22, 2018

Hi my Asw, The code works perfect and base on my data. Just for explanation the rows are not stably and are change each time when i download the data from payroll system. The rows which should transfer the amounts on my above example were 2,3,14,15. The row 1 just is the heading. However your code works perfect and is what i was require. I am obligate to you and i say a great thanks for your time spent for my project. Hv a great, lovely day

My Aswer Is This · Nov 22, 2018

We are glad you have this working for you. I never provided the code. You will note Fluff provided the code.
My question was what happens next time you run the code. Will we always be looking for those same 4 numbers. Those values are what we call hard coded into the script. So to change them you would have to change the script. I was thinking if they will always be changing you would not want to keep modifying the code and there could be a easier way to do this if you wanted that. If you want more help let us know. Thanks for your kind comments. You have a nice day and weekend.

Panoos64 said:
Hi my Asw, The code works perfect and base on my data. Just for explanation the rows are not stably and are change each time when i download the data from payroll system. The rows which should transfer the amounts on my above example were 2,3,14,15. The row 1 just is the heading. However your code works perfect and is what i was require. I am obligate to you and i say a great thanks for your time spent for my project. Hv a great, lovely day

Panoos64 · Nov 22, 2018

Apologies Fluf, i was looking to the last note and i saw My Asw query. However i would like to say my thanks to you personally, once again for your support to me and your spending time for my project. Hv a great day and also a great weekend.

Fluff · Nov 22, 2018

You're welcome

Move data from Debit to Credit side

Panoos64

Well-known Member

Excel Facts

My Aswer Is This

Well-known Member

Panoos64

Well-known Member

My Aswer Is This

Well-known Member

Fluff

MrExcel MVP, Moderator

My Aswer Is This

Well-known Member

Panoos64

Well-known Member

My Aswer Is This

Well-known Member

Panoos64

Well-known Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Move data from Debit to Credit side

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP, Moderator

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock