Resize of cells in different codes displays an error. Required to edit to count <=0 or >0

[RAJESH1960]

Hello guys

First of all, I am very grateful, to JohnnyL for sharing the codes with comments. With the help of formulas and codes shared by many of the members in the forum and with the help of JohnnyL’s comments, I have tried to edit and create my first code. Thank you to each of them.

I tried to edit JohnnyL’s code to improvise and edited major changes and some additional changes in it. Except for some manual work, I have the code good and running but I am stuck at some places for which I need your expertise to complete it. I have commented some of the issues, in all the codes which need to be replaced by editing some lines. Out of the 7 macro buttons, 2 are dummy as mentioned next to the macro. Those steps have to be done manually, To get the final result, the macro buttons have to be pressed in the same order.

Now, for the problems I could not solve and need your expertise to solve and complete this project.

• I have to manually select the cells from AC2 to AU2 and double click every time. I need help to write the code in such a way that it can be included in the “Move PasteData to CopyData” code.
• When I press the button “Get NA Ledgers to MasterData”, and if there are no NA ledgers in the MasterData sheet, it generates an error. Hence I need to add an extra line If B2 =<1 Then exit sub… else play the rest of the code… something like that. Same solution goes for all the other button codes.
• In each code where changes are required, which I was unable to write the code, I have commented at each line where the code requires editing.
• The split address code is in the MasterData sheet which displays the correct result when played in that sheet only. Also, it doesn’t run in option explicit. I need your expertise to correct the code and include it one of the above codes wherever it is required.
• Most of the Application lines of code have changed or deleted while editing each macro - Application.ScreenUpdading, True / False, Application.CutCopyMode = False / True, etc., I have no idea why and where to place them.
• When the project is over, there will be only 2 sheets on display – List of Ledgers and PasteData. Rest of the sheets need to be hidden with a code. (This is possible and I can do it once the above problems are solved.
• Finally, for the most difficult and biggest problem. Enter January and February in List of ledgers sheet Cells A1 and A2 respectively and run the buttons one by one.

If possible, then, try to combine all the 7 codes in 2 or max 3 buttons. I would really appreciate that. Like ClearData, Generate Master XML and Generate Purchases XML. Just wondering if it is possible, to put it all in one button and get 2 xml’s generated and saved on the desktop.

Please note: I have a copy of all the sheets in the workbook and hidden it. By any chance, while testing or editing the code, it gets deleted or the formulas and data are deleted, it will not be a problem to copy the data again - by clicking the select sheet button, copy and paste.

Important Note: Please do not run JohnnyL’s code. It is just for reference only as the presentation,working and range of cells in the code of the sheets have changed.
Edit & Combine codes.xlsm

 

[johnnyL]

I am still highly confused as to what all you want done. several parts of your post are not specific enough for me to grasp what you want. We have had this discussion before about creating posts with a short 'To-Do' list as opposed to a very long laundry list of problems that need to be addressed.

Might I suggest you break up your asking into various smaller lists of requests?

That being said, I have added a few of the multitude of asks that you made. Please take a look at that & then update us where you are at.

File Link

 

[RAJESH1960]

Ok Ok. Sorry. I just got so carried away in recreating the code that I wanted the solution to all my errors at a time. I will check and revert back. Next time I will make sure I do not confuse anybody and ask one query at a time.

 

[RAJESH1960]

Pending to do list No.1 when there are NA ledgers.
I am mentioning this because the codes have to run with 2 options in every case. One is when all the masters match and are available. The other when there are unique ledgers in the list which are not available. Right now I am testing it when Ledgers are NA.
I run and press the code buttons in the same order as displayed. I noticed that you have corrected the code in Move paste data to copy data to fill down cells AC2:AU2 successfully. First of all, When I run the button "Get NA Ledgers to master Data" the result is correct, but it is displaying the formula instead of the value.
Only after the above issue is solved, I have to press the Split Address button which is in the MasterData sheet. I need help to edit the code in such a way that it runs from the List of ledgers sheet. and then I can delete the button from MasterData sheet.

 

[RAJESH1960]

You have missed to write the updated formula for column D2.

 

[RAJESH1960]

Since you have inserted the formula in the formula array, I have changed the range and included cell C2 also to clear contents.

 

[RAJESH1960]

For your referecne, this was the formula to get the State in one of the earlier files. But it has variables defined. I hope it helps after some editing

Sheets("MasterData").Range("D2").Formula = "IFERROR(VLOOKUP(LEFT($C" & 1 + FormulaNumber & _
                    ",2)+0,'States Code'!$A$1:$B$37,2,0),"""")"              ' Write updated formula to C2

 

[RAJESH1960]

I have changed the range and included cell C2 also to clear contents.

I have replaced and corrected the above. Hopefully it is correct.??

Sheets("MasterData").Range("B2:C2", Sheets("MasterData").Range("B2:C2").End(xlDown)).ClearContents
    'Sheets("MasterData").Range("B2:B2", Sheets("MasterData").Range("B2:B2").End(xlDown)).ClearContents

 

[RAJESH1960]

In the Get NA Ledgers to MasterCode, if I replace this code

If .Count > 0 Then                                                                      '   If dictionary count > 0 then ...
            Sheets("MasterData").Range("B2").Resize(.Count) = Application.Transpose(.keys)
        End If
    End With

With this

If .Count > 0 Then                                                                      '   If dictionary count > 0 then ...
            Sheets("MasterData").Range("B2").Resize(.Count) = Application.Transpose(.keys)
Else
            MsgBox "All Ledgers Available."                                                         '       Display message to user
            
            GoTo Continue                                                                           '       Jump to Continue:
        End If
    End With
Continue:

I hope, it will not affect the code. I don’t want to confuse you any further and also avoid tampering with your code as I am not sure, so. I leave it to you to do the needful.

 

[RAJESH1960]

"All Ledgers Available."

If I replace this code, it displays the message, but it copies the headings from C1:D1 to C2:D2 which will affect the ImportMasters sheet.