Error trying to populate Userform Listbox

[sharky12345]

I'm using this code to populate a Userform Listbox;

 If ComboBox1.Value = "Not Due Staff" Then
   Sheet2.Activate
   CmdEdit.Enabled = False
   CmdView.Enabled = False
   CmdExpired.Enabled = False
   Label15.Visible = True
   Label16.Visible = True
   Label16.Caption = "Last 121"
   Label17.Visible = True
With Me.ListBox1
.ColumnCount = 14
.ColumnWidths = "120,0,0,0,0,0,0,0,0,0,65,0,0,60"
End With
LastRow = Range("A65536").End(xlUp).Row
Set rng = Range("A2:N" & LastRow)
ReDim Ray(1 To 14, 1 To rng.Count)
For Each Dn In rng
If Dn.Value = "Not Due" Then
c = c + 1
For Ac = 1 To 14
Ray(Ac, c) = Dn.Offset(, -(14 - Ac)).Text
Next Ac
End If
Next Dn
Me.ListBox1.Column = Ray
'Sheet4.Activate
End If

I'm getting an error which I don't understand - I get an error 1004, Application Defined or or object defined error with the following line highlighted;

Ray(Ac, c) = Dn.Offset(, -(14 - Ac)).Text

The bizarre thing is if I change 'Not Due' to 'Not Duez' it works fine.

Can someone help me understand what the issue is?

 

[RoryA]

Most likely you are trying to offset too many columns left from whichever cell you are looking at. For example, if you had "Not due" in column A, your code would fail as soon as you tried to look at a cell to the left with your Offset command.

 

[sharky12345]

Thanks RoryA - the value is in column N and I have numerous versions of the listbox being populated with different text, it's just this one that doesn't work - this is odd because the other examples are identical except for the actual text - i.e. one of the others is 'Due' and that works fine.

 

[RoryA]

If the values are in column N, why are you looping through all the other columns too? The most likely cause is still that that data also appears somewhere else. To test that, change the range to just column N:

Set rng = Range("N2:N" & LastRow)

If that fixes the problem, then you have your answer.

 

[sharky12345]

Ahh, I see what you mean - thanks, I'll try your suggestion and report back

 

[sharky12345]

Just tried your suggestion and it's solved it - thanks very much, this stuff is easy when you know what you're looking for

 

[RoryA]

this stuff is easy when you know what you're looking for

Too true.