Consulting

Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 41 to 60 of 62

Thread: VBA to keep format when concatenating

  1. #41
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    The runSecondStepofTool Algorithm:


    1. With Sheet2
      1. Concatenate each Record (Substituting a currency symbol of various NumberFormats where indicated)
      2. Assign the Record String to a Dictionary Object
      3. To the left of the Table insert a formula to concatenate Each Record + some additional strings

    2. With Sheet1
      1. Assign "Eur" to the Freight Currency Field of all Records
      2. Concatenate each Record (Substituting a currency symbol of various NumberFormats where indicated)
      3. Compare this Record String to the Sheet2 Dictionary. If found, Assign "V" to column "G" to the right of the Table
      4. In Column "H", If "V" not present in Column "G", assign an INDEX + MATCH formula using certain fields from the corresponding Row in Sheet2
      5. Replace Formulas in Column "H" with Values.
      6. Format Column "H"
      7. In Column "H", Replace "N/A" with "Not Requested"



    To investigate look down ~ 900 rows in Column H to find clues as to problem. And when you get it to work a logic bomb will make it fail at the first "Not Requested" and all subsequent rows will be "Not Requested"



    My Step 2 Algorithm
    Sheet1 has no formatting except the headers
    Run Step 1 Code on sheet2
    Sort both sheets by Vendor then Plant Number then Part number


    1. With Sheet1 format Price and Freight NumberFormat with Currency Symbol to match sheet 2 Currency Symbol
    2. For each Record in Sheet 1 & 2
      1. If Vendor & Plant & Part# = Vendor & Plant & Part#, then Records Match
        1. Else Compare above and below Records to see which Sheet has missing/additional record
        2. Insert blank Highlighted row on sheet missing record. Adjust Row Counter as needed. GoTo Next Row (Record)

      2. If Freight = Freight Then Records Match Else Highlight Discrepancies
      3. If Price = Price Then Records Match Else Highlight Discrepancies
      4. If P.Currency.NumberFormat = Price.NumberFormatThen Records Match Else Highlight Discrepancies
      5. IF.Currency.NumberFormat = Freight.NumberFormat Then Records MatchElse Highlight Discrepancies

    3. Next Record (Row)
    4. With Sheets 1 & 2, delete all records with no highlighting for ease of investigation, keeping all records (rows) aligned


    To investigate merely look from sheet to sheet, row by row, only rows with discrepancies are there.

    With the clues above, can you see the Logic Bomb in your code?
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  2. #42
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,744
    @SamT

    The 'newest' gulden is at least 14 year old; since 2001 we adopted the less valuable EURO.

  3. #43
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    @ snb,

    I know, but Euros are not country specific. Or are they?

    When I got out of the USNavy, I had a scrapbook with over 30 different currencies and a raft of diplomas and certificates from various tech schools and colleges.

    It was stolen from me, by children, no doubt, as even if they had a way to exchange all the money, there was less than 30 USD in it. But the bills were very pretty compared to US "greenbacks."
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  4. #44
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,744
    All Euros are country specific.

  5. #45
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    Then, although a Gulden is prettier, a Nederland Euro would work.
    I always 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. #46
    HI snb
    Belgium? I think I once bumped into you web site

    @ SamT
    Thanks!

    I will try to find you a more valuable Gulden for you for sure I'll try hard
    and the letter obviously to say thank you

    No. It's not the main target, it's not even a minor target.

    To use a football analogy, concatenating the NumberFormats is merely one way to shape your tongue while head butting the ball down field, and I am not even sure that a head butt is the best possible passing technique in your case.

    I don't know if you are familiar with this old adage, but you've been focused on one tree for so long that you have forgotten the forest.





    Great SamT...I had been studying literature for all my life...then when I was going to starve... I adapted my dreamer mind to technical jobs as much I could.. I
    was not familiar with your old adage but now I love it

    When I say that my main target was that silly thing with the concatenation, I meant that I disturbed you guys for that reason... as in my very bugged opinion the tool was ok and the only missing thing was the possibility to have also the format (EUR/USD) reported in sheet1...

    Now your immensely more technical mind unveiled as the all procedure is very subject to errors, the Logic Bomb as you call it.
    And now that I read your explanation, that unfortunately I can only understand partially, I see that my tool was very rudimental...

    What Can I say? I can only say thanks and asking you to be patient please , as you certainly have been till now.
    Your algorithm sounds supremely good, and if it's not asking too much of your time yes please let's do it

    I attached the latest version of the file, where you have both mine and yours codes, only one comment about your codes:

    We can rearrange the code in Sheet2NumFormats to standardize them before you transfer the data into your system. The you can run both Sheet2 subs to clean it up before transfer.
    Remove these constants and Replace the three Format Column Code sections With this
    I did it I removed the three constants and added the long section you posted but I got an error so I went back to the first version you sent... , see the screenshot please.

    Thanks
    Attached Images Attached Images
    Attached Files Attached Files

  7. #47

    Red face

    I forgot to say that the only thing, if I can dare , that I don't like is:

    To investigate merely look from sheet to sheet, row by row, only rows with discrepancies are there.
    please this looking from sheet to sheet is exactly what I wanted to avoid with my concatenation rubbish idea!!

    my goal (or better to say my dream given my ignorance with the code..) was to have everything at my hand in sheet1 at the very end of the procedure...please
    Last edited by isasa74; 10-12-2015 at 02:12 PM.

  8. #48
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    have everything at my hand in sheet1 at the very end of the procedure...please
    I'll see what I can come up with. Tomorrow.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  9. #49
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    isasa,

    Give me a list of the various problems, errors, and issues you see in the Request that cause problems with the System..
    I always 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. #50
    Hi SamT ,

    I thought you forgot about me!! THANKS super THANKS for your help as always

    Here you are:

    Sheet2 – Problem with request.

    1. Blank Spaces (fixed by the 1st step procedure)
    2. Different Formats (fixed by the 1st step procedure)
    3. Wrong currencies, basically:

    a. When column B = US20 then Freight must be expressed in USD in all the others cases in EUR. (it would be wonderful to have those errors already highlighted by the Step 1st in Sheet2)
    b. Price should always be expressed in USD. (it would be wonderful to have those errors already highlighted by the Step 1st in Sheet2)

    4. Wrong Import Duties, basically when NL10 in Column B import duties column must have a value, when Column B <> NL10 there must be NO import duties.
    (exception when the part number (column C- Sheet2) starts with 8FI* even with column B = NL10 in column B there must be NO import duties.)


    Problem with the mass upload tool (reasons why I need to check after the upload, just FYI)

    1. We change price/freight very often, when too many “condition” have been uploaded for the same Part Number the upload can go wrong (new price/freight don’t overwrite the old ones)
    2. When too many conditions have been uploaded for the same part number funny IFR may be created like NL10 with import duties or wrong vendors.
    3. When we have too many vendors for the same part, the upload tool can mess up and change price/freight for the wrong vendor.



    PLEASE NOTE :

    Concerning point 3. a & b I’m exploring solution with the conditional formatting but I got stuck with the currency as Conditional formatting doesn’t seem to me able to handle that.

  11. #51
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    1 to 4: I can fix all that easily... Before you upload the request to the Mass upload tool.

    Problem with the mass upload tool (reasons why I need to check after the upload, just FYI)

    1. We change price/freight very often, when too many “condition” have been uploaded for the same Part Number the upload can go wrong (new price/freight don’t overwrite the old ones)
    2. When too many conditions have been uploaded for the same part number funny IFR may be created like NL10 with import duties or wrong vendors.
    3. When we have too many vendors for the same part, the upload tool can mess up and change price/freight for the wrong vendor.
    WE can take those tasks away from the MUT and give them to Excel and VBA, But first let's get the request fixed as much as possible.

    I wish to introduce a new Programming Term to you: "Business Rules."

    Examples of Business Rules for Requests:

    1. No blank Spaces.
    2. Only Number Formats "$" and "€"
      1. Exception: When Value = zero or Blank, NumberFormat = "$0.00"

    3. When column B = US20 then Freight must be expressed in Number Formats "$"
    4. when NL10 in Column B import duties column must have a value
      1. exception: when the part number starts with 8FI* there must be NO import duties.

    5. when Column B <> NL10 there must be NO import duties.
    6. Import Duties NumberFormat = "General" (No Number Format)
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  12. #52
    Quote Originally Posted by SamT View Post
    1 to 4: I can fix all that easily... Before you upload the request to the Mass upload tool.
    Thanks!



    WE can take those tasks away from the MUT and give them to Excel and VBA, But first let's get the request fixed as much as possible.
    for the last three points, don't bother about because even when the Upload Template ( a MUT sheet where you put DATA in) would be perfect, there is always something getting lost/wrong when transmitted from the MUT to SAP, and to fix that I don't have either clue or authorization.

    For example:

    When too many conditions have been uploaded for the same part number funny IFR may be created like NL10 with import duties or wrong vendors.
    that " many conditions" I'm talking about are those already in the system so , at that point, it is not only about the MUT but more about how the MUT and SAP are communicating.



    I'm already super happy if we can have the first 4 points fixed by the First Step of the tool and then the second step comparing the request (sheet2) to the result of the upload that I get as an extract from the system (Sheet1).
    that way I will have:

    Step one:

    Making the two sheets perfectly comparable (no blank, all same format, etc.).
    Plus, if we fix the 4 points of the Business rules, spotting out those errors already coming with the request.(so not imputable to the upload)

    Step two:

    Actually Comparing the two sheets , Spotting out what went wrong with the upload itself (if anything goes wrong of course, errors are indeed very rare but too dangerous)

    I wish to introduce a new Programming Term to you: "Business Rules."
    thanks SamT I'm learning a lot from you, thank you very much.

    Let me know what else you may need for your magic please.
    Last edited by isasa74; 10-20-2015 at 11:15 AM.

  13. #53
    long time that I don't hear from you

  14. #54
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    Thank you for reminding me. I have many projects an 5 or 6 are for VBA Express members. I recently cleaned up my hard drive and some of my work for you was accidentally deleted. I have now downloaded again all the files we shared.
    I always 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. #55

    Smile

    oops no problem

    Listen Great SamT ...

    if for you it is a problem to start it over don't bother please, for me it would be wonderful even only to have, somehow with a magic of yours, the currency reported in Sheet1 along with all the concatenation (I know you hate this idea sorry!!).

    On the contrary if you still enjoy helping me I have please an exception for one of the business rules (Step-One for Sheet2):


    Exception for rule:

    4 when NL10 in Column B import duties column must have a value

    IF the 6th,7th,8th digits in the part number in Sheet2-Column C is one among the following combination:

    069; 052; 019; 017; 036 (e.g. 8UFT.019.15) then even if B=NL10 still NO import duties.

    Thanks!

  16. #56
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    Isasa,

    How do you know who requested the parts, and when the request as issued?

    Is there a Request ID number?

    I think that an Error rweprot is better than indicating errors on the request or the SAP Report. That way your team only has to look at lines with errors, instead of scanning the entire request and Sap Error Report.

    But I need to also put the requesting facility, person, date, and request number for this to be a very good error reporting tool.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  17. #57

    Unhappy

    Hi SamT, I hope you are good and thanks for helping me as always

    Quote Originally Posted by SamT View Post
    Isasa,

    How do you know who requested the parts, and when the request as issued?
    All via emails.

    Is there a Request ID number?
    Negative , all via emails

    I think that an Error rweprot is better than indicating errors on the request or the SAP Report. That way your team only has to look at lines with errors, instead of scanning the entire request and Sap Error Report.
    The errors in the request form are not the only problem, it is good to have those captured but they are not the only ones.

    The request content is copied (with other details that I add) in a template to be uploaded via MUT, but after the upload I still need to check the outcome via SAP extract as we have those random (rarely indeed but dangerous) errors with the MUT itself.

    That is why it is (very) good to check the business rules in the request (Sheet2) but it is also crucial to check the extract from SAP (Sheet1), the latter check is actually the main goal of the all tool thing.

    But I need to also put the requesting facility, person, date, and request number for this to be a very good error reporting tool.
    Don't have a name, always coming from different people and always via emails.
    Last edited by isasa74; 10-30-2015 at 03:31 PM. Reason: typo

  18. #58
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    So there is no method to identify a request and the SAP output together?
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  19. #59
    Hi SamT

    No there no way to identify them together because the outcome of SAP is identified with only my name/or one's of my team an the date.


    1. I receive the prices via email (many emails to be precise) to be uploaded for the next month one week before month end in the request form that you know, I do a first check to get read of the very gross errors (typos for example)
    2. I transfer the content of the request in a template that can be processed by the MUT.
    3. I upload everything via MUT.
    4. I have then a windows of one week to check for errors in SAP.
    5. I get out of SAP an extract of all the prices upload for a given date (all those for next month usually)
    6. I check the extract or errors.


    My (yours too) tool is meant:
    1. to check errors in the request sheet2 (even after the upload , that doesn't matter because prices are for the next month) that way I can understand whether the error comes from a wrong request or from the faulty upload tool.
    2. to check out if what requested is actually what we have in the system (by comparing the two sheets)
    3. to Spot not Requested Records


    You have noticed that the tool gives the message NOT Requested when in the SAP extract you have a record that wasn't in the request, that is because in SAP there are some prices records (funny ones) created when someone tries to place an order for a piece that doesn't have a prices record yet.
    Last edited by isasa74; 10-31-2015 at 02:57 PM. Reason: typos

  20. #60
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    Please be patient, I've been down for a while, but I'm back on the job now.
    I always 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
  •