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

 

[rollis13]

You are initializing your variable wsDestination here:

Option Explicit

Dim DestinationLastRow          As Long
Dim DestinationRemarksColumn    As String
Dim wsDestination               As Worksheet

Sub Match_Portal()

This would be okay since you use it in both subs but when for some reason you delete the sheet "Edited Portal" the variable will still be "True" and will not get your code which recreates the sheet to work:

' 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

 

[RAJESH1960]

Do you mean to say that I shouldn't delete the 3 created sheets to match the next data.?

 

[rollis13]

The only quick workaround is to set the variable wsDestination to Nothing at the beginning of the sub Match_Portal.

 

[RAJESH1960]

Yes Yes Yes. I tested 3 times after replacing true with false and It worked without error. Thank you so much rollis 13.?

 

[rollis13]

Sorry, I edited my last post in the meantime, please check, should be:

Set wsDestination = Nothing

 

[RAJESH1960]

Sorry, I edited my last post in the meantime, please check, should be:

Set wsDestination = Nothing

If Not wsDestination Is Nothing Then DestinationSheetExists = False                      ' Check to see if the DestinationSheet exists
    If DestinationSheetExists = False Then

I edited the above line. Ain't that right?

 

[rollis13]

Not sure, needs testing but I would just leave that part as it was and add Set wsDestination = Nothing at the begining of the macro.

 

[RAJESH1960]

I changed again like you said and changed false back again to true. Still it is working right.

Set wsDestination = Nothing                                            ' Assign DestinationSheet to wsDestination
         Set wsSource = Sheets(SourceSheet)                                                 ' Assign SourceSheet to wsSource
        Set wsMatched = Sheets(MatchedSheet)                                                ' Assign MatchedSheet to wsMatched
     Set wsMismatches = Sheets(MismatchesSheet)                                             ' Assign MismatchesSheet to wsMismatches
    On Error GoTo 0                                                                         ' Turn Excel error handling back on
'
' 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

 

[RAJESH1960]

It is working fine in both the sample and original data. Thanks once again rollis 13. Good Night.?

 

[rollis13]

The logic of your change in post #16 wasn't correct.
Set wsDestination = Nothing goes before On Error Resume Next
Anyway, glad having been of some help.