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]

That's 3 columns not 7.

 

[M0n5ta09]

That's 3 columns not 7.

I left the remaining 4 columns out (which are text) as the main columns are Col_A which I need to list every occurrence for and Col_C which needs to be sorted by date, new to old.

 

[Andrew Poulsom]

What's the address of "NamedTable". You are trying to offset one of its cells to the left by more columns than is possible.

 

[M0n5ta09]

"NameTable" is a dynamic named range in the Worksheet "Data" and occupies the Range A1:G30

<font face=Courier New>Set ws = Worksheets("Data")<br>Set TargetRange = ws.Range("NamedTable")<br></FONT>

 

[Andrew Poulsom]

So in your loop, when Rng refers to A1 you can't Offset to the left.

 

[M0n5ta09]

I've changed my code to this;

<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> lb <SPAN style="color:#00007F">As</SPAN> msforms.ListBox<br><SPAN style="color:#00007F">Dim</SPAN> myArray() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> lrw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lcol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> ws = Worksheets("Data")<br><SPAN style="color:#00007F">Set</SPAN> TargetRange = ws.Range("NamedRange")<br><br><SPAN style="color:#00007F">ReDim</SPAN> myArray(1 <SPAN style="color:#00007F">To</SPAN> TargetRange.Columns.Count, 1 <SPAN style="color:#00007F">To</SPAN> TargetRange.Rows.Count)<br> <br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> TargetRange<br>    <SPAN style="color:#00007F">If</SPAN> cell.Value = TargetCell <SPAN style="color:#00007F">Then</SPAN><br>        lrw = lrw + 1<br>        <SPAN style="color:#00007F">For</SPAN> lcol = 1 <SPAN style="color:#00007F">To</SPAN> TargetRange.Columns.Count<br>            myArray(lrw, lcol) = TargetRange.Cells(lrw, lcol)<br>        <SPAN style="color:#00007F">Next</SPAN> lcol<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> cell<br>    <br><SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> myArray(1 <SPAN style="color:#00007F">To</SPAN> TargetRange.Columns.Count, 1 <SPAN style="color:#00007F">To</SPAN> TargetRange.Rows.Count)<br><br><SPAN style="color:#00007F">Set</SPAN> lb = Me.ListBox1<br><br><SPAN style="color:#00007F">With</SPAN> lb<br>    .ColumnCount = 7<br>    .ColumnWidths = "80;80;80;80;80;80;80"<br>    .Column = myArray<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN></FONT>

and I am getting "Run-time error '70': Permission denied"

What am I doing wrong?

 

[Andrew Poulsom]

Does your ListBox have its RowSource property set?

 

[M0n5ta09]

Thank you Andrew, I did have a RowSource set which I have removed.

I've also changed the .Column = myArray to .List = myArray so the orientation is correct but the results are not what I should be expecting. I'm either getting additional non related results or no results whatsoever.

 

[Andrew Poulsom]

What's TargetCell?

 

[M0n5ta09]

TargetCell is a string passed to the Userform and has the value S/N 001, S/N 002 etc.

So only the rows in NamedRange that match the TargetCell should appear in the ListBox.