# Posts by PariD

• ## Select multiple ranges stored in range array

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:

• ## Select multiple ranges stored in range array

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!!!

• ## Custom sort usernames in a list

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,

• ## Custom sort usernames in a list

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.

Cheers,

• ## Compile Error: Expected: line number or label or statement or end of statement

Re: Compile Error: Expected: line number or label or statement or end of statement

Hi rory,
Thanks, that works!!! Much appreciated. :rock:

Cheers,

• ## Compile Error: Expected: line number or label or statement or end of statement

Re: Compile Error: Expected: line number or label or statement or end of statement

Hi ashu1990,

Thanks for your response.

Could you please elaborate on how to put the values into a variant? Sorry if I sound ignorant.

Cheers,

• ## Wildcard search following IF function

Re: Wildcard search following IF function

Hi Markjoh,

See if this works

Cheers

• ## Compile Error: Expected: line number or label or statement or end of statement

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.

• ## Allow user to define the color in conditional formatting

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

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

• ## Allow user to define the color in conditional formatting

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

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

• ## Allow user to define the color in conditional formatting

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.

• ## Allow user to define the color in conditional formatting

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.