Fill a column automatically RRS feed

  • Question

  • Hi!

    I have an excel sheet with three tabs and on the third tab I need to fill in a column depending on what I have in my two previous tabs.

    To put that simply:


    Role                                                               Privileged
    ReutersDealing_dealer_BID_Spot               FALSE
    ReutersDealing_dealer_MINE_Spot               FALSE
    ReutersDealing_dealer_BID_Forward               FALSE
    ReutersDealing_dealer_MINE_Forward               FALSE
    ReutersDealing_dealer_Spot_CreditLimits       FALSE
    ReutersDealing_dealer_Spot_Payment               FALSE
    ReutersDealing_dealer_Forward_Payment       FALSE
    ReutersDealing_dealer_Forward_CreditLimits      FALSE
    ReutersDealing_dealer_platformcoordinator       TRUE
    ReutersDealing_basic_account                       TRUE
    ReutersDealing_Additional_Personal               TRUE


    User         Role                           

    B18481 ReutersDealing_dealer_Forward_CreditLimits
    B316         ReutersDealing_dealer_BID_Spot
    B316   ReutersDealing_dealer_MINE_Spot
    B316  ReutersDealing_dealer_Spot_CreditLimits
    B316         ReutersDealing_dealer_Spot_Payment
    B316         ReutersDealing_dealer_BID_Forward
    B316         ReutersDealing_dealer_MINE_Forward

    B1775       ReutersDealing_dealer_platformcoordinator


    User                Privileged

    The idea is that it would tell if a user is privileged if this user is assigned to a role which is defined as a privileged one.

    I tried to solve that myself and produced the code below but until now, the code keeps looping according to the number of rows in my Tab1 (Roles) and not the number of user in my Tab3 (ACCOUNTS)

    Sub Privileged()
    ' First part here is to speed up the process
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView
    ' Here is the main part of the macro that writes data
        Dim Roles As Worksheet
        Dim Person_ESet_Mapping As Worksheet
        Dim ACCOUNTS As Worksheet
        Dim r As Long
        Dim m As Long
        Dim t As Long
        Dim OwnerID As String
        Dim Role As String
        Dim Privileged As String
        Dim IsPrivileged As String
        Dim RolesCell As Range
        Dim Person_ESet_MappingCell As Range
        Dim Person_ESet_MappingAddress As String
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Set Roles = Worksheets("Roles")
        Set Person_ESet_Mapping = Worksheets("Person_ESet_Mapping")
        Set ACCOUNTS = Worksheets("ACCOUNTS")
        t = 1
        m = Roles.Range("B" & Roles.Rows.Count).End(xlUp).Row
        For Each RolesCell In Roles.Range("B2:B" & m)
            Role = RolesCell.Value
            Privileged = RolesCell.Offset(0, 3).Value
            Set Person_ESet_MappingCell = Person_ESet_Mapping.Range("A:A").Find(What:=OwnerID, LookAt:=xlWhole)
            If Not Person_ESet_MappingCell Is Nothing Then
                Person_ESet_MappingAddress = Person_ESet_MappingCell.Address
                            t = t + 1
                            ACCOUNTS.Range("M" & t).Value = Privileged
                        Loop Until Person_ESet_MappingCell.Address = Person_ESet_MappingAddress
            End If
        Next RolesCell
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        ActiveWindow.View = ViewMode
        With Application
            .ScreenUpdating = True
            .Calculation = CalcMode
        End With
    End Sub

    Can anyone please help to locate my mistake? :)

    Friday, January 31, 2020 12:53 PM


All replies

  • To:  charliecoch1
    re:  find user status

    Since the term Reuters is on a list, I assume you are dealing with substantial numbers.
    You need to be aware of why nested loops should not be used on large data bases.
    If you have 10,000 users and 5000 roles (or vice versa) then the code has 50,000,000 matches to do.
    You might as well let it run overnight.

    Something not clear to me is:  If user B316 is privileged on one item is B316 privileged on all items?
    Also, at some point you may run into problems using text/variables identical to cell/column header names.

    The new (free) Excel workbook "Professional_Compare" is now available at MediaFire.
    (compares every cell or every row in two worksheets)
    Download from:

    Saturday, February 1, 2020 3:27 AM
  • Futhermore,

    Why won't this do the job...

    • Marked as answer by charliecoch1 Wednesday, February 12, 2020 3:11 PM
    Saturday, February 1, 2020 4:01 AM
  • Hey!

    Sorry, I was gone for a week and couldn't reply earlier.

    Yes, you're right, I was able to solve this with a few formulas here and there! Thank you very much :)

    Wednesday, February 12, 2020 3:11 PM