Macro Help: Numbers to Negative sign, Moving Columns for all tabs within workbook

N

night1218

New Member
Joined
Oct 3, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Item StatusCategorySetItem IDSection codeInboxEntry/TradeStmt DateValue DateItem TypeCCYAmountUSD Base Amount
OutstandingN AccountUSD0012,015,896,203.00N AAN Test Inbox
15/7/2021​
15/7/2021​
15/7/2021​
L CRUSD
1000.00​
1000.00​
OutstandingN AccountUSD0012,015,896,204.00N AAN Test Inbox
15/7/2021​
15/7/2021​
15/7/2021​
L CR WOUSD
0.24​
0.24​
OutstandingN AccountUSD0012,015,896,205.00N AAN Test Inbox
15/7/2021​
15/7/2021​
15/7/2021​
L DRUSD
80235.00​
80235.00​
OutstandingN AccountUSD0012,015,896,206.00N AAN Test Inbox
15/7/2021​
15/7/2021​
15/7/2021​
L DR WOUSD
1.20​
1.20​
OutstandingN AccountUSD0012,015,896,207.00N AAN Test Inbox
15/7/2021​
15/7/2021​
15/7/2021​
L CR NETTUSD
84.00​
84.00​
OutstandingN AccountUSD0012,015,896,208.00N AAN Test Inbox
15/7/2021​
15/7/2021​
15/7/2021​
L DR NETTUSD
1206.50​
1206.50​
OutstandingN AccountUSD0012,015,896,209.00N AAN Test Inbox
15/7/2021​
15/7/2021​
15/7/2021​
S CRUSD
922.06​
922.06​
OutstandingN AccountUSD0012,015,896,210.00N AAN Test Inbox
15/7/2021​
15/7/2021​
15/7/2021​
S DRUSD
10384.34​
10384.34​

Need help on the above, what i'm trying to do is:-
1)Depend on the "Item Type"("L DR, L DR NETT, L DR WO" only), change the numbers under "Amount" from positive to negative

After (1), i need to cut entire column from column "AU" to column"D" ( i hope the below code is correct..)

VBA Code: 
Columns("AU:AU").Select
    Application.CutCopyMode = False
    Selection.Cut
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Columns("D:E").Select
    Selection.NumberFormat = "0"

2) Repeat the same to all tabs under the same workbook (Each tabs have different numbers of rows)
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
What columns are those values in?

In future please use the XL2BB add-in to post samples.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
ahh..sorry..i thought i was doing it right, now i get it.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAU
1Item StatusCategorySet Item ID Section codeInboxEntry/TradeStmt DateValue DateItem TypeCCYAmountUSD Base AmountRef 1Ref 2Ref 3AgeDaysHrsRoot CauseLast UserLast ActionLast Audit Last NoteDocFunctional AmountFuntional CurrencyCorp Reporting AmountCorp Reporting CcyQuantityAsset Known or UnknownAsset IDAsset CodeAsset DescPriceCCYRef 4Item OwnItem Own LongItem ReasonItem ResolutionTran CodeProductInputFeed IDFeed Code Ex Count
2OutstandingN AccountUSD001########N AAN Test Inbox########################L CRUSD1000.001000.00XXXXXX117########
3OutstandingN AccountUSD001########N AAN Test Inbox########################L CR WOUSD0.240.24XXXXXXXXX117########
4OutstandingN AccountUSD001########N AAN Test Inbox########################L DR USD################XXXXXXXXX117########
5OutstandingN AccountUSD001########N AAN Test Inbox########################L DR WOUSD1.201.20XXXXXXXXX117########
6OutstandingN AccountUSD001########N AAN Test Inbox########################L CR NETTUSD84.0084.00XXXXXXXXX117########
7OutstandingN AccountUSD001########N AAN Test Inbox########################L DR NETTUSD1206.501206.50XXXXXX117########
8OutstandingN AccountUSD001########N AAN Test Inbox########################S CRUSD922.06922.06XXXXXX117########
9OutstandingN AccountUSD001########N AAN Test Inbox########################S DRUSD################XXXXXXXXX117########
Sheet1
 
