Posts by RAJESH SHAH

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hello experts

    When I run this code the date format is changed from dd-mm-yyyy to mm-dd-yyyy in the destination sheet.

    In the source sheet in the cell J7 the date is displayed as 08-07-2023 and in cells O7 it is displayed as 8-july-23 but I am getting the result in the destination sheet as 07-08-2023 in both the cells instead of 08-07-2023. This is the full code

    Thank you gijsmo. That worked for the 2A copy sheet. But the Not imported sheet is still showing the incorrect dates. Please tell me how do I edit the other module.? (Create_Not_imported)

    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question


    Hello experts

    When I run the code, I am facing a date issue in the destination sheets. The dates are converted from the source sheet from dd/mm/yyyy to mm-dd-yyyy in some of the rows. Can someone please help me correct the code to get the right dates. For instance, in the second row the date 03/01/2022 is converted to 01-03-2022. This is just one date. There are multiple dates which start from day 1 to 12 are all converted to months. Please note this was posted in another forum and I haven't come across any working solution for the above.

    Date Format Issue once again

    Try this:

    =IF(AND(ISBLANK(B2), ISBLANK(C2)), "",IF(AND(ISBLANK(B2), NOT(ISBLANK(C2))), "New Price",IF(AND(NOT(ISBLANK(B2)), ISBLANK(C2)), "Product Withdrawn", IF(C2 > B2, "Increased", IF(C2 < B2, "Decreased",IF(C2 = B2, "Price not changed", ""))))))

    If you want Blank to be printed then try the below formula

    =IF(AND(ISBLANK(B2), ISBLANK(C2)), "Blank",

    IF(AND(ISBLANK(B2), NOT(ISBLANK(C2))), "New Price",IF(AND(NOT(ISBLANK(B2)), ISBLANK(C2)), "Product Withdrawn", IF(C2 > B2, "Increased", IF(C2 < B2, "Decreased",IF(C2 = B2, "Price not changed", ""))))))

    Namaskar Experts

    I need your expertise to edit this code which is showing syntax error. What is wrong in this code line and please tell me what do I do to correct it? I am trying to check the value in Step1 sheet column I and if the value is greater than 0 then ... else....

    Code
                Sheets("EditCS").Range("C" & FormulaDestinationRow).Formula = "=IF('Step1'!I" & i & "=>0",""Invoices for outward supply"",""Credit Note"")"

    I am using windows 10. I have created this app with the above lines included and it is compatible in all versions of excel. Windows 11 no idea. I have used the same code in more than 20 applications and it has never given me any problem.

    I am not a VBA expert but I too copy the data from excel and paste the data to notepad with the help of a code shared online. I can share the code which works for me but can't explain more. Edit the code as per your sheet name, define the varialbes and check it out. Hope it helps.

    Code
        Sheets("XYZ").Range("A1").Resize(x, LastColumnNumberInRow).Copy
    '
        strData = CreateObject("htmlfile").ParentWindow.ClipboardData.GetData("Text")                                       ' Save contents into strData
    '
        strTempFile = "C:\Users\" & Environ("username") & "\Desktop\Name.xml"
        CreateObject("Scripting.FileSystemObject").CreateTextFile(strTempFile, True).Write strData                      ' Write the data to file

    Hello experts

    I am trying something which I have never done before. There are multiple sheets in the workbook. Most of the sheets have an extension of state code in their names. Like B2B KA, B2B HP, etc., I have to create a folder of each state code in a particular folder such KA, HP, etc., In short I have to create multiple folders with the criteria that all names with B2B extension of 2 letters. There are names like B2B SRS and only B2B. Such names to be avoided.

    If it is possible to do this with the help of a code, then I need your expertise to create as the above which I will really appreciate it. I am sharing the workbook with the name of the sheet names for reference. Please note that the names of sheets will be different in different scenarios. The main object is to select the B2B sheet with extensions of 2 letters in the workbook and create folders by that name. I have tried to explain in the steps sheet too.

    Query Sheet name folders.xlsm

    Thank you.

    If I may a general remark .... you are extremely complicating your own life by using Excel like a word processor ...


    Do not confuse a properly structured database ... with plenty of Cosmetic Issues : Merged Cells, Footnotes, Remarks, etc ...

    I am not a professional coder. I am learning from the comments shared with the code. I just play around with the codes and try to understand them. Later when I create an application I use those codes. When I am stuck I take and get help online.

    The merged cells, footnotes and all is the data as received and I have to extract from that data without effecting the original data.

    Once again, thank you very much.

    I need to add 3 more formulas in the code

    Carim. Thank you man. As there was no response at all, for the last 4 hours ,I was busy trying to solve this with online help. I already have got the formulas right. and getting the values of columns B and C with a scripting.dictionary. Only column P is Pending and I have shared the formula for that too. In a short time that person may get the values in column P with the help of scripting.dictionary.

    I really appreciate your effort to help. Thank you very much. :)

    I posted the request in another forum and didn't receive the expected solution. I got a part of the solution solved which was not the final result. In the meantime I made some changes in the heading columns and updated the file and got some of the formulas working. The rest of the formulas are still pending to be solved.

    Hello experts

    I have this application which is incomplete and I need to add 3 more formulas in the code to complete it. As the formulas are too complicated for me, due to which I am stuck at this point.

    In the sales Result sheet I have manually written the expected result. I have even explained how to get the result in the steps sheet of the 3 columns. Hopefully some one will understand my issue and help me to solve it.

    Thank you in advance.

    Get values in column B C and S of Sales sheet.xlsm


    FYI, This data and columns headings are completely different from the one posted in the other forum..

    Code to do multiple functions to get result in cell

    In column T I am getting a Value error. The result I am expecting is B2C as per the formula. If column P contains B2C it should display B2C. in column T. Again, If column P is blank then it should display B2C in column T.

    Sorry, I didn't know you have already corrected the formula. It is perfect.

    Thanks rory. :)