Put amount in DEBIT,CREDIT columns based on matching part of item

O

Omran Y

Board Regular
Joined
Jul 17, 2023
Messages
59
Office Version
  1. 2013
Platform
  1. Windows
Hello
I have data for theses sheets
omrany.xlsm
ABCDE
1DATENAMEDEBITCREDITBALANCE
231/12/2023Ahmed1,000.001,000.00
331/12/2023Hamuda50,000.001,000.0049,000.00
431/12/2023Hameed3,000.005,000.00-2,000.00
531/12/2023Hamdaan2,000.00-2,000.00
631/12/2023Muneer0.000.000.00
731/12/2023Manar1,000.00-1,000.00
BVC




omrany.xlsm
ABCDE
1DATENAMEINVOICE NO CONDITIONAMOUNT
201/01/2024AhmedVN number 345666Bank withdrawal Ref No 78903,000.00
302/01/2024AhmedVN number 345667Cash withdrawal VCF NO 7890002,000.00
403/01/2024AhmedVN number 345668Bank withdrawal Ref No 78911,200.00
504/01/2024AhmedVN number 345669Cash withdrawal VCF NO 7890011,300.00
605/01/2024HamudaVN number 345670Futures purchases inv NO 234442,200.00
706/01/2024HamudaVN number 345671Cash withdrawal VCF NO 7890023,400.00
807/01/2024AhmedVN number 345672Futures purchases inv NO 234453,500.00
908/01/2024AmeerVN number 345673Futures purchases inv NO 234462,000.00
1009/01/2024AmeerVN number 345674Futures purchases inv NO 234472,001.00
SA




omrany.xlsm
ABCDE
1DATENAMEINVOICE NO CONDITIONAMOUNT
201/01/2024AhmedVN number 345666Bank deposit Ref NO 78923,000.00
302/01/2024HameedVN number 345667Bank deposit Ref NO 78932,000.00
403/01/2024AhmedVN number 345668Cash deposit VCF NO 7890031,200.00
504/01/2024HameedVN number 345669Cash deposit VCF NO 7890041,300.00
605/01/2024AhmedVN number 345670Futures Sales inv NO 234463,000.00
706/01/2024HameedVN number 345671Cash deposit VCF NO 7890052,000.00
807/01/2024AhmedVN number 345672Futures Sales inv NO 234471,200.00
908/01/2024AneenVN number 345673Futures Sales inv NO 234481,201.00
1009/01/2024AneenVN number 345668Bank deposit Ref No 78942,000.00
VS


omrany.xlsm
ABCDE
1DATENAMEINVOICE NO CONDITIONAMOUNT
201/01/2024AhmedVN number 345666Cash deposit VCF NO 7890053,000.00
302/01/2024AhmedVN number 345667Cash deposit VCF NO 7890062000
403/01/2024AhmedVN number 345668Bank deposit Ref NO 78931200
504/01/2024AhmedVN number 345669Bank deposit Ref NO 78941300
605/01/2024AhmedVN number 345670Bank deposit Ref NO 78952200
706/01/2024AhmedVN number 345671Futures Returns purchases inv NO 234463400
807/01/2024AhmedVN number 345672Futures Returns purchases inv NO 234473500
908/01/2024HameedVN number 345673Futures Returns purchases inv NO 234483501
1009/01/2024HameedVN number 345674Futures Returns purchases inv NO 234493502
1110/01/2024HameedVN number 345675Futures Returns purchases inv NO 234503503
1211/01/2024HameedVN number 345676Futures Returns purchases inv NO 234513504
1312/01/2024AnanVN number 345677Futures Returns purchases inv NO 234522000
AP


omrany.xlsm
ABCDE
1DATENAMEINVOICE NO CONDITIONAMOUNT
201/01/2024AhmedVN number 345666Bank withdrawal Ref No 78923,000.00
302/01/2024AhmedVN number 345667Bank withdrawal Ref No 78932,000.00
403/01/2024AhmedVN number 345668Futures Sales Returns inv NO 234471,200.00
504/01/2024AhmedVN number 345669Futures Sales Returns inv NO 234481,300.00
605/01/2024HamudaVN number 345670Futures Sales Returns inv NO 234493,000.00
706/01/2024HamudaVN number 345671Futures Sales Returns inv NO 234502,000.00
807/01/2024HamudaVN number 345672Futures Sales Returns inv NO 234511,200.00
908/01/2024amerVN number 345673Futures Sales Returns inv NO 234521,201.00
1009/01/2024AnanVN number 345674Futures Sales Returns inv NO 234532,000.00
1109/01/2024AmeerVN number 345675Futures purchases inv NO 234553000
SSC



