CODEWITHSUNDEEP
excelvba
Carly
Carly

Reputation: 17

Excel VBA - how to use Match with a matrix

I'm trying to use Excel VBA to iterate through the rows of a spreadsheet called User List, retrieve a value in that row and compare it to another spreadsheet called Permissions. The value is the row number that the program needs to look on Permissions. Permissions is a matrix that has x's under the columns of the specific permission the row retrieved from User List needs. I want to get those values of the columns with x's and put it in a list in an output spreadsheet.

I'm completely lost on how to do this. I gave it a shot, but I am stuck on using match. What do I need to match and how do I access the values of column of the cells with x's?

Sub Test()
     Dim i As Integer
     lRowUser = Sheets("User List").Cells(Sheets("User List").Rows.Count, 1).End(xlUp).Row
     For i = 2 To lRowUser
        Sheets("Output").Cells(i,2).Value = WorksheetFunction.Match(Cells(..
    Next i
End Sub

Upvotes: 0

Views: 163

Answers (1)

rxex
rxex

Reputation: 485

Perhaps this may get you started? I tested it in a mock book and it seems to work. I know that this solution does not use Match, which is what you asked about, but I didn't think that Match would be the right approach to find the solution to your problem.

Sub Test()
 
 Dim i As Integer
 Dim perm As String
 
 
 Set userSht = Sheets("User List")
 Set permSht = Sheets("Permission Set List")
 Set outpSht = Sheets("Output")

 lRowUser = userSht.Cells(Rows.Count, 1).End(xlUp).Row
 maxColPerm = permSht.Cells(1, Columns.Count).End(xlToLeft).Column
 outpLine = outpSht.Cells(Rows.Count, 1).End(xlUp).Row + 1
      
 For i = 3 To lRowUser
 
    permLine = userSht.Cells(i, 6)
    UserName = userSht.Cells(i, 1)
    'the user's permission code is on the 6th column of the User List, indicating
    'the line in the Permission Set List that corresponds to the user
            
        For j = 5 To maxColPerm
        'permission codes seem to be listed on the first line, starting 
        'on column 5 of the Permission Set List
            
            If permSht.Cells(permLine, j).Value = "x" Then
                perm = permSht.Cells(1, j).Value
                outpSht.Cells(outpLine, 2) = perm
                outpSht.Cells(outpLine, 1).Value = UserName
                outpLine = outpLine + 1
            End If
        
        Next j
    
Next i

End Sub

Upvotes: 1

Related Questions