Posts by PariD

    Re: Select multiple ranges stored in range array


    Hi!!


    Managed to find a workaround. Not sure if this is the most efficient method but here goes in case some one needs to use it:


    Hi,


    I am trying to do something that sounds fairly straightforward but have not been able to achieve the final result.


    I have a sheet which has multiple range addresses stored in different cells and so far I have been able to pass the Range address to Range variables by looping through a range array. Now I would like to select these multiple ranges. My code looks as below;


    Code
    Dim Cnt as Integer    Cnt = Sheets("Sheet1").(Range("1040").Value ' Cnt holds the the total number of ranges to be specified in Sheet1    Dim RStr(1 To 80) As String ' This will hold the range addresses specified in Sheet1 in cells C1041 to C1120. Upto 80 range addresses could be specified. [size=10]    Dim Rng(1 To 80) As Range ' This will store the ranges based on the range addresses stored in RStr()[/SIZE]    Dim i, j As Integer,     Set Rng(1) = Sheets("Sheet1").Range("C1040")    j = 1    For i = 1 To Cnt        If Rng(1).Offset(i, 0).Value <> "" Then            RStr(j) = Rng(1).Offset(i, 0).Value            j = j + 1        End If    Next i        j = j - 1    For i = 1 To j        Sheets("Sheet2").Select        Set Rng(i) = Range(RStr(i))    Next iUnion(Rng(j-1)).select ' j-1 is the size of the array

    [/SIZE]


    I am not sure on how to select multiple ranges using range array hence tried Union() however it is obviously not correct and throws an error.


    Any help is welcome please.


    Cheers!!!

    Re: Custom sort usernames in a list


    Quote from holycow;687980

    It also assumes Column C is blank and can be used to contain a temporary sort formula. If Column C is not blank then change it for a different column.


    Edit: If you change to another Column other than C, then also change the column number to sort by.


    Hi Holycow,


    This works!!! :)


    Had been struggling with this for 2 days and the solution you have given is so simple & effective. THANKS A TON!!!


    Cheers,

    Hi,


    I have a set of data with username and corresponding units for each user. I would like to sort this data in such a way that the 1st set would include the list of unique users whose names have occured for the first time in the list, the 2nd set would be where their names have occured a 2nd time ... and so on. Below is the sample of what I would like to achieve:


    [TABLE="width: 282"]

    [tr]


    [TD="class: xl66, width: 145, bgcolor: transparent, colspan: 2, align: center"]Exisiting list[/TD]
    [TD="width: 64, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl66, width: 166, bgcolor: transparent, colspan: 2, align: center"]Desired sorting[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent, align: center"][/TD]
    [TD="bgcolor: transparent, align: center"][/TD]
    [TD="bgcolor: transparent, align: center"][/TD]
    [TD="bgcolor: transparent, align: center"][/TD]
    [TD="bgcolor: transparent, align: center"][/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]User Name[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Units[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]User Name[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Units[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Adam[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]1[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Adam[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Adam[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]4[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Eve[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]8[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Adam[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]2[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Paul[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]7[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Adam[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]6[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]3[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Eve[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]8[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Sam[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]2[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Eve[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]4[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Adam[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]4[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Eve[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]1[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Eve[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]4[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Eve[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]3[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Paul[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]8[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Eve[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]5[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Eve[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]6[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Adam[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]2[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Paul[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]7[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Eve[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Paul[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]8[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]3[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]3[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Adam[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]6[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]1[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Eve[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]3[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]3[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]4[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]4[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Eve[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]5[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]9[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]9[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]8[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Eve[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]6[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]4[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]8[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]5[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]4[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]Sam[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]2[/TD]
    [TD="class: xl66, bgcolor: transparent, align: center"][/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]Kelly[/TD]
    [TD="class: xl65, bgcolor: transparent, align: center"]5[/TD]

    [/tr]


    [/TABLE]


    Would be grateful if someone can share the macro that can help me achieve this.


    Thanks in advance.


    Cheers,

    Re: Wildcard search following IF function


    Hi Markjoh,


    See if this works



    Cheers

    Hi,
    I am trying to pass the column value of cells with values not equal to zero to variables F1, F2, F3 , F4, F5 & F6. The code is as under;

    Code
    Dim i, F1, F2, F3, F4, F5, F6 As Integer
    Set TsRng = Range("E3:J3")
    i = 1
    For Each Ts In TsRng
        If Ts.Value <> 0 Then
            "F" & i = Ts.Column
            i = i + 1
        End If
    Next


    I get a compile error message for the line "F" & i = Ts.column. Not sure what I am doing wrong here. Please help.
    Thanks in advance.

    Re: Allow user to define the color in conditional formatting


    Hi!!! I had missed out mentioning that I had also posted my query on Excel forum


    Link : http://www.excelforum.com/exce…formatting.html?p=2784170


    Sorry for the inconvenience.


    While I have neither got any response on this thread nor at excel forum (I know its been a very short while since I posted :), I managed to get a working solution by referring to other threads. Thanks a lot.


    I am sharing the code (am giving 3 conditions out of 14 to save space) in case it is of use to anyone else.


    Thanks.



    Note: I have given the User an option for choosing color in a separate sheet (Sheet 2) and the conditional formatting code will reference it from there. In case color is to be chosen in the same sheet, I have commented the code for that in condition 1


    Re: Allow user to define the color in conditional formatting


    Hi!!! I had missed out mentioning that I had also posted my query on Excel forum


    Link : http:// http://www.excelforum.com/excel-programming/829943-script-which-allows-user-to-define-the-color-in-conditional-formatting.html?p=2784170</a>


    Sorry for the inconvenience.


    While I have neither got any response on this thread nor at excel forum (I know its been a very short while since I posted :), I managed to get a working solution by referring to other threads. Thanks a lot.


    I am sharing the code (am giving 3 conditions out of 14 to save space) in case it is of use to anyone else.


    Thanks.



    Note: I have given the User an option for choosing color in a separate sheet (Sheet 2) and the conditional formatting code will reference it from there. In case color is to be chosen in the same sheet, I have commented the code for that in condition 1


    Re: Allow user to define the color in conditional formatting


    I have recorded the conditional formatting macros as under and need help to assign color for condition 1 as the color of cell B2, condition 2 as the color of cell B3, condition 3 as the color of cell B4 & so on and so forth...


    Tried googling for a solution but have not found any so far. As mentioned earlier, I am a novice at VBA & macros and would really appreciate a little help on this. Thanks in advance.


    Hi there!!!


    I am faily good with excel formulas but a complete novice with macros and VBA.


    I am working in excel 2010 and have created a worksheet which color codes timelines i.e. I have put in conditional formatting on a range of cells where 14 criteria are evaluated and 1 color is defined for each criteria.


    In this sheet the color for each criteria has been defined by me however I would like to give user the option for choosing the color for each criteria.