Consulting

Page 6 of 6 FirstFirst ... 4 5 6
Results 101 to 117 of 117

Thread: Convert Many Invoice Formats to a Standard Format

  1. #101
    I was thinking about starting a new post for this question but I will start here and see if anyone is able to help

    I have a question about adjusting one of the macros I have in my conversion xlsm files. Should I start a different post or asking in this one is fine?


    I have a macro code below that looks within my xlsm file and replaces text. It has a different excel file it uses to know what to replace. I am having an issue that it is replacing items it should not. I have attached the xls files it uses to know what to replace and the raw data file that it searches.

    The issue is it is searching all the columns. I only need it to search column D for "Call ReplaceAllSheets(Worksheets("Data").Range("A1"))". Then for both "Call ReplaceAllSheets(Worksheets("Data").Range("D1")) and Call ReplaceAllSheets(Worksheets("Data").Range("G1")) it only needs to look in column C. It is replacing items in the wrong column. How can I restrict it to only search column D for products and only search column C for customers?

    I attached the module exported within the .zip. I also attached the XLSM file I paste all the text into and then run the macros.

    Thanks all



    SamT


    I was wondering if you have played around with this project? Anything you needed?

    Attached Files Attached Files

  2. #102

  3. #103
    snb


    That link refers to post 100. From what I see post 100 completes the task of deleting the rows with stores I do not need and also adding a 9 in front of the credit identification number. I dont know much about the code but I dont see anything that might help with the replace module, does that also have within it restrictions about using the replace module? They are ran at different times and it can not be combined if so.

    What I am asking now is a task that is completed from within a xlsm file I use for converting. That is used after I fix the credit number and delete stores I do not need.

    I attached a xlsm file that is filled with raw data. I then use the "runall" macro to do all the conversions. During this time is when it is searching within the wrong columns. I need to restrict where it looks when doing the replacing.

    In column B is the invoice numbers. Column D is the products. I have one product with the name of "85". So if the invoice number has a 85 within it is changing that also. Needs to only do product replacements in column D and only do store replacements in column C
    Attached Files Attached Files

  4. #104
    I am going to start a new post for my last question I asked in post 101. This post is focusing on the project as a whole. I just need help with the one replace module.

  5. #105
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #106
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Joe,

    I haven't forgotten you. I find this problem so fascinating that I spend 1 to 2 hrs many nights working with it. But, it is, a labor of love for me.

    This thread is at the top of my watch list, I will see anything posted here in less than 48 hrs.

    SamT
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #107
    SamT

    Thanks. I know it is a very labor intensive project so just wanted to check and see if you still had interest in it.

    The process I use to convert my orders is very similar. I just altered the xlsm files to convert raw order data into formatted order data. I am hoping I will be able to modify your project to do the same, but that is a question for much much later.



    Again thanks for staying with it!!!

  8. #108
    Paul

    I have been using the file you created. It works good to get the task completed.
    I had to make some changes to the raw data store names that is causing an issue. For many of the stores I have created a copy of the store with the word "STALE" after the name. They both are the same store.

    So now it is not matching like I first asked you it needed to. Can you adjust your code so it ignores the word STALE when it is comparing store names. Stale will always be at the end of the store name.

    I have attached a copy of the file you created for me.



    So I need it to ignore STALE when it is matching up store names.

    ex

    PriceChopper:Pch215 Sidney
    PriceChopper:Pch215 Sidney STALE

    I hope that is an easy adjustment.

    Thanks for the help.
    Attached Files Attached Files

  9. #109
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Not tested since I didn't have the latest CSV file

    You'll have to adjust this a bit I'm sure, but the idea is to delete the " STALE" from the entries in col 3 of the CSV sheet and then continue by replacing stores with TRUE so that SpecialCells can delete them easily

        'delete stores
        Call .Columns(3).Replace(" STALE", "", xlPart)      '   in CSV file <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    
    
        On Error Resume Next
        For Each rStore In ThisWorkbook.Worksheets("DeleteStores").Cells(1, 1).CurrentRegion
            Call .Columns(3).Replace(rStore.Value, True, xlWhole)
        Next
        
        .Columns(3).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
        On Error GoTo 0
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #110
    I will try. I am not sure where to enter that code. I did attach the data file. If I have trouble I let you know.




    Another helpful task would be to delete any row with a 0 in column E "qty"
    I normal use filter to delete all the rows with 0 before I run you macro. If the was included that would be great.







    Thanks for the quick reply.
    Attached Files Attached Files

  11. #111
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Option Explicit
    
    
    Sub FixCSV()
        Dim wbCSV As Workbook, wb As Workbook
        Dim wsCSV As Worksheet
        Dim rCSV As Range, rCSV1 As Range, rStore As Range
        Dim i As Long, j As Long
        
        'find open WB ending in CSV
        For Each wb In Workbooks
            If Right(wb.FullName, 3) = "CSV" Then
                Set wbCSV = wb
                Exit For
            End If
        Next
        
        If wbCSV Is Nothing Then
            Call MsgBox("There is no CSV file open in Excel", vbExclamation + vbOKOnly, "Fix CSV")
            Exit Sub
        End If
        
        Application.ScreenUpdating = False
        
        Set wsCSV = wbCSV.Worksheets(1)
        
        With wsCSV      '   Guessing
            .Cells(1, 1).Value = "Date"
            .Cells(1, 2).Value = "Invoice"
            .Cells(1, 3).Value = "Store"
            .Cells(1, 4).Value = "Product"
            .Cells(1, 5).Value = "Qty"
            .Cells(1, 6).Value = "Cost"
            .Cells(1, 7).Value = "InvCred"
            .Cells(1, 8).Value = "Something"
            .Cells(1, 9).Value = "Counter1"
            .Cells(1, 10).Value = "Counter2"
            .Cells(1, 11).Value = "Counter3"
            .Cells(1, 12).Value = "Representitive"
        
            'delete stores
            Call .Columns(3).Replace(" STALE", "", xlPart)  '   PHH 12/29/2020
    
    
             On Error Resume Next
            For Each rStore In ThisWorkbook.Worksheets("DeleteStores").Cells(1, 1).CurrentRegion
                Call .Columns(3).Replace(rStore.Value, True, xlWhole)
            Next
            .Columns(3).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
            
            
            Call .Columns(5).Replace(0, True, xlWhole)  '   PHH 12/29/2020
            .Columns(5).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete  '   PHH 12/29/2020
            On Error GoTo 0
            
            Set rCSV = .Cells(1, 1).CurrentRegion
            
            'save original order
            For i = 1 To rCSV.Rows.Count
                .Cells(i, 13).Value = i
            Next i
            
            Set rCSV = .Cells(1, 1).CurrentRegion
            Set rCSV1 = rCSV.Cells(2, 1).Resize(rCSV.Rows.Count - 1, rCSV.Columns.Count)
            
            With .Sort
                .SortFields.Clear
                .SortFields.Add Key:=rCSV1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SortFields.Add Key:=rCSV1.Columns(3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SortFields.Add Key:=rCSV1.Columns(7), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            
                .SetRange rCSV
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
        
        With rCSV
            For i = 2 To .Rows.Count
                If .Cells(i, 7).Value = "C" Then    '   CREDIT?
                    j = i
                    'same store and same date
                    Do While (.Cells(j, 3).Value = .Cells(i - 1, 3).Value) And _
                        (.Cells(j, 1).Value = .Cells(i - 1, 1).Value)
                        .Cells(j, 2).Value = "9" & .Cells(i - 1, 2).Value      '  add leading 9
                        .Cells(j, 12).Value = .Cells(i - 1, 12).Value           '  add rep
                        .Cells(j, 7).Value = "-C"                               '  add marker
                        j = j + 1
                    Loop
                End If
            Next i
        
            Call .Columns(7).Replace("-C", "C", xlWhole)
        End With
        
        'back to original sort order
        With wsCSV
            With .Sort
                .SortFields.Clear
                .SortFields.Add Key:=rCSV1.Columns(13), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            
                .SetRange rCSV
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        
            'get rid of order column
            .Columns(13).Delete
        
            'row 1 was originally blank
            .Rows(1).Resize(1, 12).ClearContents
        End With
        
        Application.ScreenUpdating = False
        
        MsgBox "CSV file " & wbCSV.FullName & " reformatted"
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #112
    Seems to be working great.
    Thanks

    Two other questions if you have time.
    1) Can you also have it remove KKB when it finds it. I have that used the same as the word STALE. So just deleting KKB would be great. I do need to make sure it only deletes KKB if it finds it in column C. There may be cases where KKB is in another column and that needs to stay.

    row 44 in the attached file shows a store with KKB after it.


    Also when there is no invoice on the same day it changes the identification number so the top item of the credit has a different invoice number. It leaves the top one and adds a 9 to the ones below it. Causing the credit to be split into two credit memos.

    The first line is 1189 and then under that is 91189. That is because there was no invoice on that day. Can the code be adjusted so if it does not find an invoice on the same day to not make any changes to the credit number.
    That is a cause does not happen often. When that does happen it is no assigned a rep. "Column L" I dont think there will be a way to give it a rep. I can deal with that. I just cant have the credit split into two.

    The attached file shows the results where this happens after your code is ran. Starting on row 28 you will see what is happening.


    12/24/2020 1189 Independent Stores:Northstar Orchards 21 CW 6 3 C
    12/24/2020 91189 Independent Stores:Northstar Orchards 25 WW 2 3 C
    12/24/2020 91189 Independent Stores:Northstar Orchards 26 FP 14 3 C
    12/24/2020 91189 Independent Stores:Northstar Orchards 31 OAT 3 3 C
    12/24/2020 91189 Independent Stores:Northstar Orchards 33 ITAL 8 2.25 C
    12/24/2020 91189 Independent Stores:Northstar Orchards 34 JR 3 3 C
    12/24/2020 91189 Independent Stores:Northstar Orchards 40 Flaxseed 2 3 C
    12/24/2020 91189 Independent Stores:Northstar Orchards 45 Multi Grain 4 3 C
    12/24/2020 91189 Independent Stores:Northstar Orchards 23 HR 1 3 C
    12/24/2020 91189 Independent Stores:Northstar Orchards 22 RYE 2 3 C
    12/24/2020 91189 Independent Stores:Northstar Orchards 28 WB 3 3 C
    Attached Files Attached Files

  13. #113
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    OK try this version

    The XLSM includes the before and after CSV file as a worksheet so I could annotate it

    I had to change the CSV data to get more test material (used num = 123456 and qty = 1234 a lot)

    Rows 2 - 13 are to remove STALE and KKB

    Rows 452 - 467 have 12/22/2020 with "I" and 143348

    Rows 484 - 486 have 12/22/2020 with C and will change to 9143348

    Rows 487 - 489 have 12/24/2020 with C but have no match for that date and that store



    Capture.JPG
    Attached Files Attached Files
    Last edited by Paul_Hossler; 12-30-2020 at 02:27 PM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #114
    I have been dragged away from this project. I should resume it tomorrow. I will let you know how that works.

    Thank for helping

  15. #115
    I posted the question below in post 100. I was going to ask again.

    I have a question about adjusting one of the macros I have in my conversion xlsm files.


    I have a macro code below that looks within my xlsm file and replaces text. It has a different excel file it uses to know what to replace. I am having an issue that it is replacing items it should not. I have attached the xls files it uses to know what to replace and the raw data file that it searches.

    The issue is it is searching all the columns. I only need it to search column D for "Call ReplaceAllSheets(Worksheets("Data").Range("A1"))". Then for both "Call ReplaceAllSheets(Worksheets("Data").Range("D1")) and Call ReplaceAllSheets(Worksheets("Data").Range("G1")) it only needs to look in column C. It is replacing items in the wrong column. How can I restrict it to only search column D for products and only search column C for customers?

    I attached the module exported within the .zip. I also attached the XLSM file I paste all the text into and then run the macros. If someone wants to help and just update the module that is used would be best. Then I can just replace the module in the active replace xlsm file.

    Thanks all


    Attached Files Attached Files

  16. #116
    SamT

    Just wondering if you played around with thisproject any more.

    I have more questions where I am going to start a new post.You have always helped me a lot so any ideas I would appreciate.


    Thanks

  17. #117
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    "FlexReplaceDataInvoice"

    I remember that project: Convert a bunch of invoice formats to a standard. Unfortunately, that was two computers ago and your folder did not get Backed up by the time that computer died, (BIOS issues). It was not lost forever. I plan on building another box and will place that HD in the new one. I don't know when that will happen as I have a ton of very more urgent projects on my plate ATT.

    How is that going? What is not happening now?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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