Msg box error.

[RAJESH1960]

Hello guys,

I am facing 2 error messages in my code.

The first time I press match portal, the error is at this line:

wsDestination.UsedRange.Clear,

Maybe this is due to the destination sheet is not created yet. But the second time I press match portal without doing anything the code runs and gives the correct result. How do I avoid this error.?

The second error is that when the code is running it displays a msgbox in the middle of the code and once again at the end of the code. That is twice while running the code. The msgbox line is written only once in the end of the code. I wonder why...
Need your expert help to make these 2 errors vanish.
Two Error messages to remove.xlsm

 

[Micron]

1) test if the sheet exists first?
2) you'd have to copy and post the code I guess. I followed that link and have no idea what I'm looking at. It doesn't appear to be code.
If you do that, please use code tags (vba button on posting toolbar).

 

[RAJESH1960]

The sheet doesn't exist. It is created by the code. There are many test codes. Please use the match portal code only. The other ones are not connected to this button. I have removed them now and sharing the link again.
Two Error messages to remove.xlsm

 

[rollis13]

Can't realize (but haven't done enough testing) why you get the first error since you have the code to create the sheets if missing:

' Create DestinationSheet if it doesn't exist
    If Not wsDestination Is Nothing Then DestinationSheetExists = True                      ' Check to see if the DestinationSheet exists
    If DestinationSheetExists = False Then                                                  ' If DestinationSheet does not exist then ...
        Sheets.add(after:=wsSource).Name = DestinationSheet                                 '   Create the DestinationSheet after the Source sheet
        Set wsDestination = Sheets(DestinationSheet)                                        '   Assign the DestinationSheet to wsDestination
    End If
'
' Create MatchedSheet if it doesn't exist
    If Not wsMatched Is Nothing Then MatchedSheetExists = True                              ' Check to see if the MatchedSheet exists
    If MatchedSheetExists = False Then                                                      ' If MatchedSheet does not exist then ...
        Sheets.add(after:=wsSource).Name = MatchedSheet                                     '   Create the MatchedSheet after the Source sheet
        Set wsMatched = Sheets(MatchedSheet)                                                '   Assign the MatchedSheet to wsMatched
    End If
'
' Create MismatchesSheet if it doesn't exist
    If Not wsMismatches Is Nothing Then MismatchesSheetExists = True                        ' Check to see if the MismatchesSheet exists
    If MatchedSheetExists = False Then                                                      ' If MismatchesSheet does not exist then ...
        Sheets.add(after:=wsSource).Name = MismatchesSheet                                  '   Create the MismatchesSheet after the Source sheet
        Set wsMismatches = Sheets(MismatchesSheet)                                          '   Assign the MismatchesSheet to wsMismatches
    End If

For the second double MsgBox that's because you are calling the sub twice (for two different headers):

    HeaderTitle = "Integrated Tax"                                                                  ' Set the header title we will look for & sort
    Call SortColumnAndApplyFormulas(HeaderTitle)                                                    ' Pass HeaderTitle to the sub routine
'
    HeaderTitle = "Central Tax"                                                                     ' Set the header title we will look for & sort
    Call SortColumnAndApplyFormulas(HeaderTitle)                                                    ' Pass HeaderTitle to the sub routine

You could move the MsgBox just after these two calls instead of having it inside the called sub:

 MsgBox "Data extracted successfully. Check Mismatched sheet for more Matches."                             '   Display message to user

 

[RAJESH1960]

You could move the MsgBox just after these two calls

Rollis 13. That has solved the msgbox appearing twice problem. Now if I have to test another data, I have to delete the 3 sheets created by the code. And then after inserting a new data in Portal and Purchase register, when I press Match Portal button the first error appears. Please note, after I press end and play the code it runs correctly. But the question is why the error..?

 

[RAJESH1960]

Each time I paste a new data, I get the same error when I run the code for the first time at this line

    wsDestination.UsedRange.Clear                                                                   ' Delete previous contents from destination sheet

 

[RAJESH1960]

Like if the code is written like if the destination sheet exists then clear else ..... it may work I hope.

 

[RAJESH1960]

I just removed these 3 lines and I think it is running now without error. Have to check with the original data.

    'wsDestination.UsedRange.Clear                                                                   ' Delete previous contents from destination sheet
    'wsMatched.UsedRange.Clear                                                                       ' Delete any previous contents from MatchedSheet
    'wsMismatches.UsedRange.Clear                                                                    ' Delete any previous contents from MismatchesSheet

Hopefully, I am not doing any mistake.?

 

[RAJESH1960]

Not working even after removing those lines. Getting error at different line. why? No idea at all.

 

[RAJESH1960]

The weird part is if I don't press debug and press end and play the code again it runs correctly. That is I uncomment the 3 lines.