+ Reply to Thread
Results 1 to 52 of 52

Need VBA for System Generated Data to get Output in User defined format

  1. #1
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Need VBA for System Generated Data to get Output in User defined format

    I am attaching Excel Worksheet, Which contains two sheet one is system generated file and second is Output which i want to achieve. for your easy understanding i have highlighted Cell and fond with colors. in system generated file there is several items which are repeated couple of times and transaction quantity of these items are differ. i want these items in output file only one time and sum of that particular item.
    Last edited by KRIXXXX; 04-21-2020 at 11:58 PM.

  2. #2
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Need VBA for System Generated Data to get Output in User defined format

    Hello again !

    Try the below code … Formatting the report (font/color/boarders … etc.) is a real headache ! It adds a lot to the code


    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Thumbs up Re: Need VBA for System Generated Data to get Output in User defined format

    This code works but when i change quantity of MR it gives differ output.

    also add option to select Date (Column L) of which data I want in Output.

    same Item Quantity Total (Column J) in output file.

    final output should be like sheet "OUTPUT of Date 15-01-20"
    Last edited by KRIXXXX; 04-21-2020 at 11:59 PM.

  4. #4
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Need VBA for System Generated Data to get Output in User defined format

    Hey KRIXXXX

    I have revised the code to accommodate for the comments provided. Note that you haven't mentioned anything about the "specified" date to be extracted initially & my first code was built to match your initial file with sheet "Output I Want" … It would save forum members time & efforts if you clearly understand your requirement & explain to us in your first post.

    Anyway, the revised code will ask you to enter the date then will extract the data based on that date. There's only one difference between the output of my code & your "OUTPUT of Date 15-01-20" sheet which is the "MR Number" where you have listed all MR numbers & my code will only show MR numbers related to the specified date … Let me know if that needs to be changed


    PS: If the below code does what you need, please mark this thread as solved by selecting Thread Tools from the menu link above & add to the reputation(s) of those who helped

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Thumbs up Re: Need VBA for System Generated Data to get Output in User defined format

    Sorry for bothering you again. I am facing two Issues.

    First one is it shows in "MR Number" first MR two times -- in the beginning of the string and at the end.
    (23098599-22975714-22977062-22995678-22995679-23012874-23012879-23097978-23098380-23098531-23098599)

    Second is when i enter the Date, for example 15-Jan-2020 it should give me in (Output) result item wise quantity total which is
    transacted on dated "15-01-20". as stated in worksheet "OUTPUT of Date 15-01-20" but in actual when i run VBA and
    enter 15-Jan-2020 it generates data of all dates.

    Column L is Transaction Date Column
    and
    Column J is Transaction Quantity Column

    Column B contains Item Codes.
    Last edited by KRIXXXX; 04-26-2020 at 02:02 AM.

  6. #6
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Need VBA for System Generated Data to get Output in User defined format

    I have added 1 line in the code & it should fix the issue you encountered. But you still haven't answered my question, if you filtered data for 15th of Jan, do you still want all MR Numbers to be displayed or only those related to the selected date ?

    Please Login or Register  to view this content.

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    Try this:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  8. #8
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    That was my Mistake. I want only MR Numbers which are transacted on date : Selected Date.

    and Yes, What is the code to extract MR Number from One single String in the same sheet at the end of system generated date (in Column N in that particular row of MR Number)
    i.e. MR Number - 22975714******** Requestor -** Mr. KEVIN PETERSON***

  9. #9
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    It still gives me output of all item with all transacted quantity.

    on date:15-01-20 (dd-mm-yyyy) only 23 items transacted and transaction quantity total is 3195.333

    where as in output I am getting 41 items transacted and transaction quantity total is 15755.164 which is of all various transaction date.

    date:15-01-20 is for example only.

  10. #10
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    what is the code for getting the output of One particular date and MR Numbers which are transacted on that particular date only...

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    On your "OutputIWant" sheet you list 41 numbers - I'm confused

    But, to get the date entries, I combined the date with the Item code to form a composite key.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Need VBA for System Generated Data to get Output in User defined format

    2 in 1.
    See if this works.
    If it works, cosmetic for output will be added only if needed.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jindon; 04-23-2020 at 02:16 AM.

  13. #13
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by KRIXXXX View Post
    It still gives me output of all item with all transacted quantity.

    on date:15-01-20 (dd-mm-yyyy) only 23 items transacted and transaction quantity total is 3195.333
    @KRIXXXX, have you tried the code in post # 6 ?

  14. #14
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    Yes, But It gives me Output of all dates.

  15. #15
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    Thanks, It works well. I have tried using some dates and it gives me correct output.
    But When I have Generated Output for the date 19-03-2020 and of all dates.

    On Date 19-03-2020 there are 28 nos of Items are transacted and total transaction quantity sum for this date is 8177.071 (Column J) but in output file it gives 26 items and sum of this items is 4801.554

    where as in output of all dates it gives 41 nos of Items which is correct but Sum of all transacted items quantity is 12379.647 but actually it is 15755.164

    So i request you to do some improvement in code.

    One more thing is in output file CELL "C8" date should be report generation date. for example if i generated the report for the date 01-02-2020 then this date should be displays in Cell "C8".

    and same thing is which MR numbers CELL "C9" only those MR should be displays here which are transacted on that particular date.

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Need VBA for System Generated Data to get Output in User defined format

    I don't know who you are talking to though.
    Please Login or Register  to view this content.

  17. #17
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Need VBA for System Generated Data to get Output in User defined format

    @KRIXXXX, the code in post #6 is working correctly for date 19-Mar-2020 where total = 8177.071 but # of items is 28 not 26. I have ran the code from my laptop & kept a copy of the output in the 2nd sheet, please run the macro in the attached file & let me know if you get the same results
    Attached Files Attached Files
    Last edited by nankw83; 04-23-2020 at 06:10 AM.

  18. #18
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    Now it works:

    Please Login or Register  to view this content.
    Last edited by xladept; 04-23-2020 at 05:04 PM.

  19. #19
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by nankw83 View Post
    @KRIXXXX, have you tried the code in post # 6 ?


    Yes, but It still gives me output of all item with all transacted quantity.

    on date:15-01-20 (dd-mm-yyyy) only 23 items transacted and transaction quantity total is 3195.333

    where as in output I am getting 41 items transacted and transaction quantity total is 15755.164 which is of all various transaction date.

    date:15-01-20 is for example only.

  20. #20
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by nankw83 View Post
    I have added 1 line in the code & it should fix the issue you encountered. But you still haven't answered my question, if you filtered data for 15th of Jan, do you still want all MR Numbers to be displayed or only those related to the selected date ?

    Please Login or Register  to view this content.
    That was my Mistake. I want only MR Numbers which are transacted on date : Selected Date.

  21. #21
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by nankw83 View Post
    @KRIXXXX, the code in post #6 is working correctly for date 19-Mar-2020 where total = 8177.071 but # of items is 28 not 26. I have ran the code from my laptop & kept a copy of the output in the 2nd sheet, please run the macro in the attached file & let me know if you get the same results
    Attachment 674179

    i am getting above error message when entering 19-mar-2020 date and running vba code.
    Last edited by KRIXXXX; 04-26-2020 at 02:03 AM.

  22. #22
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by xladept View Post
    Now it works:

    Please Login or Register  to view this content.
    Now it gives me correct items and itemwise transaction quantities.

    but still there is little issue.

    when i enter dates like 03/01/2020 (dd/mm/yyyy) and other dates which dd and mm are lesser than 12 it gives me correct items and item wise transaction quantities. but in cell C8 it gives output date 1-Mar-2020 in stead of 3-Jan-2020 and MR Numbers in cell C9 remains blank.

  23. #23
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    Just enter 3/01/2020 and see if that fixes the discrepancy because of this assignment:

    Please Login or Register  to view this content.

  24. #24
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by xladept View Post
    Just enter 3/01/2020 and see if that fixes the discrepancy because of this assignment:

    Please Login or Register  to view this content.
    Attachment 674213

    when i enter date 3/01/2020 it gives me above error message

  25. #25
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by KRIXXXX View Post
    Attachment 674179

    i am getting above error message when entering 19-mar-2020 date and running vba code.
    We're not able to view the attached file … Could you type the error message & at what line it happens ?

  26. #26
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    I'm sorry - I should have told you to enter as 3/1/2020. In the code I've defined that date as a string and it needs to match the data exactly. f.r. 1/15/2020 15:45 is rendered "1/15/2020" by the code - so, enter as m/d/yyyy.

    Tidying up:

    Please Login or Register  to view this content.
    Last edited by xladept; 04-24-2020 at 01:03 PM.

  27. #27
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    No data for entered date !
    Last edited by KRIXXXX; 04-24-2020 at 02:03 PM.

  28. #28
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    Are you getting the message with my code?

  29. #29
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by xladept View Post
    I'm sorry - I should have told you to enter as 3/1/2020. In the code I've defined that date as a string and it needs to match the data exactly. f.r. 1/15/2020 15:45 is rendered "1/15/2020" by the code - so, enter as m/d/yyyy.

    Tidying up:

    Please Login or Register  to view this content.
    Still in some cases MR Numbers remains blank in cell C9.

  30. #30
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by xladept View Post
    Are you getting the message with my code?
    No. Your code is working perfectly for items and its transaction quantities but in some cases MR Numbers remains blank in cell C9 and Date in cell C8 it gives output date 1-Mar-2020 in stead of 3-Jan-2020 (this date is for example only)

  31. #31
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    But - 3/1/2020 is 1-March-2020 and there are no records with that date

  32. #32
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by xladept View Post
    Are you getting the message with my code?
    @xladept

    I have change the sequence of date and month in your code and now it works perfectly. Thank You So much Bro.

    Sub KRIXXXV(): Dim wo As Worksheet, ws As Worksheet, S As String, r As Long
    Dim G, Code As String, O, K, D As Date, n As Long, q As Long
    Set wo = Sheets("Output I Want"): Set ws = Sheets("System Generated File")
    G = ws.UsedRange: D = (ws.Cells(19, 12)): D = Day(D) & "/" & Month(D) & "/" & Year(D)
    S = InputBox("Enter date d/m/yyyy")
    If IsDate(S) Then D = S
    S = ws.Cells(4, 1): S = Right(S, Len(S) - InStrRev(S, ":"))
    wo.Cells(2, 1) = UCase(S) & " STORE": S = ""
    wo.Cells(3, 3) = ws.Cells(11, 3): wo.Cells(4, 3) = ws.Cells(9, 6)
    wo.Cells(5, 3) = ws.Cells(6, 3): wo.Cells(6, 3) = ws.Cells(10, 6)
    wo.Cells(7, 3) = ws.Cells(6, 6): wo.Cells(8, 3) = D
    wo.UsedRange.Offset(12).ClearContents
    For r = 14 To UBound(G): q = InStr(1, G(r, 1), "MR Number")
    If q Then
    n = r + 4: Do: n = n + 1
    If n > UBound(G) Then Exit Do
    If InStr(1, G(n, 12), D) Then S = S + Mid(G(r, 1), 12, 9): Exit Do
    Loop Until InStr(1, G(n, 1), "MR Number"): End If
    Next r: q = 1: wo.Cells(9, 3) = WorksheetFunction.Trim(S)
    O = wo.Range("G1").Resize(UBound(G, 1), 5)
    With CreateObject("Scripting.Dictionary")
    For r = 19 To UBound(G, 1): Code = G(r, 2): S = (ws.Cells(r, 12))
    If Not IsDate(S) Then GoTo GetNext
    : S = Day(S) & "/" & Month(S) & "/" & Year(S)
    If S <> D Then GoTo GetNext
    If .Exists(Code) Then .Item(Code) = .Item(Code) + G(r, 10) _
    Else .Item(Code) = G(r, 10): O(q, 1) = q: O(q, 2) = Code: _
    O(q, 3) = G(r, 3): O(q, 4) = G(r, 8): O(q, 5) = "": q = q + 1
    GetNext: Next r: K = .Keys()
    For r = 0 To UBound(K): O(r + 1, 5) = .Item(K(r))
    Next r: End With
    If UBound(K) > -1 Then wo.Range("A13").Resize(UBound(K) + 1, 5) = O
    End Sub

  33. #33
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by xladept View Post
    But - 3/1/2020 is 1-March-2020 and there are no records with that date
    This issue is resolved.

    I have one more request and you have spend much time on my sheet so I think it will be very easy for you to resolve my this query. I am attaching here workbook in this workbook there is one sheet named "BACKSIDE" and i want that output also.

    Please provide the code for this. I was thinking for creating new thread but it will be easy for you and saves other members time.

    If you create separate code for this, kindly tell me how to add two different vba code in one workbook.
    Last edited by KRIXXXX; 04-26-2020 at 02:04 AM.

  34. #34
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    It still has the original XML report for Santariana - i hope that's a mistake?

  35. #35
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by xladept View Post
    It still has the original XML report for Santariana - i hope that's a mistake?
    I have uploaded the updated file. Yes It has original XML report.

  36. #36
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    Try this -it's all in the same program

    Please Login or Register  to view this content.
    And - thanks for the rep!
    Last edited by xladept; 04-24-2020 at 08:02 PM. Reason: To make it fit

  37. #37
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Thumbs up Re: Need VBA for System Generated Data to get Output in User defined format

    Thanks a Lot.
    Last edited by davesexcel; 04-29-2020 at 06:34 AM.

  38. #38
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Thumbs up Re: Need VBA for System Generated Data to get Output in User defined format

    Please do some customization in code,

    So in some case when i need Output of all dates it can be done.

    For example, When I run the code and input box prompts for date if I don't enter date and just hit enter it should generate the output of All Dates.

    Thanks
    Attached Files Attached Files
    Last edited by davesexcel; 04-29-2020 at 06:34 AM.

  39. #39
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    Try this:

    Please Login or Register  to view this content.

  40. #40
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    "MR Number" does not generates for any one perticular date or for all dates. this field remains Blank.

    also there is no output generate in sheet "BACKSIDE"
    Last edited by davesexcel; 04-29-2020 at 06:40 AM.

  41. #41
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    It worked for me
    Last edited by xladept; 04-28-2020 at 12:34 AM.

  42. #42
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Thumbs up Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by xladept View Post
    It worked for me
    System Date of My Laptop and My Workspace PC is DD/MM/YYYY

    after your above Remark, I have Changed My Laptops System Date from DD/MM/YYYY to MM/DD/YYYY and
    Now It generates MR Numbers also but for dates which are lesser than greater than 12 (I think It is). When I run code for dates 3 Jan 20 , 4 Jan 20 and 1 Feb 20 MR Number field remains Blank.

    But Problem is I have to Use this VBA code at My Workspace. And In my workspaces All PC are Local Area Network Connected and all PCs System date is Configured in DD/MM/YYYY and We can't change it.

    So Please make some changes So It can Run Perfectly in PC's which System Date is DD/MM/YYYY
    Last edited by KRIXXXX; 04-28-2020 at 10:33 AM.

  43. #43
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    Here's my copy of your file:
    Attached Files Attached Files

  44. #44
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    Well, it's not pretty but it seems to work

    Please Login or Register  to view this content.

  45. #45
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    not working
    Last edited by davesexcel; 04-29-2020 at 06:36 AM.

  46. #46
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    Did the copy from post #43 work? I just don't know what else to do

  47. #47
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Thumbs up Re: Need VBA for System Generated Data to get Output in User defined format

    Quote Originally Posted by xladept View Post
    Did the copy from post #43 work? I just don't know what else to do
    No,

    My uploaded copy in post #38 works perfectelly in My PC. In that file if you can see the code I have change the sequence of date and month as per My PC system date i.e. dd/mm/yyyy. and it works perfectly for single dates.

    May be this can help you.
    Last edited by KRIXXXX; 04-29-2020 at 11:02 AM.

  48. #48
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    I've submitted this thread to the Forum, hopefully, someone has dealt with and solved this issue before.

  49. #49
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    See if this negates the formatting problem:

    Please Login or Register  to view this content.
    Last edited by xladept; 04-29-2020 at 02:18 PM.

  50. #50
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Thumbs up Re: Need VBA for System Generated Data to get Output in User defined format

    @xladept

    Thank You so Much Mate.

    It works like Charm.

    I know it's now going to kind of annoying But this is one last request related to this Thread,
    If you can add the heading "SANTARIANA DIVISION STORE" in the cell "A1" of sheet "BACKSIDE" it will be appreciated.
    I'm sure It will be very easy for You.

  51. #51
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need VBA for System Generated Data to get Output in User defined format

    I've learned my lesson on date formatting dilemma's and I'm so pleased to have this chore done!

    You're welcome and here's the finishing touch (I hope):

    Please Login or Register  to view this content.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Last edited by xladept; 04-30-2020 at 12:44 AM.

  52. #52
    Forum Contributor
    Join Date
    04-08-2020
    Location
    MUMBAI
    MS-Off Ver
    OFFICE 2007
    Posts
    234

    Re: Need VBA for System Generated Data to get Output in User defined format

    @xladept

    THANKS ...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 04-13-2020, 09:57 PM
  2. Output Boolean Array from User-Defined Function
    By jben86 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2013, 04:25 PM
  3. Replies: 0
    Last Post: 11-19-2012, 10:41 AM
  4. User defined function to output multiple values
    By firefly2k8 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-20-2010, 12:03 PM
  5. embedding user-defined hyperlink into auto-generated email
    By kuraitori in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-17-2008, 10:49 PM
  6. [SOLVED] Returning formatted output from a User defined function
    By Salman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-14-2006, 03:05 AM
  7. [SOLVED] How to output an array in Excel with a user-defined function?
    By Andy Chan in forum Excel General
    Replies: 2
    Last Post: 12-30-2005, 04:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1