Paste function error in my macro????? 2

[julius1]

I have 2 columns, A is the raw data and B is the additional character I need to merge it with into column C.
A- E.123456
B- .C
C- E.123456.C

Problem is when the macro gets to this point I get a Run time error 1004
Paste method of Worksheet class class failed

this is the code area that comes up for the debugging:

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\jwg052057\Desktop\Book5.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(4, _
1), Array(12, 9))
Range("B2&quot.Select
ActiveCell.FormulaR1C1 = ".C"
Range("C2&quot.Select
ActiveSheet.Paste (This is the line that comes up in yellow)
Range("B2:C2&quot.Select
Selection.AutoFill Destination:=Range("B2:C3528&quot, Type:=xlFillDefault
Range("B2:C3528&quot.Select
Columns("C:C&quot.Select
Selection.Copy

This beast is almost done, with the exception of this. Any one have any ideas? Thanks!

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!

 

[dyarwood]

Try ActiveCell.Paste

I am assuming you are getting that error and it says something like the areas are not of equal size.

dyarwood

 

[julius1]

No, all it states is the paste error. The formula in column C is suppose to be A2&B2 to merge them, but it never does happen.

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!

 

[dyarwood]

How about the following

a = Cells(i,1).Value
b = Cells(i,2).Value
c = a & b

Or a formula like

Range("C2&quot.FormulaR1C1 = "=A2 & B2"
Something like that will mean that you don't need to copy and paste.

dyarwood

 

[MakeItSo]

Have you tried the Concatenate function?
-->Range("C2&quot.FormulaR1C1="=Concatenate(A2, B2)
;-)

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[julius1]

Ok, I am really confused. The macro works now and pastes it to the main spreadsheet. Thanks! for that.
I am wondering can I run a formula in the main sheet?
Can I paste it or do I have to type it in for the cell?
Here is my vlookup
=VLOOKUP(A2,NOV26DATES03.xls!$A$2:$B$8663,2,FALSE)
I have paste that into the cell while recording my macro.
Now when I get to the point of it having to do the vlookup and obtain the value, it gives me the paste error again.

The last one from above, I entered the formula manually and it seemed to work, but i am not sure if this vlookup will be recorded correctly.

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!

 

[dyarwood]

Do something like the following

Range("A2&quot.FormulaR1C1 = "=VLOOKUP(A2,NOV26DATES03.xls!$A$2:$B$8663,2,FALSE)"

that will put that formula into cell A2 from the code.

dyarwood

 

[julius1]

Ok, I re ran the process, and it does record the formula. All worked out great Thanks guys!!

julius.gazdag@centurytel.com
Im a Telco guy, I can get you where you want to go, but I can't guarantee it will work when you get there!