omrany.xlsm
ABCDEFG
1DATENAMEINVOICE NO CONDITIONDEBITCREDITBALANCE
2
3
4
5
6
7
8
9
10
11
12
ACC



what I want:
* brings the balances from BVC sheet first
* if the amount in BALANCE column for BVC sheet is positive then will put in DEBIT column .
* if the amount in BALANCE column for BVC sheet is minus then will put in CREDIT column and covert to positive value as in Hameed, Hamdaan, Manar names
* when brings the amount from BVC sheet then will first row for each name will subtract debit from credit , the next row for the same row will change formula until finish the name( see the first row for each name and the next how calculat)
* when brings the data for names first should sort based on on column A from old date and to last date for the same name ,second sort based names in column B
* if the amount in BALANCE column for BVC sheet contains zero and there is no existed then no need show
* there is new names in sheets and there is no existed at all in BVC sheet then will show
* when there are amounts in BALANCE column for BVC sheet then will brings the date and write in column C the sheet name with date is existed in column A for BVC sheet, PREVIOUS BALANCE in column D .
* when put amounts in column DEBIT,CREDIT depends on words in column D across sheets, then will put theses(Bank withdrawal, Cash withdrawal, Futures Returns purchases, Futures Sales) in DEBIT column, will put theses(Cash deposit, Bank deposit, Futures Sales Returns, Futures purchases ) in CREDIT column .
* based on point 8 will replace with items are existed across sheets(delete after items in point 8) .
* I want macro to implementation for 10000 rows for each sheet.

the result should be
omrany.xlsm
ABCDEFG
1DATENAMEINVOICE NO CONDITIONDEBITCREDITBALANCE
231/12/2023AhmedBVC OF DATE 31/12/2023PREVIOUS BALANCE1,000.001,000.00
301/01/2024AhmedVN number 345666Bank withdrawal3,000.004,000.00
401/01/2024AhmedVN number 345666Cash deposit3,000.001,000.00
501/01/2024AhmedVN number 345666Bank withdrawal3,000.004,000.00
602/01/2024AhmedVN number 345668Bank withdrawal1,200.005,200.00
702/01/2024AhmedVN number 345667Cash deposit2,000.003,200.00
802/01/2024AhmedVN number 345667Bank withdrawal2,000.005,200.00
903/01/2024AhmedVN number 345668Bank deposit1,200.004,000.00
1003/01/2024AhmedVN number 345667Cash withdrawal2,000.006,000.00
1103/01/2024AhmedVN number 345668Bank deposit1,200.004,800.00
1203/01/2024AhmedVN number 345668Futures Sales Returns1,200.003,600.00
1304/01/2024AhmedVN number 345669Bank deposit1,300.002,300.00
1404/01/2024AhmedVN number 345669Cash withdrawal1,300.003,600.00
1504/01/2024AhmedVN number 345669Bank deposit1,300.002,300.00
1604/01/2024AhmedVN number 345669Futures Sales Returns1,300.001,000.00
1705/01/2024AhmedVN number 345670Bank deposit2,200.00-1,200.00
1805/01/2024AhmedVN number 345672Futures purchases3,500.00-4,700.00
1905/01/2024AhmedVN number 345670Bank deposit2,200.00-6,900.00
2006/01/2024AhmedVN number 345666Bank deposit3,000.00-9,900.00
2106/01/2024AhmedVN number 345671Futures Returns purchases3,400.00-6,500.00
2207/01/2024AhmedVN number 345668Cash deposit1,200.00-7,700.00
2307/01/2024AhmedVN number 345672Futures Returns purchases3,500.00-4,200.00
2408/01/2024AhmedVN number 345670Futures Sales3,000.00-1,200.00
2509/01/2024AhmedVN number 345672Futures Sales1,200.000.00
2608/01/2024AmeerVN number 345673Futures purchases2,000.00-2,000.00
2709/01/2024AmeerVN number 345674Futures purchases2,001.00-4,001.00
2809/01/2024AmeerVN number 345675Futures purchases3,000.00-7,001.00
2908/01/2024amerVN number 345673Futures Sales Returns1,201.00-1,201.00
3009/01/2024AnanVN number 345674Futures Sales Returns2,000.00-2,000.00
3112/01/2024AnanVN number 345677Futures Returns purchases2,000.000.00
3208/01/2024AneenVN number 345673Futures Sales1,201.001,201.00
3309/01/2024AneenVN number 345668Bank deposit2,000.00-799.00
3431/12/2023HamdaanBVC OF DATE 31/12/2023PREVIOUS BALANCE2,000.00-2,000.00
3531/12/2023HameedBVC OF DATE 31/12/2023PREVIOUS BALANCE2,000.00-2,000.00
3602/01/2024HameedVN number 345667Bank deposit2,000.00-4,000.00
3704/01/2024HameedVN number 345669Cash deposit1,300.00-5,300.00
3806/01/2024HameedVN number 345671Cash deposit2,000.00-7,300.00
3908/01/2024HameedVN number 345673Futures Returns purchases3,501.00-3,799.00
4009/01/2024HameedVN number 345674Futures Returns purchases3,502.00-297.00
4110/01/2024HameedVN number 345675Futures Returns purchases3,503.003,206.00
4211/01/2024HameedVN number 345676Futures Returns purchases3,504.006,710.00
4331/12/2023HamudaBVC OF DATE 31/12/2023PREVIOUS BALANCE49,000.0049,000.00
4405/01/2024HamudaVN number 345670Futures purchases2,200.0046,800.00
4506/01/2024HamudaVN number 345671Cash withdrawal3,400.0050,200.00
4605/01/2024HamudaVN number 345670Futures Sales Returns3,000.0047,200.00
4706/01/2024HamudaVN number 345671Futures Sales Returns2,000.0045,200.00
4807/01/2024HamudaVN number 345672Futures Sales Returns1,200.0044,000.00
4931/12/2023ManarBVC OF DATE 31/12/2024PREVIOUS BALANCE1,000.00-1,000.00
ACC
Cell Formulas
RangeFormula
G2,G49,G34:G35,G32,G29:G30,G26G2=E2-F2
G44:G48,G36:G42,G33,G31,G27:G28,G3:G25G3=G2+E3-F3

