Problem with Union function when opening multiple datasets

[menos]

I have a lengthy macro that imports text files and runs some calculations on them. It works just fine when used to import 1 file, but breaks on the second file in the following section, with the error message “Runtime error 1004 Method Union of object ‘_global’ failed”. Any idea why and how I might fix it? I am using Excel 2003. Many thanks

Dim X As Long
Dim LastRow As Long
Dim Source As Worksheet
Dim Destination As Worksheet
Dim RowsWithNumbers As Range
Set Source = Worksheets("ValidHFs")
Set Destination = Worksheets("summary")
With Source
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For X = 2 To LastRow
If .Cells(X, "D").Value > 0 And .Cells(X, "D").Value <> "" Then
If RowsWithNumbers Is Nothing Then
Set RowsWithNumbers = .Cells(X, "D")
Else
'breaks here:
Set RowsWithNumbers = Union(RowsWithNumbers, .Cells(X, "D"))
End If
End If
Next
If Not RowsWithNumbers Is Nothing Then
RowsWithNumbers.EntireRow.Copy Destination.Range("A17")
End If
End With

[p45cal]

When it breaks down, what's the value of X?
I'm just wondering whether it's reaching an Excel limit of some kind?

[Bob Phillips]

What is the address of RowsWithNumbers when it happens?

[menos]

I have tried it on a few files and X varies; it broke at rows 4158, 490 and 658 for the last 3 tries. values of the cells at the break point vary as well.

[p45cal]

Are you setting RowsWithNumbers to Nothing between files?

[menos]

No, how would I do that?

[Bob Phillips]

Are you setting RowsWithNumbers to Nothing between files?

It will be Nothing implicitly, it is a procedure variable, and he is not looping all files in the one procedure.

[p45cal]

Set RowsWithNumbers = Nothing
possibly just before the
With Source
line.

[p45cal]

and he is not looping all files in the one procedure.Can we be sure, as the OP says that this snippet is only a section of the code? (There's no Sub, End Sub either.)
menos, perhaps we should see the whole Sub?

[menos]

that didn't work I'm afraid. I am not looping all files at once in the same procedure, should be one at a time. I have pasted the start and end below

' Select Start Drive & Path
ChDrive ("R")
ChDir ("R:\MENOS 1&2\Hot Flush Monitor\developing coding\analysis\")
With Application
' Set File Name Array to selected Files (allow multiple)
Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
' Reset Start Drive/Path
ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Not IsArray(Filename) Then
msgbox "No file was selected."
Exit Sub
End If
' Open Files
For i = LBound(Filename) To UBound(Filename)
msg = msg & Filename(i) & vbCrLf ' This can be removed

Workbooks.OpenText Filename(i), Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1)), TrailingMinusNumbers:=True

[many, many other statements including RowsWithNumbers procedure]



Next i
msgbox msg, vbInformation, "Files Opened" ' This can be removed

End Sub

[p45cal]

Try setting RowsWithNumbers to nothing directly after
For i = LBound(Filename) To UBound(Filename)

[Tinbendr]

Stab in the dark, But Xld did allude to this earlier.

Set RowsWithNumbers = Union(RowsWithNumbers, .Range(.Cells(X, "D").Address))

David

[menos]

I tried both suggestions but neither worked. Instead i have taken out that procedure and linked it to a command button so it can be run with the other manual checks. A cop out I know, but does the job. Thanks all for the suggestions though - it really helps me get my head round vba (and I need all the help I can get!)