VBA Populating Userform ListBox

[M0n5ta09]

Hi,

I found a post with something similar and modified it to suit my need but I am getting an '1004' error, any suggestions?

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> TargetRange <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> TargetCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ActRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Ac <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> ws = Worksheets("Data")<br><SPAN style="color:#00007F">Set</SPAN> TargetRange = ws.Range("NamedTable")<br><br>ActRow = ActiveCell.Row<br><br>TargetCell = ActiveSheet.Range("B" & ActRow).Value<br>UserForm1.LblSerial.Caption = TargetCell<br>        <br>        <SPAN style="color:#00007F">With</SPAN> UserForm1.ListBox1<br>            .ColumnCount = 7<br>            .ColumnWidths = "80,80,80,80,80,80,80"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br>        <SPAN style="color:#00007F">ReDim</SPAN> Ray(1 <SPAN style="color:#00007F">To</SPAN> TargetRange.Count, 1 <SPAN style="color:#00007F">To</SPAN> 7)<br><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Rng <SPAN style="color:#00007F">In</SPAN> TargetRange<br>            <SPAN style="color:#00007F">If</SPAN> Rng.Value = TargetCell <SPAN style="color:#00007F">Then</SPAN><br>                c = c + 1<br>                <SPAN style="color:#00007F">For</SPAN> Ac = 1 <SPAN style="color:#00007F">To</SPAN> 7<br>                    Ray(c, Ac) = Rng.Offset(, -(7 - Ac))<br>                <SPAN style="color:#00007F">Next</SPAN> Ac<br>             <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> Rng<br>        <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> Ray(1 <SPAN style="color:#00007F">To</SPAN> 7, 1 <SPAN style="color:#00007F">To</SPAN> c)<br>        UserForm1.ListBox1.List = Application.Transpose(Ray)<br><br>UserForm1.Show</FONT>

 

[Andrew Poulsom]

Which line causes the error and what is the text of the error?

 

[M0n5ta09]

Which line causes the error and what is the text of the error?

The line: "Ray(c, Ac) = Rng.Offset(, -(7 - Ac))"

 

[Andrew Poulsom]

What's the Address of Rng? Maybe the negative Offset is too large.

 

[M0n5ta09]

What's the Address of Rng? Maybe the negative Offset is too large.

The value of Rng is S/N 001, 002 etc. which is taken from the selected cell.

What I am trying to achieve is to populate a multicolumn listbox from a named range with only those matching Rng and then sort a date field from newest to oldest.

Does this make sense?

 

[mikerickson]

Inside the loop C is used for the first index of Ray.
When you try to redim Ray, the C is effecting the second index.

I think the line you want is

' won't work
ReDim Preserve Ray(1 To C, 1 To 7)

BUT that won't work, as Redim Preserve will only change the last index in a two dimensional array.

Try swapping the indices and using the .Column property of the List box

ReDim Ray(1 To 7, 1 To TargetRange.Count)

For Each Rng In TargetRange
    If Rng.Value = TargetCell Then
        c = c + 1
        For Ac = 1 To 7
            Ray(Ac, c) = Rng.Offset(, -(7 - Ac))
        Next Ac
    End If
Next Rng

ReDim Preserve Ray(1 To 7, 1 To c)

UserForm1.ListBox1.Column = Ray

 

[M0n5ta09]

Many thanks Mike.

The line "Ray(Ac, c) = Rng.Offset(, -(7 - Ac))"

Is still throwing up a '1004' runtime error, "Application-defined or object-defined error"

 

[mikerickson]

What range are you trying to put in the user form?
The way your code is set up, you are using negative column offsets from a cell in column B, that's what causing the error.

Which rows do you want to be put in the ListBox?

 

[M0n5ta09]

Col_A Col_B Col_C
S/N 001 TRUE 01/05/2015
S/N 005 TRUE 01/05/2015
S/N 001 TRUE 05/05/2015
S/N 002 FALSE 01/03/2015
S/N 005 TRUE 19/07/2015
S/N 003 TRUE 09/05/2015
S/N 002 FALSE 06/08/2015
S/N 004 TRUE 02/02/2015
S/N 005 TRUE 29/09/2015
S/N 003 TRUE 01/01/2016

Here's a sample of what I have in a named range. What I am trying to do is list say all "S/N 003" and display them in date order, newest at the top.

 

[M0n5ta09]

Col_A Col_B Col_C
S/N 001 TRUE 01/05/2015
S/N 005 TRUE 01/05/2015
S/N 001 TRUE 05/05/2015
S/N 002 FALSE 01/03/2015
S/N 005 TRUE 19/07/2015
S/N 003 TRUE 09/05/2015
S/N 002 FALSE 06/08/2015
S/N 004 TRUE 02/02/2015
S/N 005 TRUE 29/09/2015
S/N 003 TRUE 01/01/2016

Here's a sample of what I have in a named range. What I am trying to do is list say all "S/N 003" and display them in date order, newest at the top.

So, the listbox will show;

S/N 003 TRUE 01/01/2016
S/N 003 TRUE 09/05/2015