Need help to edit code

[RAJESH1960]

Hello guys
This code was written by JohnnyL. It is a perfect code but I forgot to mention one condition in my query. So I need help to edit the code. The code runs correctly when there is a #N/A error in column F in the List of ledgers sheet. I want to add one condition in it. If there is not a single error in column F then the code should display a message box stating that "All Ledgers Available.". Right now the code shows an error when there is no error in column F at this line

 Sheets("MasterData").Range("B2").Resize(.Count) = Application.Transpose(.keys)

Query Code to get Ledger names from another sheet.xlsm

 

[RAJESH1960]

If I delete Mercury from the list in LIst of Ledgers sheet column A then the code runs right without any error.

 

[johnnyL]

Replace:

        Sheets("MasterData").Range("B2").Resize(.Count) = Application.Transpose(.keys)                              ' Display unique values on Sheets("MasterData")
    End With
'
    Application.ScreenUpdating = True                                                                               ' Turn ScreenUpdating back on
'
    CodeCompletionTime = Timer - StartTime                                                                          ' Stop the stop watch
    CodeCompletionTime = Format(CodeCompletionTime, ".#####")                                                       ' Prevent scientific notation results
    Debug.Print "Time to complete MoveDataToDifferentSheets = " & CodeCompletionTime & " seconds."                  ' Display the time elapsed to the user (Ctrl-G)
'
    Application.Speech.Speak "This code completed in, , , " & CodeCompletionTime & " seconds."                      ' Provide audio result
End Sub

With

        If .Count > 0 Then
            Sheets("MasterData").Range("B2").Resize(.Count) = Application.Transpose(.keys)                          ' Display unique values on Sheets("MasterData")
        Else
            CodeCompletionTime = Timer - StartTime                                                                  ' Stop the stop watch
            MsgBox "All Ledgers Available."                                                                         ' Display message to user
            GoTo Continue                                                                                           ' Jump to Continue:
        End If
    End With
'
    CodeCompletionTime = Timer - StartTime                                                                          ' Stop the stop watch
'
Continue:
    Application.ScreenUpdating = True                                                                               ' Turn ScreenUpdating back on
'
    CodeCompletionTime = Format(CodeCompletionTime, ".#####")                                                       ' Prevent scientific notation results
    Debug.Print "Time to complete MoveDataToDifferentSheets = " & CodeCompletionTime & " seconds."                  ' Display the time elapsed to the user (Ctrl-G)
'
    Application.Speech.Speak "This code completed in, , , " & CodeCompletionTime & " seconds."                      ' Provide audio result
End Sub

 

[RAJESH1960]

Perfect. Just like you JohnnyL.?

 

[johnnyL]

Glad to help @RAJESH1960.