Consulting

Page 5 of 5 FirstFirst ... 3 4 5
Results 81 to 100 of 100

Thread: Creating identifying numbers

  1. #81
    SamT

    it looks good. Includes all the information I need plus more.

    I was not correct when I said product and description need a : the are combined. The replace sheets I provided include the full product codes

    I have not been to my computer so I can’t provide them till later. I am replying from my phone. I just wanted to anwser your question so you didn’t start coding with it not correct

    tks

  2. #82
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,272
    Location
    it looks good. Includes all the information I need plus more.
    Ideally, it will have all the information (Headers/Field Names) you will ever need, and no more.

    _______________________________________________________

    From this discussion, I assume that T-Pro can handle empty columns?
    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

  3. #83
    Samt


    I use the same xlsm files just altered to convert my orders to upload into the ERP program. The xlsm files that convert the orders are much simpler. They just do replacing data and change the date. Will I or someone be able to alter your xlsm files to do the same. I have attached the xslm files if you wanted to take a look how they work. I also attached the raw data orders I receive. I do this daily to import orders. I upload KKB, Albany and Vermont orders.

    They also have replace xlsx files like I have for the data conversion xlsm files.

    Thanks
    Attached Files Attached Files

  4. #84
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,272
    Location
    A Reply to post #67.

    You are the only one here who cvan see your images
    Albany Map
    [IMG]file:///C:/Users/JoeF/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png[/IMG]
    Albany Map
    [IMG]file:///C:/Users/JoeF/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png[/IMG]

    Midstate
    [IMG]file:///C:/Users/JoeF/AppData/Local/Temp/msohtmlclip1/01/clip_image003.png[/IMG]
    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

  5. #85
    I was not aware of that. I did also include the photos in the attached .doc file from post 67.

  6. #86
    SamT


    I wanted to see how things are going? Is this a project you still have been able to work on? Is there anything you need from me if so?

  7. #87
    Paul_Hossler


    First I want to say your macro is working perfect. It is allowing me to convert and upload the data until I have a better way.
    I have a question I want to ask and see if you can help me with. I need column L contents to also copy to the credit. In row L is the rep for the customer. The credits do not include the rep.
    So currently the macro searches out all the stores that have a credit on the same day as the invoice then copied the invoice number and put a 9 in front of it. If it is possible to also have the contents of column L copied to the credit that would be great.

    If this is possible and you have time to help me out I would appreciate it.




    Thank for the help.

  8. #88
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,329
    Location
    Quote Originally Posted by joeny0706 View Post
    Paul_Hossler


    First I want to say your macro is working perfect. It is allowing me to convert and upload the data until I have a better way.
    I have a question I want to ask and see if you can help me with. I need column L contents to also copy to the credit. In row L is the rep for the customer. The credits do not include the rep.
    So currently the macro searches out all the stores that have a credit on the same day as the invoice then copied the invoice number and put a 9 in front of it. If it is possible to also have the contents of column L copied to the credit that would be great.

    If this is possible and you have time to help me out I would appreciate it.

    Thank for the help.
    I wasn't following, since SamT was driving, so I missed this ....

    Is it possible to make the macro execute on the csv file I have open. I saved it to the personal workbook. So when I have the CSV open I want to convert I can just run you macro. When I do it now the first thing it does is ask what csv file I want to convert.
    Can it just execute on the csv I have open when I run the macro rather than ask me to open the file?

    Also if rather than saving the converted file it just makes the changes and I can save the file where and how I want


    The macro does work great. If that is a lot of changes don't worry it is good as is.


    Thanks Much

    The only test data I have doesn't have any col L data, so can you post an updated CSV?

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    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

  9. #89
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,272
    Location
    I'm retired. VBA is my hobby. Sometimes Life gets in the way.

    I have been scouring this thread for bits and pieces of detail about your Requirements, Requests, and Business Rules.

    I'll be back.
    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

  10. #90
    Paul

    I have attached the raw data and also the output the macro creates.

    Thanks
    Attached Files Attached Files

  11. #91
    SamT


    No hurry. We all have busy lives. I just wanted to make sure you did not need anything.

    Thanks

  12. #92
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,329
    Location
    Quote Originally Posted by joeny0706 View Post
    Paul

    I have attached the raw data and also the output the macro creates.

    Thanks
    OK, see if this works for you

    The CSV file must be open in Excel as a workbook. It should be the only CSV open

    The macro just reformats the CSV worksheet in the CSV workbook without saving it anywhere

    The Rep is copied to the credit lines

    Option Explicit
    
    
    Sub FixCSV()
        Dim wbCSV As Workbook, wb As Workbook
        Dim wsCSV As Worksheet
        Dim rCSV As Range, rCSV1 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"
            
        
            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

  13. #93
    Paul


    Looks good. Thanks

    After I run the macro I need to save the file, close it and open it again. If I dont do that and I look all the credit numbers have a ' in front of the 9. But once I close it and open it again it is gone. Weird.
    Attached Files Attached Files

  14. #94
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,272
    Location
    .Cells(j, 2).Value = "'9" & .Cells(i - 1, 2).Value
    Not critical in your situation: Leading apostrophe makes the Invoice number a String. Normally does not display in cell


    Important!!! Change last "Application.ScreenUpdating = False" to True. This will also fix the display.
    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

  15. #95
    I will fix that. I didn’t have time yet to look at code. Wonder why it disappears after saving


    Thanks

  16. #96
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,329
    Location
    Quote Originally Posted by joeny0706 View Post
    I will fix that. I didn’t have time yet to look at code. Wonder why it disappears after saving Thanks
    When you save as a CSV all formatting is lost, so now it's just 912345
    When you bring it back into Excel, Excel see 912345 and leaves it as a number

    Originally, you wanted a prefixed "9" and I wanted to be sure that the resulting number matched the pattern correctly, especially since sorting was required

    That's why "912345" was left justified in the cell since it was a string (that only looked like a number), whereas 12345 was a true number and was right justified

    Not needed now (but doesn't hurt anything). Just edit this line to make it "9" instead of "'9"

     .Cells(j, 2).Value = "9" & .Cells(i - 1, 2).Value      '  add leading 9
    ---------------------------------------------------------------------------------------------------------------------

    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

  17. #97
    I have two more task I need then I will be able to start uploading my Flexibake raw data using the process I do now.

    Back in post 29 I asked
    I have one task that I will need to do also. I have 7 different stores that I need to delete out before converting. They need to be removed before going into the accounting program. I have a module that deletes any rows with 0 in the qty column. What I would do is alter that module to delete any rows that have these stores in the name column. Since I think you are working on a new process to do all my converting I wanted to see if that is something I will need to do "create a module to delete these names" or will that be a task the new process will be able to do?

    Also do you have any questions or anything I can do to help?

    Attached are the store names that I will need to delete before doing the converting. This is only with the flexibake raw data. I will not need this task for any of the other raw data.
    This task will need to happen when I am running the macro Paul created for me that fixes the credit identification numbers. I attached the CSV Fix xlsm file. If that can be altered to also delete out stores I dont need. These stores need to be removed before I enter the data into my current xlsm that does all the converting.



    Also back in post 69 I asked this.

    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 the macro code below look within my xlsm file and replaces text. It has a different xlsx 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 xlsx 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?












    Option Explicit


    Sub Replaces()
    Dim wbData As Workbook

    Application.ScreenUpdating = False

    'delete Data is if still exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Data").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    'open Replaces workbook and copy data sheet in
    Workbooks.Open Filename:="C:\FlexibakeConversions\FlexreplacedataInvoice.xlsx" ' <<<<<<<<<<<<<<<<<
    Set wbData = ActiveWorkbook
    wbData.Worksheets("Data").Copy Before:=ThisWorkbook.Worksheets(1)
    wbData.Close False

    ThisWorkbook.Activate

    'do the replaces
    Call ReplaceAllSheets(Worksheets("Data").Range("A1"))
    Call ReplaceAllSheets(Worksheets("Data").Range("D1"))
    Call ReplaceAllSheets(Worksheets("Data").Range("G1"))
    'get rid of Data
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Data").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Application.ScreenUpdating = True
    End Sub


    'this sub is Private so that it's only usable in this module
    Private Sub ReplaceAllSheets(R As Range)
    Dim i As Long
    Dim ws As Worksheet
    Dim r1 As Range

    Set r1 = R.CurrentRegion

    If r1.Rows.Count < 2 Then Exit Sub

    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = "Data" Then GoTo GetNextSheet
    If ws.UsedRange.Cells.Count < 2 Then GoTo GetNextSheet

    For i = 2 To r1.Rows.Count
    ws.UsedRange.Cells.Replace What:=r1.Cells(i, 1).Value, Replacement:=r1.Cells(i, 2).Value, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    Next i
    GetNextSheet:
    Next
    End Sub




    Thanks all for the help. This will get me going for now. My process is not the most efficient but for now it does allow me to complete the task I am assigned.
    Attached Files Attached Files

  18. #98
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,329
    Location
    Probably the most logical place would be to have a list of stores to delete in the Fix CSV workbook and delete when you fix the CSV

    New code, but look at attachment

            'delete stores
            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
    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

  19. #99
    Paul


    That works great in removing the stores at fixing the credit numbers at same time.

    Thanks

  20. #100
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,919
    Or:

    Sub M_snb()
      sn = Sheet1.Columns(1).SpecialCells(2)
        
      With Sheets.Add(, Sheets(Sheets.Count), , "J:\download\hbc9-27-10-10_Out.csv")
        For j = 1 To UBound(sn)
          .Columns(3).Replace sn(j, 1), "", 2
        Next
        .Columns(3).SpecialCells(4).EntireRow.Delete
            
        .Cells(1).Resize(, 12) = [transpose(char(row(65:77)))]
            
        .Cells(1, 16).Resize(2) = Application.Transpose(Array(.Cells(1, 7), "C"))
        .Cells(1).CurrentRegion.AdvancedFilter 2, .Cells(1, 16).CurrentRegion, .Cells(1, 20)
        sn = .Cells(1, 20).CurrentRegion.Offset(1)
        .Cells(1, 16).CurrentRegion.ClearContents
        .Cells(1, 20).CurrentRegion.ClearContents
            
        For j = 1 To UBound(sn) - 1
          If j > 1 Then If sn(j, 1) & sn(j, 3) = sn(j - 1, 1) & sn(j - 1, 3) Then sn(j, 12) = sn(j - 1, 12)
          sn(j, 2) = "9" & sn(j, 2)
        Next
        .Columns(7).Replace "C", ""
        .Columns(7).SpecialCells(4).EntireRow.Delete
        .Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(sn), UBound(sn, 2)) = sn
        .Rows(1).ClearContents
      End With
    End Sub

Posting Permissions

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