ndendrinos
02-08-2011, 09:09 AM
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
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