Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to delete Row & Sum

    I need a macro to to the following on all csv Files in folder C:\Journals

    1) To delete the rows where Col B & C are blank and there is a number in Col A next to the blank data in Col B & C
    2) To sum Col B and C seperately and where there is a difference to add this to Col C in the same row containing account number 7224020

    I have attached sample data. The file is a Csv file, but this site does not allow CSV files, so I saved this as an xls file
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I get a message that the file is corrupt and I can not open it. Try changing the extension of your CSV to TXT and attaching that.

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Some questions on your request
    1) Delete any rows where B&C are blank and have a number in A. So if B&C are both blank and A is blank, text, boolean, or an error, then leave it?
    2) What if there is a difference between the sum of col B and the sum of Col C and there is no account 7224020? What if there is more than 1 - where should it go? If the row with that account has a value in C already, replacing it will not make the difference go away, what to do then?

    Steve

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the reply

    I have opened the file from this site and it opens up with now problem. Not sure whats causing the problem on your side. Have attached txt file

    1) If B & C and both Blank and A has text , then delete row
    2) There will always be an account 7224020 in Col A -I suggestion may be to clear the Value in Col C in the same row where 7224020 appears and insert the balancing figure there

    Howard
    Attached Files Attached Files
    Last edited by HowardC; 2013-10-23 at 12:59.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    So the value in the row with 7224020 should always be cleared and then the balancing number added in it?
    What if the balancing number is negative? Do you still want it in C or should the negative be put into B?

    You haven't answered all the questions:
    1) what if A is blank when B&C are blank?
    2) What if by some mistake, there is no entry or there are multiple entries?

    Steve

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the reply
    The row with 7224020 should always be cleared and then the balancing number added in it. The balancing number will always be a negative, so it must go into Col C

    1) If A is Blank & B & C are blank, then the row must be deleted
    2) If there is no entry , it can be left intact.
    3) if there are multiple entries, then nothing must be done, except if Col B & C are blank, then row to be deleted

    Howard

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If I understand this should work.
    WARNING: I recommend making a copy of the folder and the files and running the code on the copies. The code opens the CSVs, edits them and then saves them (overwriting the originals). If the code does not do what you want, your original files would be lost if you did not make copies of them)

    Code:
    Option Explicit
    Sub FixTextFiles()
      Dim sPath As String
      Dim sFile As String
      Dim wkb As Workbook
      Dim wks As Worksheet
      Dim x As Integer
      Dim lRows As Long
      Dim lRow As Long
      Dim lAcct As Long
      
      'Change as desired
      lAcct = 7224020
      sPath = "C:\Journal\"
    
      Application.ScreenUpdating = False
      'get first file
      sFile = Dir(sPath & "*.csv")
      x = 0
      Do While sFile <> ""
        x = x + 1 'increment file counter
        Set wkb = Workbooks.Open _
          (Filename:=sPath & sFile, _
                UpdateLinks:=0, _
                AddToMRU:=False)
        Set wks = wkb.Worksheets(1)
        'balance the columns
        With Application.WorksheetFunction
          lRow = .Match(lAcct, wks.Columns("A:A"), 0)
          wks.Cells(lRow, 3).ClearContents
          wks.Cells(lRow, 3).Value = .Sum(wks.Columns("B:B")) - .Sum(wks.Columns("C:C"))
        End With
        
        'delete some rows
        With wks
          lRows = .Cells.SpecialCells(xlCellTypeLastCell).Row
          For lRow = lRows To 2 Step -1
            If .Cells(lRow, 2) & .Cells(lRow, 3) = "" _
              And .Cells(lRow, 1) <> "" Then
              .Cells(lRow, 1).EntireRow.Delete
            End If
          Next
        End With
        'Re-save the CSV, close the file
        Application.DisplayAlerts = False
        wkb.SaveAs Filename:=sPath & sFile, FileFormat:=xlCSV
        wkb.Close (False)
        Application.DisplayAlerts = True
        'geet the next file
        sFile = Dir
      Loop
      Application.ScreenUpdating = True
      'let us know the program is done
      MsgBox x & " Files Processed"
    End Sub
    Steve

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    HowardC (2013-10-23)

  9. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for all your effort in writing the code. Have tested and it works perfectly

    Regards

    Howard

  10. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    You helped me a while back. The code works perfectly if there is only one account number i.e lAcct = 7224020. I now have some CSV files where 7224020 is the balancing figure and one CSV file where the balancing figure is 7884200. I tried to amend the code as follows:

    Code:
     lAcct = 7224020
    to lAcct = Array (7884200,7224020)
    When running your original code and these two numbers need to be matched , an error comes up "unable to get the match property of the worksheetfunction" and the following code is highlighted

    Code:
      lRow = .Match(lAcct, wks.Columns("A:A"), 0)
    If I delete the CSV file containing account 7884200, then macro works perfectly

    It would be appreciated if you would kindly amend your code to accommodate this requirement

    Howard

  11. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Please walk me through the logic.
    Currently it looks for 7224020 and clears the contents of column c in that row. Then it places in that cell the value to "balance"
    Then it deletes some rows.

    Are you looking for:
    Look for 7224020, if found,
    clears the contents of column c in the 7224020 row
    place in that cell the value to "balance"

    If not found, look for 7884200
    if found
    clears the contents of column c in the 7884200 row
    place in that cell the value to "balance"

    If not found, search for the next number in the array and if found do like above,

    If no items from array, clear nothing, balance nothing,

    After the array is checked out
    Then deletes some rows....

    Or are you after something different? If so could you elaborate?

    Steve

  12. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    You have got it spot on and I quote

    "Look for 7224020, if found,
    clears the contents of column c in the 7224020 row
    place in that cell the value to "balance"

    If not found, look for 7884200
    if found
    clears the contents of column c in the 7884200 row
    place in that cell the value to "balance"

    If not found, search for the next number in the array and if found do like above,

    If no items from array, clear nothing, balance nothing"

    All the CSV files contain 7224020, except one file

    Howard

  13. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Have attached two sample files (Zip) as well as in Xls format

    1) If Col A has a number & B & C are blank row to be deleted
    2) Look for 7224020 and if found clears the contents of column c in the 7224020 row and place in that cell the value to "balance", If not found, look for 7884200
    and if found clears the contents of column c in the 7884200 row place in that cell the value to "balance"

    All The CSV files will have either account 7224020 or 7884200. These must contain the balancing value

    Your assistance in resolving this is most appreciated

    Howard
    Attached Files Attached Files

  14. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about this code? The blue lines are added and the red lines are edited, so you can see what edits I made. You can add more numbers (if needed later) to the ARRAY and it should adapt to their addition automatically

    Code:
    Option Explicit
    Sub FixTextFiles2()
      Dim sPath As String
      Dim sFile As String
      Dim wkb As Workbook
      Dim wks As Worksheet
      Dim x As Integer
      Dim lRows As Long
      Dim lRow As Long
      Dim vAcct As Variant
      Dim i As Integer
      
      'Change as desired
      vAcct = Array(7224020, 7884200)
      sPath = "C:\Journal\"
    
      Application.ScreenUpdating = False
      'get first file
      sFile = Dir(sPath & "*.csv")
      x = 0
      Do While sFile <> ""
        x = x + 1 'increment file counter
        Set wkb = Workbooks.Open _
          (Filename:=sPath & sFile, _
                UpdateLinks:=0, _
                AddToMRU:=False)
        Set wks = wkb.Worksheets(1)
    
        'search through Array
        lRow = 0
        i = LBound(vAcct)
        With Application.WorksheetFunction
          Do
            On Error Resume Next
            lRow = .Match(vAcct(i), wks.Columns("A:A"), 0)
            On Error GoTo 0
            i = i + 1
          Loop While lRow = 0 And i <= UBound(vAcct)
          If lRow <> 0 Then
          'balance the columns
            wks.Cells(lRow, 3).ClearContents
            wks.Cells(lRow, 3).Value = .Sum(wks.Columns("B:B")) - .Sum(wks.Columns("C:C"))
          End If
        End With
        
        'delete some rows
        With wks
          lRows = .Cells.SpecialCells(xlCellTypeLastCell).Row
          For lRow = lRows To 2 Step -1
            If .Cells(lRow, 2) & .Cells(lRow, 3) = "" _
              And .Cells(lRow, 1) <> "" Then
              .Cells(lRow, 1).EntireRow.Delete
            End If
          Next
        End With
        'Re-save the CSV, close the file
        Application.DisplayAlerts = False
        wkb.SaveAs Filename:=sPath & sFile, FileFormat:=xlCSV
        wkb.Close (False)
        Application.DisplayAlerts = True
        'get the next file
        sFile = Dir
      Loop
      Application.ScreenUpdating = True
      'let us know the program is done
      MsgBox x & " Files Processed"
    End Sub
    Steve

  15. The Following User Says Thank You to sdckapr For This Useful Post:

    HowardC (2013-11-21)

  16. #14
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for your help and explanations

    Code works perfectly

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •