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

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
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
 
Rich (BB code):
Application.ScreenUpdating = False

    Dim x, rng1 As Range, rng2 As Range
    Set rng1 = Sheets("pastedata").Cells(1).CurrentRegion
    x = Application.Match("Supplier Invoice No. & Date", rng1.Rows(1), 0)
    If IsError(x) Then MsgBox """Supplier Invoice No. & Date"" is missing", vbCritical: Exit Sub
    Set rng2 = Sheets("copydata").Cells(1).CurrentRegion
    rng1.Rows(1).Interior.ColorIndex = xlNone
    x = Filter(rng1.Parent.Evaluate("if(isna(match(" & rng1.Rows(1).Address & "," & _
    rng2.Rows(1).Address(, , , 1) & ",0))," & rng1.Rows(1).Address & ")"), False, 0)
    If UBound(x) > -1 Then
        If vbOK = MsgBox("Missing in DataBase" & vbLf & vbLf & _
            Join(x, ":" & vbLf), vbInformation + vbOKCancel, "Continue?") Then
           
            Set rng1 = rng1.Resize(, rng1.Columns.Count + 1)
            x = rng2.Parent.Evaluate("iferror(match(" & rng2.Rows(1).Address & "," & _
                rng1.Rows(1).Address(, , , 1) & ",0)," & rng1.Columns.Count & ")")
            x = Application.Index(rng1, Evaluate("row(2:" & rng1.Rows.Count & ")"), x)
            rng2.Rows(rng2.Rows.Count + 1).Resize(rng1.Rows.Count - 1) = x
        
        Else
           Exit Sub
        End If
    End If
This code is added in the clear old workings below the sub. after which it will clear the data
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
It is not, but I had to improvise it. So, I got another code and added it to the clear old workings code. It is an upgrade, hopefully the last one after I hide all the sheets except PasteData and List of Ledgers sheets.
 
Upvote 0
It is just astonishing. It is working with the single code. I wish I could give you a hug man. I also created a separate button for clear button along with your 2 buttons. Time taken 5 seconds to get the result. Time to wrap up the project till further updates if any. ?????. I really appreciate for writing comments at each line of code. It will really help me to create new projects in future. Thanks for that too.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top