VBA Match Function Error Handling/Loop Through Specified Worksheets

[belang46]

Hello all,

I am having a couple issues with a macro i created this week (already received help from this board once, and I appreciate it)...I am very new to this, and I realize the code is probably sloppy...if anyone could please help, any suggestions are welcomed.

My issues are as follows:
1. I used to just have the below code copied 7 times, to individually scan through the 7 sheets I need it to read. I did get it to work that way. It was inefficient and hard to manage, so I looked up some tutorials and tried to make it loop through the specified worksheets. I now keep receiving a type mismatch error (at the line below, colored green). Anyone have any idea what i'm doing wrong? " ++++" 's

2. In my Match function, i receive an error whenever it looks for a value in the sheet and it isn't there...this is to be expected, as some of these sheets are not going to have the attribute I am running the macro for. Is there any type of error handling that would work well for this, or is there some form of if statement i should be using, etc.? The line where I keep receiving this error is denoted with " **** " 's

3. Any other suggestions are appreciated-I'm not even sure in the regular code portion I used the "ws" variable correctly when calling up a worksheet.

THANKS SO MCUH FOR YOUR HELP!!!!

Option Explicit
Sub test()
Dim ws As Worksheet, InputCells As Excel.Range, RowValue As Long, ColumnValue As Long, _
i As Long, j As Integer, K As Long, L As Integer, M As Integer, _
Count As Long, Str As String, Str2 As String, Str0 As String
 
Set InputCells = Application.InputBox(Prompt:="Select Attribute Name", _
                Title:="Attribute", Type:=8)
RowValue = InputCells.Row
ColumnValue = InputCells.Column
For Each ws In ThisWorkbook.Worksheets
 
'++++++Mismatch error at next line...I need the macro to loop through only these tabs in this workbook!****            
 
If ws.Name = "1A" Or "1B" Or "2A" Or "2B" Or "3" Or "4" Or "5" Then
 
'******When it cannot find a MATCH value in the sheet (next line below)
(every sheet does not have every attribute), 
I need it to just disregard and continue to the next sheet...
I've tried different styles of error handling i found in a book, 
but I cannot get it to work.  Also, am I using the "Sheets(ws)" piece correctly?**** 
 
        i = WorksheetFunction.Match(InputCells.Value, Sheets(ws).Range("A:A"), 0)
 
        K = i + 1
 
            Do Until IsEmpty(Sheets(ws).Cells(K + 2, 1).Value)
 
                  K = K + 1
 
            Loop
 
        i = i + 1
 
            For i = i To K
 
                If IsEmpty(Sheets(ws).Cells(i, 7).Value) Then Str2 = Str2
                If Not IsEmpty(Sheets(ws).Cells(i, 7).Value) Then Str = Sheets(ws).Cells(i, 1).Value _
                    & " " & Application.Text(Sheets(ws).Cells(i, 7).Value, "$0;$(0);0")
                If Not IsEmpty(Sheets(ws).Cells(i, 7).Value) Then Str2 = Str2 + ", " + Str
 
            Next i
 
        Str0 = "PMT " + ws.Name
 
        Sheets("Test").Cells(RowValue + 1, ColumnValue) = Str0 + Str2
 
    End If
Next ws
End Sub

 

[MrKowz]

Give this a shot. Your original IF statement (to check the sheet names) had to look like:

If ws.Name = "1A" Or ws.Name = "1B" Or ws.name = "2A" Or etc...

That would have looked kind of messy, so I switched it up and made it a Select Case statement.

Also, the variable "i" needs to be dimmed as a variant in this case, since it is being used as the returned value for the match. When the match returns an error, i cannot handle the error value... thus creating a run-time error. By having it a variant, it is able to store the error, and then right after that, we test for the error.

Sub test()
Dim ws          As Worksheet, _
    InputCells  As Excel.Range, _
    RowValue    As Long, _
    ColumnValue As Long, _
    [B][COLOR=red]i           As Variant, _[/COLOR][/B]
    j           As Integer, _
    K           As Long, _
    L           As Integer, _
    M           As Integer, _
    Count       As Long, _
    Str         As String, _
    Str2        As String, _
    Str0        As String
 
Set InputCells = Application.InputBox(Prompt:="Select Attribute Name", _
                Title:="Attribute", Type:=8)
RowValue = InputCells.row
ColumnValue = InputCells.Column
For Each ws In ThisWorkbook.Worksheets
 
'++++++Mismatch error at next line...I need the macro to loop through only these tabs in this workbook!****
 
    [COLOR=red][B]Select Case ws.Name[/B][/COLOR]
[COLOR=red][B]        Case "1A", "1B", "2A", "2B", "3", "4", "5"[/B][/COLOR]
 
            '******When it cannot find a MATCH value in the sheet (next line below)
            '(every sheet does not have every attribute),
            'I need it to just disregard and continue to the next sheet...
            'i 've tried different styles of error handling i found in a book,
            'but I cannot get it to work.  Also, am I using the "Sheets(ws)" piece correctly?****
 
            i = WorksheetFunction.Match(InputCells.Value, Sheets(ws).Range("A:A"), 0)
            [COLOR=red][B]If Not IsError(i) Then[/B][/COLOR]
               K = i + 1
 
                   Do Until IsEmpty(Sheets(ws).Cells(K + 2, 1).Value)
 
                         K = K + 1
 
                   Loop
 
               i = i + 1
 
                   For i = i To K
 
                       If IsEmpty(Sheets(ws).Cells(i, 7).Value) Then Str2 = Str2
                       If Not IsEmpty(Sheets(ws).Cells(i, 7).Value) Then Str = Sheets(ws).Cells(i, 1).Value _
                           & " " & Application.Text(Sheets(ws).Cells(i, 7).Value, "$0;$(0);0")
                       If Not IsEmpty(Sheets(ws).Cells(i, 7).Value) Then Str2 = Str2 + ", " + Str
 
                   Next i
 
               Str0 = "PMT " + ws.Name
 
               Sheets("Test").Cells(RowValue + 1, ColumnValue) = Str0 + Str2
            [B][COLOR=red]Else[/COLOR][/B]