thanks.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
I have the following doubts:
1. Are all the sheets?
2. The results sheet has 48 records, but on the sheets you only have 46 records, you have 3 more records in the name Ahmed.
3. Can you explain, using your example, what you mean by this:
Omran Y said:
* based on point 8 will replace with items are existed across sheets(delete after items in point 8) .
Click to expand...

4. I am going to use the ACC sheet some columns, for example (AC to AI) to sort the data, then those data will be deleted, do you agree?
After the calculations the data will remain in column A to G.​

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --
 
Upvote 0
1. Are all the sheets?
Click to expand...
yes
2. The results sheet has 48 records, but on the sheets you only have 46 records, you have 3 more records in the name Ahmed.
Click to expand...
that's right and sorry about it !
should just show based what I have in sheet . this mistake show because I change data for every time without I notice that.
4. I am going to use the ACC sheet some columns, for example (AC to AI) to sort the data, then those data will be deleted, do you agree?
Click to expand...
ok
 
Upvote 0
3. Can you explain, using your example, what you mean by this:
Omran Y said:
* based on point 8 will replace with items are existed across sheets(delete after items in point 8) .
Click to expand...
👆👆👆You needed to explain it



Also, You should put the correct result in a post, otherwise, I wouldn't be able to verify the result of the macro.
 
Upvote 0
Check this.
Works with your example data, try the 10 thousand per sheet and comment.

