How to use VBA code to enter formula only if sheet exists
Category: [Excel] Demo Available
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
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA to enter formula to a cell without activating the sheet |
| How to change fill color of autoshapes based on result of formula in a cell |
| How to use Formula: =IF ERROR |
| How to use Standard Deviation Formula in Excel |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.