[B][COLOR=red]                MsgBox "Value not found.  Continuing with macro"[/COLOR][/B]
[B][COLOR=red]            End If[/COLOR][/B]
[B][COLOR=red]        Case Else[/COLOR][/B]
    [B][COLOR=red]End Select[/COLOR][/B]
Next ws
End Sub

 

[belang46]

MrKowz...thanks for the quick response.

I am actually still getting an error in MATCH function...the same type mismatch, in the same place. I copied it exactly as you replied to me, so not sure what i'm doing wrong...anything else I should consider?

Thanks again.

 

[MrKowz]

MrKowz...thanks for the quick response.

I am actually still getting an error in MATCH function...the same type mismatch, in the same place. I copied it exactly as you replied to me, so not sure what i'm doing wrong...anything else I should consider?

Thanks again.

Ahh, I didn't look deep enough. You had Sheets(ws)... ws is a sheet reference, and doesn't need to be encased with Sheets. Also, for this purpose, use Application.Match, not WorksheetFunction.Match. By using Application instead of WorksheetFunction, it will return a VBA error which we can test with the error handler.

Change that line to:

i = Application.Match(InputCells.Value, ws.Range("A:A"), 0)

 

[belang46]

Thanks...that worked perfectly. I now am almost in business, but one last question if you don't mind (You are a huge help, let me know if I can submit any recognition/recommendation for you to any websites).

In some instances, an attribute on a tab will have no dollar amount associated with it. In that case, I don't want it to be part of my text output. I thought I accomplished this with the first if statement in the "For I" loop...however, I still get output like the below:

[Item 1 $(54), Item 2 $(24), Item 3 0]

I would hope to show the first 2 items, but item 3 should not be returned. Is there another sort of if statement that may work?

Thanks again, I really appreciate your help.

 

[MrKowz]

I believe this will do what you need. Changes highlighted in red.

Sub test()
Dim ws          As Worksheet, _
    InputCells  As Excel.Range, _
    RowValue    As Long, _
    ColumnValue As Long, _
    i           As Variant, _
    j           As Integer, _
    K           As Long, _
    L           As Integer, _
    M           As Integer, _
    Count       As Long, _
    Str         As String, _
    Str2        As String, _
    Str0        As String
 
Set InputCells = Application.InputBox(Prompt:="Select Attribute Name", _
                Title:="Attribute", Type:=8)
RowValue = InputCells.row
ColumnValue = InputCells.Column
For Each ws In ThisWorkbook.Worksheets
 
'++++++Mismatch error at next line...I need the macro to loop through only these tabs in this workbook!****
 
    Select Case ws.Name
        Case "1A", "1B", "2A", "2B", "3", "4", "5"
             
            '******When it cannot find a MATCH value in the sheet (next line below)
            '(every sheet does not have every attribute),
            'I need it to just disregard and continue to the next sheet...
            'i 've tried different styles of error handling i found in a book,
            'but I cannot get it to work.  Also, am I using the "ws" piece correctly?****
             
            i = Application.Match(InputCells.Value, ws.Range("A:A"), 0)
            If Not IsError(i) Then
               K = i + 1
        
                   Do Until IsEmpty(ws.Cells(K + 2, 1).Value)
        
                         K = K + 1
        
                   Loop
        
               i = i + 1
        
                   For i = i To K
        
                      [COLOR=red][B] 'If IsEmpty(ws.Cells(i, 7).Value) Then Str2 = Str2 ' I commented out this line, because it doesn't appear to do anything useful.
                       If Not IsEmpty(ws.Cells(i, 7).Value) Or ws.Cells(i, 7).Value <> 0 Then
                           Str = ws.Cells(i, 1).Value & " " & Application.Text(ws.Cells(i, 7).Value, "$0;$(0);0")
                           Str2 = Str2 + ", " + Str
                       End If
[/B][/COLOR]        
                   Next i
        
               Str0 = "PMT " + ws.Name
        
               Sheets("Test").Cells(RowValue + 1, ColumnValue) = Str0 + Str2
            Else
                MsgBox "Value not found.  Continuing with macro"
            End If
        Case Else
    End Select
Next ws
End Sub

As far as recognition/reccomendation, being a volunteer on these forums, a simple "thanks" is all I ask.

 

[belang46]

Thanks MrKowz. I'm all set for the time being.

I ended up slightly altering, as I was still receiving some 0 values...i feel it may have been the format of the data (text instead of numbers perhaps)

If ws.Cells(i, 7).Value <> 0 Then

That did the trick...just deleted the first half of your proposed solution.

Couldn't have gotten here without your help...Thanks again!