A problem with Set (Range)
 

I want to read in 2 text-files which should be placed together side by side
from say C1 to F1740 so the first text file uses columns C and D and the
second one uses E and F. I can then easily use 100 text files if I just get
this sample right. The problem I have is when I want to use the Range(Cells
(1, 3), Cells(1470,4) in the set line of the program it stops, while when I
use C1:D1470 it works. I would like to use the Range(Cells) code so I can
wander though the Excel sheet 100 times just by updating the column variable.
I have had this problem before that when it comes to the paste line of the
program Excel does not like it. Also when I have placed a clip on the
clipboard Excel asks me if I want to use the clip for another program and I
click no. How can I disable this function so I do not have to click no 100
times?

This is my code

Private Sub CommandButton2_Click()
Dim TextTest As Variant
For Each c In Range("A1:A2")
Dim Path As String
Dim filename As String
Dim fileextension As String
Path = "C:\Peter\Test\"
fileextention = ".txt"
filename = c.Value
TextTest = Path & filename & fileextention
Workbooks.OpenText filename:=TextTest, Origin:=xlWindows,
DataType:=xlDelimited, Tab:=True
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet

Dim rngformula3 As Range
Dim rngpaste3 As Range
Dim kolumn As Long

kolumn = 3

Set wb1 = Workbooks("textinläsning.xls")
Set wb2 = ActiveWorkbook



Set rngformula3 = wb2.Worksheets(1).Range("A1:B1470")
Set rngpaste3 = wb1.Sheets("Data").Range(Cells(1, kolumn), Cells(1470,
kolumn + 1))

rngformula3.Copy
rngpaste3.PasteSpecial (xlPasteValues)

Set ws1 = Nothing
Set wb1 = Nothing
Set wb2 = Nothing

Set rngformula3 = Nothing
ActiveWorkbook.Close savechanges:=False
kolumn = kolumn + 2
Next
End Sub

Thanks guys!

/Peter

A problem with Set (Range)
 

Celss is not explicitly referenced so it will refer to the active sheet
(whatever that might be. Try something more like...

with wb1.Sheets("Data")
Set rngpaste3 = .Range(.Cells(1, kolumn), .Cells(1470, kolumn + 1))
end with
--
HTH...

Jim Thomlinson


"Peter" wrote:

I want to read in 2 text-files which should be placed together side by side
from say C1 to F1740 so the first text file uses columns C and D and the
second one uses E and F. I can then easily use 100 text files if I just get
this sample right. The problem I have is when I want to use the Range(Cells
(1, 3), Cells(1470,4) in the set line of the program it stops, while when I
use C1:D1470 it works. I would like to use the Range(Cells) code so I can
wander though the Excel sheet 100 times just by updating the column variable.
I have had this problem before that when it comes to the paste line of the
program Excel does not like it. Also when I have placed a clip on the
clipboard Excel asks me if I want to use the clip for another program and I
click no. How can I disable this function so I do not have to click no 100
times?

This is my code

Private Sub CommandButton2_Click()
Dim TextTest As Variant
For Each c In Range("A1:A2")
Dim Path As String
Dim filename As String
Dim fileextension As String
Path = "C:\Peter\Test\"
fileextention = ".txt"
filename = c.Value
TextTest = Path & filename & fileextention
Workbooks.OpenText filename:=TextTest, Origin:=xlWindows,
DataType:=xlDelimited, Tab:=True
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet

Dim rngformula3 As Range
Dim rngpaste3 As Range
Dim kolumn As Long

kolumn = 3

Set wb1 = Workbooks("textinläsning.xls")
Set wb2 = ActiveWorkbook



Set rngformula3 = wb2.Worksheets(1).Range("A1:B1470")
Set rngpaste3 = wb1.Sheets("Data").Range(Cells(1, kolumn), Cells(1470,
kolumn + 1))

rngformula3.Copy
rngpaste3.PasteSpecial (xlPasteValues)

Set ws1 = Nothing
Set wb1 = Nothing
Set wb2 = Nothing

Set rngformula3 = Nothing
ActiveWorkbook.Close savechanges:=False
kolumn = kolumn + 2
Next
End Sub

Thanks guys!

/Peter