Solved: Why does code need On Error Resume Next?

[ndendrinos]

Two identical sheets with corresponding UserForms and NamedRanges.
When selecting B8 in Sheet “Invoice” the code works, If I select another choice from
Userform2 the code works.

In sheet “REFUNDS” when I try the same routine I get an error.

Run Time Error “9” / Subscript out of range
and

Range(a(i)).Value=r.value
is highlighted.

If I add at the top of the UserForm8 code:

On Error Resume the all is OK

My question is why? I look at the code in UserForm8 and do not understand the why of it.

Many thanks

Here are the codes:


Private Sub ListBox1_Click()

Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Invoice").Range("B8") = UserForm2.ListBox1.Value


Dim FR As String
FR = Range("B8")
Sheets("Customers").Activate

Dim c As Range
lookfor = FR

With Sheets("Customers")
'this sets the column where to match from (hereA)
Set c = .Columns(1).Find(What:=lookfor, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then
c.EntireRow.Copy


Sheets("Invoice").Activate
ActiveSheet.Range("A1").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

'this will copy to the non contiguous cells

Dim r As Range, topRange As Range, i As Integer, a() As Variant
ReDim a(1 To Range("billto").Count) As Variant
i = 0
For Each r In Range("billto")
i = i + 1
a(i) = r.Address
Next r
Set topRange = Range("B1", Cells(1, Columns.Count).End(xlToLeft))
i = 0
For Each r In topRange
i = i + 1
Range(a(i)).Value = r.Value
Next r


Rows("1:1").ClearContents


End If
End With




Application.ScreenUpdating = True
Application.EnableEvents = True



Unload UserForm2


Range("A17").Select

End Sub
And



Private Sub ListBox1_Click()

'On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("REFUNDS").Range("B8") = UserForm8.ListBox1.Value


Dim FR As String
FR = Range("B8")
Sheets("Customers").Activate

Dim c As Range
lookfor = FR

With Sheets("Customers")
'this sets the column where to match from (hereA)
Set c = .Columns(1).Find(What:=lookfor, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then
c.EntireRow.Copy


Sheets("REFUNDS").Activate
ActiveSheet.Range("A1").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

'this will copy to the non contiguous cells


Dim r As Range, topRange As Range, i As Integer, a() As Variant
ReDim a(1 To Range("billto").Count) As Variant
i = 0
For Each r In Range("billto")
i = i + 1
a(i) = r.Address
Next r
Set topRange = Range("B1", Cells(1, Columns.Count).End(xlToLeft))
i = 0
For Each r In topRange
i = i + 1
Range(a(i)).Value = r.Value

Next r


Rows("1:1").ClearContents


End If
End With




Application.ScreenUpdating = True
Application.EnableEvents = True



Unload UserForm8


Range("A17").Select

End Sub
BTW each I cannot include the two
Private Sub ListBox1_Click()
in the longer codes .... is this a glitch with the board?
Thank you

[Kenneth Hobs]

Should you not be using the named range refundto rather than billto in your Userform8 code?

It is best not to use Resume Next unless you know for sure what error would happen.

[ndendrinos]

Should you not be using the named range refundto rather than billto in your Userform8 code?
Hello Kenneth Hobs. I am referring to the named range "refundto" in my original WB (did a mistake on my post when copying the code)
So this is not the problem but what could it be?

[ndendrinos]

revised attachment

[tpoynton]

didnt look at the wb, but I'd qualify Range(a(i)).Value with a worksheet. May or may not help, but I've run into similar problems by not qualifying the range

[ndendrinos]

now cross posted here: http://www.mrexcel.com/forum/showthread.php?p=2605321#post2605321

[Kenneth Hobs]

As noted at MrExcel, you assumed that the number of cells in the named range is the same as in the set range. Range B1:L1 is 11 cells while the named range is 10. This is why you get an error because you tried to iterate 11 times in the loop but ReDim said the array was 1 to 10 essentially.

[ndendrinos]

thank you tpoynton,
Yes Kenneth that was the problem forgot to include [E36] (discount) from the named range "refundto"
Have a good day