Posts by RAJESH SHAH

Cross posted https://www.facebook.com/group…p_post_approved&ref=notif

The issue is solved now. Solution

destSheet.Range("B" & destRow).NumberFormat = "dd-mm-yyyy"
destSheet.Range("B" & destRow).Value = sourceSheet.Range("O7").Value
destSheet.Range("C" & destRow).NumberFormat = "dd-mm-yyyy"
destSheet.Range("C" & destRow).Value = sourceSheet.Range("J7").Value

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

Option Explicit

Sub A01_RajeshCopyDataToOC()

Application.ScreenUpdating = False
    Dim sourceSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Sheets("Rajesh")
    
    Dim destWorkbook As Workbook
    Set destWorkbook = Workbooks.Open("C:\Users\Admin\Desktop\Pradeep Sep 23\PC\OC.xlsx", , , , "your_password_here")
    
    Dim destSheet As Worksheet
    Set destSheet = destWorkbook.Sheets("OC")
    
    Dim destRow As Long
    destRow = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row + 1 'find last used row + 1
    
    destSheet.Range("A" & destRow).Value = sourceSheet.Range("J8").Value
    destSheet.Range("B" & destRow).Value = Format(sourceSheet.Range("O7").Value, "dd-mm-yyyy")
    destSheet.Range("C" & destRow).Value = Format(sourceSheet.Range("J7").Value, "dd-mm-yyyy")
    destSheet.Range("D" & destRow).Value = sourceSheet.Range("D7").Value
    destSheet.Range("E" & destRow).Value = sourceSheet.Range("O9").Value
    destSheet.Range("F" & destRow).Value = sourceSheet.Range("K75").Value
    destSheet.Range("G" & destRow).Value = Format(sourceSheet.Range("M79").Value, "0.00")
       
    destSheet.Range("H" & destRow).Value = IIf(sourceSheet.Range("D9").Value = sourceSheet.Range("J9").Value, "On Customer Location", "On Site")
    destSheet.Range("I" & destRow).Value = sourceSheet.Range("K4").Value
    destSheet.Range("J" & destRow).Value = sourceSheet.Range("K92").Value
    
    destSheet.Range("K" & destRow).Value = IIf(sourceSheet.Range("M93").Value > 0, sourceSheet.Range("M93").Value, "")
    
    destSheet.Range("L" & destRow).Value = IIf(destSheet.Range("K" & destRow).Value = "", sourceSheet.Range("M99").Value, "")
    
    destSheet.Range("O" & destRow).Formula = "=IF(L" & destRow & ">0,""No"",""Yes"")"
    
    destSheet.Cells.EntireColumn.AutoFit
    destWorkbook.Close SaveChanges:=True ' You can set this to False if you don't want to save changes
    activeSheet.Activate
    sourceSheet.Range("A1").Select

    Set destSheet = Nothing
    Set destWorkbook = Nothing
    Set sourceSheet = Nothing
    
    Application.ScreenUpdating = True

MsgBox "Data copied to PC."
End Sub

I edited the second module successfully and got the expected result. Thank you very much gijsmo.

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", ""))))))

The problem is resolved.

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....

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

I have created a range in a sheet of state codes and was able to create multiple folders with a code. The query is resolved for now.

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.

    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.

Not all. One is still pending. Looks like everyone has hit the sack. Time for me too to hit the sack. Maybe tomorrow some one can solve the last issue. Good Night.

Quote from Carim

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.

Quote from RAJESH SHAH

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

Hello Jackson. The post is an old one. I don't remember this post. I assume that I dropped the idea of getting the result as it was giving me some problems. If it had worked, then I would have it with me. So, I must have tried to change the whole process and create a new code.

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.

Hello formula experts

I am getting a value error in some cells where I am expecting B2C. I need your help to please correct the formula and get the correct result.