How to VBA - Enter Formula only if Sheet Exists

How to use VBA code to enter formula only if sheet exists

Requirement:

The user is trying to write a macro that enters a Vlookup into a cell only if a corresponding tab exists on another workbook to compare it to. The user is trying doing this to try to avoid the 'Select Sheet' Dialogue from appearing if there is no corresponding sheet on the other workbook.

This is what the user has so far (the Function comes from here https://stackoverflow.com/questions/...twsname-exists)

Code:

Sub MySub()

Dim PreviousReport As String

If SheetExists(PreviousReport & ActiveSheet.Name) Then
    LastRow = .Cells(Rows.Count, 3).End(xlUp).Row
     ActiveSheet.Range("I7:I" & LastRow).Value = "=IFERROR(IF(VLOOKUP($B7,'[" & PreviousReport & "]" _
          & ActiveSheet.Name & "'!$B:$J,8,0)="""","""",VLOOKUP($B7,'[" & PreviousReport & "]" & ActiveSheet.Name & "'!$B:$I,8,0)),"""")"
Else
End If

End Sub

Function SheetExists(SheetToFind As String) As Boolean
Dim Sheet As Object
    SheetExists = False
    For Each Sheet In Worksheets
        If SheetToFind = Sheet.Name Then
            SheetExists = True
            Exit Function
        End If
    Next Sheet
End Function

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/149701-vba-enter-formula-only-if-sheet-exists

The code works in that it doesn't enter a Vlookup if the sheet doesn't exists but it also doesn't recognise if the sheet does exist so the user thinks the issue is with setting the SheetToFind.

Solution:

Try this

Code:

Sub MySub()
    Dim PreviousReport As String, LastRow As Long
    
    PreviousReport = "Book2" '// CHANGE THIS TO THE NAME OF THE WORKBOOK THAT NEEDS TO BE SEARCHED
    
    If SheetExists(PreviousReport, ActiveSheet.Name) Then
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, 3).End(xlUp).Row
            .Range("I7:I" & LastRow).Value = "=IFERROR(IF(VLOOKUP($B7,'[" & PreviousReport & "]" _
            & ActiveSheet.Name & "'!$B:$J,8,0)="""","""",VLOOKUP($B7,'[" & PreviousReport & "]" & ActiveSheet.Name & "'!$B:$I,8,0)),"""")"
        End With
    Else
        MsgBox PreviousReport & " does not have a sheet named " & ActiveSheet.Name, 64, "Previous Report"
    End If
     
End Sub

Function SheetExists(wb As String, sh As String) As Boolean
    Dim ws As Object
    SheetExists = 0
    
    For Each ws In Workbooks(wb).Sheets
        If sh = ws.Name Then
            SheetExists = 1
            Exit Function
        End If
    Next
    
End Function

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.