VBA - Enter Formula only if Sheet Exists

[vba_monkey]

Hello,

I am trying to write a macro that enters a Vlookup into a cell only if a corresponding tab exists on another workbbok to compare it to. I'm 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 I have so far (the Function comes from here Excel VBA If WorkSheet("wsName") Exists - Stack Overflow

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

The code works in that it doesnt enter a Vlookup if the sheet doesnt exists but it also doesnt recognise if the sheet does exist so I think the issue is with setting the SheetToFind.

Any ideas please?

Also posted here VBA - Enter Formula only if Sheet Exists

 

[JLGWhiz]

If SheetExists(PreviousReport & ActiveSheet.Name) Then

This does not look right for two reasons. 1) 'PreviousReport' variable is not defined or initialized in the posted code. 2)Even if PreviousReport is a Public Variable or Public Constant, the concatenation with the ActiveSheet.Name would join the two strings without any type of separator (this could be the desired result, but questionable). So if the statement is not producing a valid sheet name, then the statement will always be False. Then the LastRow variable uses

.Cells(Rows.Count, 3).End(xlUp).Row

to initialize. The posted code could be an excerpt from a larger body of code, but if not then the period in front of Cells should be throwing an error since there is no parent evident in the posted code.
I stopped at that point.