vba use match function result as column number

[akramer08]

I currently have a macro that loops through all each column on a sheet. Within that loop it will find the value of the column header and row header. It then will find that column header value in a table on a different sheet. The next point of the macro should look for the original row header value and return the row number, but I seem to be getting caught up in using the returned column number as a column reference number in the remainder of the code. Any ideas?

Sub Macro3()


Dim r As Long
Dim c As Long
Dim cols As Range
Dim rows As Range
Dim colmatch As Long
Dim rowcheck As Range
Dim rowmatch As Long
Dim rFind As Range


For c = 2 To 245 Step 1
    For r = 2 To 611 Step 1
        colhead = Cells(1, c).Value
        rowhead = Cells(r, 1).Value
        
        On Error Resume Next
        colmatch = WorksheetFunction.Match(colhead, Sheets("usethis").Range("A1:FDX1"), 0)
        Set rowcheck = Sheets("usethis").Range(Cells(2, colmatch), Cells(3821, colmatch))


        rowmatch = WorksheetFunction.Match(rowhead, rowcheck, 0)
        If rowmatch = 0 Then
            Cells(r, c).Value = "-"
        Else
            Cells(r, c).Value = Sheets("usethis").Cells(rowmatch, colmatch).Value
        End If


    Next r
Next c


End Sub

 

[Norie]

The first thing I would suggest doing is declaring colmatch and rowmatch as Variant rather than Long.

Then remove the On Error Resume Next and use Application.Match instead of WorksheetFunction.Match.

Now when a match isn't found you'll get error values in colmatch/rowmatch and you can add code to check for those.

 

[akramer08]

Made the changes you suggested. The issue seems to still be roughly the same. When setting the "rowcheck" it seems like it doesnt accept the values returned from the previous match function as a column number. I ran the code below. colmatch is returned as value 265, then the error is on the line containing "Set rowcheck" highlighted in red below and states that it equals nothing. If I hover over "colmatch" on that line it even shows 265.

Sub Macro3()


Dim r As Long
Dim c As Long
Dim cols As Range
Dim rows As Range
Dim colmatch As Variant
Dim rowcheck As Range
Dim rowmatch As Variant
Dim rFind As Range


For c = 2 To 245 Step 1
    For r = 2 To 611 Step 1
        colhead = Cells(1, c).Value
        rowhead = Cells(r, 1).Value
        
        colmatch = Application.WorksheetFunction.Match(colhead, Sheets("usethis").Range("A1:FDX1"), 0)
[COLOR=#ff0000]        Set rowcheck = Sheets("usethis").Range(Cells(2, colmatch), Cells(3821, colmatch))[/COLOR]


        rowmatch = WorksheetFunction.Match(rowhead, rowcheck, 0)
        If rowmatch = 0 Then
            Cells(r, c).Value = "-"
        Else
            Cells(r, c).Value = Sheets("usethis").Cells(rowmatch, colmatch).Value
        End If


    Next r
Next c


End Sub

 

[Norie]

The problem could be that you don't have a worksheet reference for Cells here.

Set rowcheck = Sheets("usethis").Range(Cells(2, colmatch), Cells(3821, colmatch))

Try this.

With Sheets("usethis")
    Set rowcheck = .Range(.Cells(2, colmatch), .Cells(3821, colmatch))
End With

By the way, Match doesn't return 0 if there's no match, it returns an error.

 

[akramer08]

Thanks Norie. After adding an error handling statement everything works great.