none
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:

    Tab1:

    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

    Tab2:

    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

    Tab3:

    User                Privileged
    B24893
    B316
    B2211
    B18481
    B316
    B1775
    B34975
    B76166

    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
        Range("M2:M10031").Select
        Selection.ClearContents
        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
                        Do
                            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

Answers

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.
    '---
    NLtL

    The new (free) Excel workbook "Professional_Compare" is now available at MediaFire.
    (compares every cell or every row in two worksheets)
    Download from:
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents


    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