VBA Code: 
Sub Put_Amount_In_Debit_Credit()
  Dim sh As Worksheet
  Dim i As Long, j As Long, k As Long, lr As Long, n As Long
  Dim arSh As Variant, itm As Variant, arrD As Variant, arrC As Variant
  Dim a As Variant, b As Variant
  Dim RegEx As Object
  Dim balance As Double
  Dim s1 As String, s2 As String
  
  arSh = Array("BVC", "SA", "VS", "AP", "SSC")
  For Each itm In arSh
    lr = lr + Sheets(itm).Range("A" & Rows.Count).End(3).Row
  Next
  ReDim b(1 To lr, 1 To 7)
  
  'Add balance
  Set sh = Sheets(arSh(0))
  a = sh.Range("A2", sh.Range("E" & Rows.Count).End(3)).Value2
  For i = 1 To UBound(a, 1)
    If a(i, 5) <> 0 Then
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 2)
      b(k, 3) = "BVC OF DATE " & Format(a(i, 1), "dd/mm/yyyy")
      b(k, 4) = "PREVIOUS BALANCE"
      If a(i, 5) > 0 Then
        b(k, 5) = a(i, 5)
      Else
        b(k, 6) = a(i, 5) * -1
      End If
    End If
  Next
  
  'Add all sheets
  For n = 1 To UBound(arSh)
    Set sh = Sheets(arSh(n))
    a = sh.Range("A2", sh.Range("E" & Rows.Count).End(3)).Value2
    For i = 1 To UBound(a, 1)
      k = k + 1
      For j = 1 To UBound(a, 2)
        b(k, j) = a(i, j)
        If j = 4 Then
          If Left(LCase(a(i, j)), 21) = LCase("Futures Sales Returns") Then
            b(k, j) = "C" & a(i, j)
          End If
        End If
      Next
    Next
  Next
  
  Application.ScreenUpdating = False
    Sheets("ACC").Select
    Range("A2:G" & Rows.Count).ClearContents
    Range("A2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
    lr = Range("A" & Rows.Count).End(3).Row
    With Range("A2:G" & lr)
      .Sort Range("B2"), xlAscending, Range("A2"), , xlAscending, Header:=xlNo
      a = .Value
    End With
    
    arrD = "@bank withdrawal|@cash withdrawal|@futures returns purchases|@futures sales"
    arrC = "@cash deposit|@bank deposit|@cfutures sales returns|@futures purchases"
    'added cfutures sales returns to differentiate from futures sales
    
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True
    RegEx.Pattern = arrD
    
    For i = 1 To UBound(a, 1)
      If a(i, 4) <> "PREVIOUS BALANCE" Then
        RegEx.Pattern = arrD
        If RegEx.Test("@" & LCase(a(i, 4))) = False Then
          RegEx.Pattern = arrC
          a(i, 6) = a(i, 5)
          a(i, 5) = ""
        End If
        s1 = RegEx.Replace("@" & LCase(a(i, 4)), "")
        s2 = Replace(a(i, 4), s1, "", , , vbTextCompare)
        a(i, 4) = s2
        If Left(a(i, 4), 2) = "CF" Then
          a(i, 4) = Mid(a(i, 4), 2)
        End If
      End If
      balance = balance + Val(a(i, 5)) - Val(a(i, 6))
      a(i, 7) = balance
    Next
    Range("A2").Resize(UBound(a, 1), UBound(a, 2)).Value = a
    
    Range("A:A").NumberFormat = "dd/mm/yyyy"
    Range("E:G").NumberFormat = "#,##0.00"
    Range("A2:G" & lr).Borders.LineStyle = xlContinuous

  Application.ScreenUpdating = True
End Sub

🧙‍♂️
 
Upvote 0
it's great!
but there is problem for the formula in first row for each name
see the result in OP for row 26 for Ameer name as in example:
I try to explain this
* when brings the amount from BVC sheet then will first row for each name will subtract debit from credit , the next row for the same row will change formula until finish the name( see the first row for each name and the next how calculat)
Click to expand...
based on your code in row 23 this is what I got
omrany.xlsm
ABCDEFG
1DATENAMEINVOICE NO CONDITIONDEBITCREDITBALANCE
231/12/2023AhmedBVC OF DATE 31/12/2023PREVIOUS BALANCE1,000.001,000.00
301/01/2024AhmedVN number 345666Bank withdrawal3,000.004,000.00
401/01/2024AhmedVN number 345666Bank deposit3,000.001,000.00
501/01/2024AhmedVN number 345666Cash deposit3,000.00-2,000.00
601/01/2024AhmedVN number 345666Bank withdrawal3,000.001,000.00
702/01/2024AhmedVN number 345667Cash withdrawal2,000.003,000.00
802/01/2024AhmedVN number 345667Cash deposit2,000.001,000.00
902/01/2024AhmedVN number 345667Bank withdrawal2,000.003,000.00
1003/01/2024AhmedVN number 345668Bank withdrawal1,200.004,200.00
1103/01/2024AhmedVN number 345668Cash deposit1,200.003,000.00
1203/01/2024AhmedVN number 345668Bank deposit1,200.001,800.00
1303/01/2024AhmedVN number 345668Futures Sales Returns1,200.00600.00
1404/01/2024AhmedVN number 345669Cash withdrawal1,300.001,900.00
1504/01/2024AhmedVN number 345669Bank deposit1,300.00600.00
1604/01/2024AhmedVN number 345669Futures Sales Returns1,300.00-700.00
1705/01/2024AhmedVN number 345670Futures Sales3,000.002,300.00
1805/01/2024AhmedVN number 345670Bank deposit2,200.00100.00
1906/01/2024AhmedVN number 345671Futures Returns purchases3,400.003,500.00
2007/01/2024AhmedVN number 345672Futures purchases3,500.000.00
2107/01/2024AhmedVN number 345672Futures Sales1,200.001,200.00
2207/01/2024AhmedVN number 345672Futures Returns purchases3,500.004,700.00
2308/01/2024AmeerVN number 345673Futures purchases2,000.002,700.00
2409/01/2024AmeerVN number 345674Futures purchases2,001.00699.00
2509/01/2024AmeerVN number 345675Futures purchases3,000.00-2,301.00
2608/01/2024amerVN number 345673Futures Sales Returns1,201.00-3,502.00
2709/01/2024AnanVN number 345674Futures Sales Returns2,000.00-5,502.00
2812/01/2024AnanVN number 345677Futures Returns purchases2,000.00-3,502.00
2908/01/2024AneenVN number 345673Futures Sales1,201.00-2,301.00
3009/01/2024AneenVN number 345668Bank deposit2,000.00-4,301.00
3131/12/2023HamdaanBVC OF DATE 31/12/2023PREVIOUS BALANCE2,000.00-6,301.00
3231/12/2023HameedBVC OF DATE 31/12/2023PREVIOUS BALANCE2,000.00-8,301.00
3302/01/2024HameedVN number 345667Bank deposit2,000.00-10,301.00
3404/01/2024HameedVN number 345669Cash deposit1,300.00-11,601.00
3506/01/2024HameedVN number 345671Cash deposit2,000.00-13,601.00
3608/01/2024HameedVN number 345673Futures Returns purchases3,501.00-10,100.00
3709/01/2024HameedVN number 345674Futures Returns purchases3,502.00-6,598.00
3810/01/2024HameedVN number 345675Futures Returns purchases3,503.00-3,095.00
3911/01/2024HameedVN number 345676Futures Returns purchases3,504.00409.00
4031/12/2023HamudaBVC OF DATE 31/12/2023PREVIOUS BALANCE49,000.0049,409.00
4105/01/2024HamudaVN number 345670Futures purchases2,200.0047,209.00
4205/01/2024HamudaVN number 345670Futures Sales Returns3,000.0044,209.00
4306/01/2024HamudaVN number 345671Cash withdrawal3,400.0047,609.00
4406/01/2024HamudaVN number 345671Futures Sales Returns2,000.0045,609.00
4507/01/2024HamudaVN number 345672Futures Sales Returns1,200.0044,409.00
4631/12/2023ManarBVC OF DATE 31/12/2023PREVIOUS BALANCE1,000.0043,409.00
ACC


it will cal incorrectly.
1.PNG


the right should be
omrany.xlsm
ABCDEFG
2308/01/2024AmeerVN number 345673Futures purchases2,000.00-2,000.00
2409/01/2024AmeerVN number 345674Futures purchases2,001.00-4,001.00
2509/01/2024AmeerVN number 345675Futures purchases3,000.00-7,001.00
ACC
Cell Formulas
RangeFormula
G23G23=E23-F23
G24:G25G24=G23+E24-F24
 
Upvote 0
Tested with +10,000 on each sheet finished in 4 seconds

Try:

VBA Code: 
Sub Put_Amount_In_Debit_Credit()
  Dim sh As Worksheet, shA As Worksheet
  Dim i As Long, j As Long, k As Long, lr As Long, n As Long
  Dim arSh As Variant, itm As Variant, arrD As Variant, arrC As Variant
  Dim a As Variant, b As Variant
  Dim RegEx As Object
  Dim balance As Double
  Dim s1 As String, s2 As String, ant As String
  
  arSh = Array("BVC", "SA", "VS", "AP", "SSC")
  For Each itm In arSh
    lr = lr + Sheets(itm).Range("A" & Rows.Count).End(3).Row
  Next
  ReDim b(1 To lr, 1 To 7)
  
  'Add balance
  Set sh = Sheets(arSh(0))
  a = sh.Range("A2", sh.Range("E" & Rows.Count).End(3)).Value2
  For i = 1 To UBound(a, 1)
    If a(i, 5) <> 0 Then
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 2)
      b(k, 3) = "BVC OF DATE " & Format(a(i, 1), "dd/mm/yyyy")
      b(k, 4) = "PREVIOUS BALANCE"
      If a(i, 5) > 0 Then
        b(k, 5) = a(i, 5)
      Else
        b(k, 6) = a(i, 5) * -1
      End If
    End If
  Next
  
  'Add all sheets
  For n = 1 To UBound(arSh)
    Set sh = Sheets(arSh(n))
    a = sh.Range("A2", sh.Range("E" & Rows.Count).End(3)).Value2
    For i = 1 To UBound(a, 1)
      k = k + 1
      For j = 1 To UBound(a, 2)
        b(k, j) = a(i, j)
        If j = 4 Then
          If Left(LCase(a(i, j)), 21) = LCase("Futures Sales Returns") Then
            b(k, j) = "C" & a(i, j)
          End If
        End If
      Next
    Next
  Next
  
  '
  Application.ScreenUpdating = False
    Set shA = Sheets("ACC")
    shA.Range("A2:G" & Rows.Count).ClearContents
    shA.Range("A2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
    lr = shA.Range("A" & Rows.Count).End(3).Row
    With shA.Range("A2:G" & lr)
      .Sort shA.Range("B2"), xlAscending, shA.Range("A2"), , xlAscending, Header:=xlNo
      a = .Value
    End With
    
    arrD = "@bank withdrawal|@cash withdrawal|@futures returns purchases|@futures sales"
    arrC = "@cash deposit|@bank deposit|@cfutures sales returns|@futures purchases"
    'added cfutures sales returns to differentiate from futures sales
    
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True
    RegEx.Pattern = arrD
    
    For i = 1 To UBound(a, 1)
      RegEx.Pattern = arrD
      If RegEx.Test("@" & LCase(a(i, 4))) = True Then
        'a(i, 6) = ""
      Else
        RegEx.Pattern = arrC
        If RegEx.Test("@" & LCase(a(i, 4))) = True Then
          a(i, 6) = a(i, 5)
          a(i, 5) = ""
        End If
      End If
      s1 = RegEx.Replace("@" & LCase(a(i, 4)), "")
      s2 = Replace(a(i, 4), s1, "", , , vbTextCompare)
      a(i, 4) = s2
      If Left(a(i, 4), 2) = "CF" Then
        a(i, 4) = Mid(a(i, 4), 2)
      End If
      
      If a(i, 4) = "PREVIOUS BALANCE" Or ant <> LCase(a(i, 2)) Then balance = 0
      balance = balance + Val(a(i, 5)) - Val(a(i, 6))
      a(i, 7) = balance
      ant = LCase(a(i, 2))
    Next
    
    shA.Range("A2").Resize(UBound(a, 1), UBound(a, 2)).Value = a
    shA.Range("A:A").NumberFormat = "dd/mm/yyyy"
    shA.Range("E:G").NumberFormat = "#,##0.00"
    shA.Range("A:G").HorizontalAlignment = xlCenter
    shA.Range("A2:G" & lr).Borders.LineStyle = xlContinuous

  Application.ScreenUpdating = True
End Sub

😇
 
Upvote 0
Tested with +10,000 on each sheet finished in 4 seconds
Click to expand...
for me finished in 8 seconds !
I'm not sure if there is way to make it 2 second at least , what you think?
 
Upvote 0
Omran Y said:
what you think?
Click to expand...

You are not asking for just one request, you have more than 10 requests for this thread.

And the output contains what you are asking for ;)


What you need is to change your computer, increase the memory or make your processor faster.

🧙‍♂️
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Merge data in listbox based on another listbox after search for name and dates
Replies
6
Views
151
Omran Y
O
A
  • Solved
Extraction last row based on specific words and put in account column(debit,credit)
Replies
4
Views
184
Alaa mg
A
A
  • Solved
Filter data in listbox on form based on matching part of item in two comboboxes
Replies
9
Views
106
Ali M
A
H
  • Solved
Split file into multiple files based on year in column
Replies
6
Views
261
Hasson
H
O
  • Solved
show data on userform vertically based on combobox for multiple ranges
Replies
2
Views
133
Omran Y
O

Forum statistics

Threads
1,223,104
Messages
6,170,125
Members
452,303
Latest member
c4cstore

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