Filling an array with specific cells

austin350s10

Active Member
Joined
Jul 30, 2010
Messages
321
I am trying to use the below code to fill a combobox with unique values. It works great except I can't figure out a way to replace BLOCK 1 with BLOCK 2. I want to use BLOCK 2 to populate mgNames so that only specific values are filled into mgNames. Currently BLOCK 1 just fills mgNames with an entire range without any conditions. Anyone have an idea how I can get BLOCK 2 to work in the way i'm intending it to?

Code:
Sub findNames()

Dim mgNames As Variant
Dim myCollection As New Collection
Dim temp As Variant

'so far I can get BLOCK 1 to fill mgNames with a range but I want to fill it with specific values with the condition in BLOCK 2.

'BLOCK 1 start ---------------------------
With ActiveSheet
    mgNames = Range(.Cells(2, 4), .Cells(.Rows.Count, 4).End(xlUp)).Value
End With
'BLOCK 1 end ----------------------------

'BLOCK 2 start ---------------------------
For Each c In Range(.Cells(2, 4), .Cells(.Rows.Count, 4).End(xlUp))
    If Trim(c.Offset(0, 7).Value) = "Hourly" Then
    
'looking for a way to fill mgNames with just the cells that meet the condition
    
    End If
Next c
'BLOCK 2 end ----------------------------

On Error Resume Next
For Each temp In mgNames
    myCollection.Add Item:=temp, Key:=temp
Next temp
On Error GoTo 0

ReDim mgNames(1 To myCollection.Count)
For temp = 1 To myCollection.Count
    mgNames(temp) = myCollection(temp)
Next temp

For temp = LBound(mgNames) To UBound(mgNames)
    boxNames.AddItem mgNames(temp)
Next temp

End Sub
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

CodeNinja

Well-known Member
Joined
Feb 18, 2013
Messages
634
Create a variable for the range and fill it by unioning (is that a word?) the ranges based on condition.

Code:
Dim rng As Range
Dim c As Range
Set rng = Nothing

'On Error Resume Next

For Each c In Range(.Cells(2, 4), .Cells(.Rows.Count, 4))
    
    If Trim(c.Offset(0, 7).Value) = "Hourly" Then
        If rng Is Nothing Then
            Set rng = c
        Else
            Set rng = Application.Union(rng, c)
        End If
    End If
Next c
mgNames = rng
 

austin350s10

Active Member
Joined
Jul 30, 2010
Messages
321
That seems to work for the most part. Only thing is the last line mgNames = rng. For some reason mgNames is only being filled with the first non-unioned range even though rng contains all of the correct ranges. I know thats kinda confusing so heres and example:

Say cells A1 & A2 meet the condition and are set to rng. Cells A3 & A4 do not meet the condition and are NOT set to rng. lastly, Cells A5 & A6 also meet the condition and are set to rng via a union.

So rng now equals $A$1:$A$2,$A$5:$A$6

When the code reaches mgNames = rng it only gets filled with the ranges A1 through A2 and skips the rest

So mgNames(1) equals the value from A1 and mgNames(2) equals the value from A2. However, It should look like;
mgNames(1) = A1, mgNames(2) = A2, mgNames(3) = A5, mgNames(4) = A6

I know this is confusing but if you have any insight I would appreciate it!
 

CodeNinja

Well-known Member
Joined
Feb 18, 2013
Messages
634
Odd, I thought that would work... Perhaps you could just redim preserve and add the result right into the variant... or some variation of the following code that suits your needs...


Code:
Dim rng As Range
Dim c As Range
Set rng = Nothing
Dim mgNames As Variant
'On Error Resume Next
redim mgnames(1 to 1)

For Each c In Range(.Cells(2, 4), .Cells(.Rows.Count, 4))
    
    If Trim(c.Offset(0, 7).Value) = "Hourly" Then
        If rng Is Nothing Then
            Set rng = c
            mgNames(1) = c.Value
        Else
            ReDim preserve mgNames(1 To UBound(mgNames) + 1)
            mgNames(UBound(mgNames)) = c.Value
            
        End If
    End If
Next c
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,054
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I have a gut feeling there may be an easier way to do what you want to do but I am having trouble figuring out what that is. Forget the code you posted and forget the method you think you have to use to do what you want... just tell us what you have and what you ultimately want from it (for example, what is boxNames that you seem to be "filling"?). To do this, show us a sample of say 10 rows of data (between Columns B and D if I read your code correctly) and then show us what you want from that sample and how whatever it is you want from it ends up looking like.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,531
Messages
5,659,360
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top