Upvote 0
Sorry, again.. i cant find the edit button to edit my last message..expanded all cell with ##### in it..

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAU
1Item StatusCategorySet Item ID Section codeInboxEntry/TradeStmt DateValue DateItem TypeCCYAmountUSD Base AmountRef 1Ref 2Ref 3AgeDaysHrsRoot CauseLast UserLast ActionLast Audit Last NoteDocFunctional AmountFuntional CurrencyCorp Reporting AmountCorp Reporting CcyQuantityAsset Known or UnknownAsset IDAsset CodeAsset DescPriceCCYRef 4Item OwnItem Own LongItem ReasonItem ResolutionTran CodeProductInputFeed IDFeed Code Ex Count
2OutstandingN AccountUSD0012,015,896,203.00N AAN Test Inbox15/7/202115/7/202115/7/2021L CRUSD1000.001000.00XXXXXX117109,778,894.00
3OutstandingN AccountUSD0012,015,896,204.00N AAN Test Inbox15/7/202115/7/202115/7/2021L CR WOUSD0.240.24XXXXXXXXX117109,778,895.00
4OutstandingN AccountUSD0012,015,896,205.00N AAN Test Inbox15/7/202115/7/202115/7/2021L DR USD80235.0080235.00XXXXXXXXX117109,778,896.00
5OutstandingN AccountUSD0012,015,896,206.00N AAN Test Inbox15/7/202115/7/202115/7/2021L DR WOUSD1.201.20XXXXXXXXX117109,778,897.00
6OutstandingN AccountUSD0012,015,896,207.00N AAN Test Inbox15/7/202115/7/202115/7/2021L CR NETTUSD84.0084.00XXXXXXXXX117109,778,898.00
7OutstandingN AccountUSD0012,015,896,208.00N AAN Test Inbox15/7/202115/7/202115/7/2021L DR NETTUSD1206.501206.50XXXXXX117109,778,899.00
8OutstandingN AccountUSD0012,015,896,209.00N AAN Test Inbox15/7/202115/7/202115/7/2021S CRUSD922.06922.06XXXXXX117109,778,900.00
9OutstandingN AccountUSD0012,015,896,210.00N AAN Test Inbox15/7/202115/7/202115/7/2021S DRUSD10384.3410384.34XXXXXXXXX117109,778,901.00
Sheet1
 
Upvote 0
Thanks for that, how about
VBA Code: 
Sub night()
   With Range("L2:L" & Range("J" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace(Replace("If(@="""","""",if((@=""L DR"")+(@=""L DR NETT"")+(@=""L DR WO""),#*-1,#))", "#", .Address), "@", .Offset(, -2).Address))
   End With
End Sub
 
Upvote 0
Fluff said:
Thanks for that, how about
VBA Code: 
Sub night()
   With Range("L2:L" & Range("J" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace(Replace("If(@="""","""",if((@=""L DR"")+(@=""L DR NETT"")+(@=""L DR WO""),#*-1,#))", "#", .Address), "@", .Offset(, -2).Address))
   End With
End Sub
Click to expand...

It worked for "L DR NETT" & "L DR WO" , "L DR" didnt. How do i add another 2 sets of "" "" in it? and if there is a way to run the macro for all tabs?
 
Upvote 0
The value in J4 has a space after it. Can you remove it
 
Upvote 0
Fluff said:
The value in J4 has a space after it. Can you remove it
Click to expand...
Worked like a charm, manage to get it work as the below.

VBA Code: 
Sub Update()
  With Range("L2:L" & Range("J" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace(Replace("If(@="""","""",if((@=""L DR"")+(@=""L DR NETT"")+(@=""L DR WO"")+(@=""S DR"") ,#*-1,#))", "#", .Address), "@", .Offset(, -2).Address))
   End With
   
   
 Columns("AU:AU").Select
    Application.CutCopyMode = False
    Selection.Cut
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Columns("D:E").Select
    Selection.NumberFormat = "0"
End Sub
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
SUMIFS or any other formula that can help
Replies
5
Views
75
Alex Blakenburg
A
1
  • Question Question
Auto filter column on vlookup update on every sheets of the workbook
Replies
1
Views
325
10ml
1
S
  • Question Question
Macro to extract Columns based on multiple criteria
Replies
6
Views
4K
Trebor76
T
K
  • Question Question
VBA Macro Transpose/Delete Rows and continue down spreadsheet with differing total number of rows
Replies
3
Views
357
johnnyL
J
A
  • Question Question
VBA Loop to clear contents of fiscal year rows in specific month columns
Replies
6
Views
652
kevin9999
K

Forum statistics

Threads
1,224,591
Messages
6,179,769
Members
452,941
Latest member
Greayliams

Share this page

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