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]

Why are you using lrw in the Cells property here?

myArray(lrw, lcol) = TargetRange.Cells(lrw, lcol)

Shouldn't it be the row of cell?

myArray(lrw, lcol) = cell.Offset(0, lcol - 1).Value

 

[M0n5ta09]

Andrew you are a gent, Thank you.

How would I sort the dates in order of newest to old and its formatting back to dd/mm/yyyy?

 

[Andrew Poulsom]

Can't you sort the data before populating the ListBox? To have the ListBox entries in the same format as the source data try:

myArray(lrw, lcol) = cell.Offset(0, lcol - 1).Text

 

[M0n5ta09]

Thank you again Andrew.

I'm thinking of having either a temporary sheet to do the sorting or using a bubblesort. do you have any preference in this?

 

[Andrew Poulsom]

I would use a temporary worksheet.

 

[M0n5ta09]

Thank you.