Hi,
I have two separate tables (one is a list of Users and User Profiles and the other is a corresponding list of User Profiles and what rights each has)
TABLE 1 - Users and User Profiles
<tbody>
</tbody>
TABLE 2 - User Profiles and Rights
<tbody>
</tbody>
What I am after is a list of the Rights each User has in the next coulmn of Table 1 and then offsetting by one column when there is multiple Rights (See below).
<tbody>
</tbody>
I have got to the point where i have got the code (see below) to look down one column of User Profiles and return all the Rights but now I am a little stuck.
Any help greatly received, thanks!
I have two separate tables (one is a list of Users and User Profiles and the other is a corresponding list of User Profiles and what rights each has)
TABLE 1 - Users and User Profiles
USERS | Accounts | Admin | Master Roster | Extrant | Basic MIS | MIS | Sunk VTS | VTS |
User 1 | X | |||||||
User 2 | X | |||||||
User 3 | X | X | X | |||||
User 7 | X | |||||||
User 8 | X | X | X | |||||
User 9 | X | X | X | X | X | X |
<tbody>
</tbody>
TABLE 2 - User Profiles and Rights
RIGHTS | Accounts | Admin | Master Roster | Extrant | Basic MIS | MIS | Sunk VTS | VTS | |
<tbody> </tbody> | X | X | X | X | X | ||||
Pilotage Read | X | X | |||||||
Roster Write | X | X | X | X | |||||
Roster Read | X | X | X | ||||||
Security Write | X | ||||||||
Security Read | X | X |
<tbody>
</tbody>
What I am after is a list of the Rights each User has in the next coulmn of Table 1 and then offsetting by one column when there is multiple Rights (See below).
USERS | Accounts | Admin | Master Roster | Extrant | Basic MIS | MIS | Sunk VTS | VTS | Right 1 | Right 2 | Right 3 |
User 1 | x | Pilotage Write | Roster Write | Security Write |
<tbody>
</tbody>
I have got to the point where i have got the code (see below) to look down one column of User Profiles and return all the Rights but now I am a little stuck.
Code:
Sub SearchX()
Dim LR As Long, i As Long, LC As Long
With Sheets("Roles")
LR = .Range("D" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("D" & i)
If .Value = "x" Then
Sheets("Roles").Range("C" & i).Copy Destination:=Sheets("Roles").Range("AN" & i)
End If
End With
Next i
End With
End Sub
Any help greatly received, thanks!