Use tab1 if it exists, otherwise tab2

Question

Hi I currently have numbers pulled from the Value tab in an Excel file. But I would like to pull it from Tab2, if Tab2 exist in that file. could somewhere suggest how to do something like if Tab2 exist, use Tab2 first, otherwise use the Value tab?

Workbooks(wkbk_value).Sheets("Value").Select
amount= WorksheetFunction.Match("Date", Rows("5:5"), 0)
price = WorksheetFunction.Match("Calculated", Rows("4:4"), 0)
time= WorksheetFunction.Match("Selected", Rows("3:3"), 0)

Are you looking into another workbook or working with one workbook which the code is in? — QHarr, Jun 05 '18 at 19:32

QHarr · Answer 1 · 2018-06-05T19:39:33.417

Notes:

Put Option Explicit at the top of your code and make sure you declare all your variables. For example, where is wkbk_value declared and assigned?
Avoid .Select and use With statement to work with a sheet
Consider adding error handling for matches not found by declaring variants and then testing the values of these for error values
Use the Worksheets collection generally rather than Sheets unless wishing to include Chart sheets.
Match, if found, will return a number (the position where found in the range). It looks more like you want to return a value so be aware of this.

Code:

Using a method by @TimWilliams

Option Explicit

Public Sub test()
    Dim amount As Variant, Price As Variant, Time As Variant, wkbk_value As String '<== Declare your variables with appropriate type
    wkbk_value = "Testing.xlsb"

    If SheetExists("Tab2", Workbooks(wkbk_value)) Then
        'Code here for Tab2
        MsgBox "EXISTS"
    Else
        With Workbooks(wkbk_value).Worksheets("Value") '<==use With statement not .Select
            'Consider how to handle not being found?
            amount = Application.Match("Date", .Rows("5:5"), 0)
            Price = Application.Match("Calculated", .Rows("4:4"), 0)
            Time = Application.Match("Selected", .Rows("3:3"), 0)
        End With
    End If

End Sub
Public Function SheetExists(ByVal shtName As String, Optional ByRef wb As Workbook) As Boolean
    Dim sht As Worksheet

     If wb Is Nothing Then Set wb = ThisWorkbook
     On Error Resume Next
     Set sht = wb.Sheets(shtName)
     On Error GoTo 0
     SheetExists = Not sht Is Nothing
 End Function

This method should still work for you. Just change any workbook references to the ones you need. — QHarr, Jun 08 '18 at 19:15
hmm I have tried to incorporate this into the existing marco, but it didn't work. maybe because I'm inserting this into a long macro? thanks — BurtBee, Jun 08 '18 at 20:37
What do you mean by doesn't work? The above is pretty easy. This is the bare bones of it to run https://pastebin.com/dGAftZ5r — QHarr, Jun 08 '18 at 20:44
Do i insert this into the rest of the macro ( I didn't post all the codes)? or should the first two lines be on the very top? Can you end sub within another already existence end sub? Thanks ! — BurtBee, Jun 08 '18 at 20:56

Subodh Tiwari sktneer · Answer 2 · 2018-06-14T15:37:44.223

0

You may try something like below.

I have declared the variables as variant and changed the WorksheetFunction with Application so that if lookup value is not found, the variables will hold the error number so that you can check whether the Match function returns a value or an error if required.

Dim ws As Worksheet
Dim Amount, Price, Time

On Error Resume Next
Set ws = Workbooks(wkbk_value).Sheets("Tab2")
On Error GoTo 0
If ws Is Nothing Then Set ws = Workbooks(wkbk_value).Sheets("Value")
With ws
    Amount = Application.Match("Date", .Rows("5:5"), 0)
    Price = Application.Match("Calculated", .Rows("4:4"), 0)
    Time = Application.Match("Selected", .Rows("3:3"), 0)
End With
End Sub

Your edited code:

Dim ws As Worksheet
Dim Amount, Price, Time
On Error Resume Next
Set ws = Workbooks(wkbk_value).Sheets("Tab2")
Amount = Application.Match("Date", ws.Rows("5:5"), 0)
On Error GoTo 0
If ws Is Nothing Then
    Set ws = Workbooks(wkbk_value).Sheets("Value")
    Amount = Application.Match("Date", ws.Rows("5:5"), 0)
End If

Notice that in your code, you have two lines calculating the Amount where as if you focus to set the worksheet (ws) first, you will need to write that line only once and it will be calculated correctly based on worksheet set earlier as shown in the first code.

edited Jun 14 '18 at 15:37

answered Jun 05 '18 at 19:49

Subodh Tiwari sktneer

9,906
2
18
22

hmm I have tried to incorporate this into the existing marco, but it didn't work. maybe because I'm inserting this into a long macro? thanks – BurtBee Jun 08 '18 at 20:37
Did you get the concept how to set a worksheet and use this in your code? That was your original question. – Subodh Tiwari sktneer Jun 09 '18 at 04:45
i think i understand your codes, wasn't sure how to incorporate into the really long codes that i was given (i just copied and pasted a small chunk of it in the question). so if tab 2 is found, can i do this - "Dim ws As Worksheet Dim Amount, Price, Time On Error Resume Next Set ws = Workbooks(wkbk_value).Sheets("Tab2") Amount = Application.Match("Date", .Rows("5:5"), 0) On Error GoTo 0 If ws Is Nothing Then Set ws = Workbooks(wkbk_value).Sheets("Value") With ws Amount = Application.Match("Date", .Rows("5:5"), 0) End With End Sub" – BurtBee Jun 14 '18 at 14:51
Yes, you can use that if you understood the logic. BTW you don't need to use With block for one line statement. You can remove it and just use `Amount = Application.Match("Date", ws.Rows("5:5"), 0)`. Also, the code you posted is missing an `End If`, I am sure you are aware of that. – Subodh Tiwari sktneer Jun 14 '18 at 15:31
I have also edited my answer and added your code along with some explanation as well. – Subodh Tiwari sktneer Jun 14 '18 at 15:38
Thank you, I will give that a try : ) – BurtBee Jun 15 '18 at 16:34
oh the actual one is multiple line statements . Thank you! – BurtBee Jun 18 '18 at 22:03

Use tab1 if it exists, otherwise tab2

2 Answers2