Excel VBA: insertion issues

Question

I tried various ways to make this paste, but none of them work. I am extremely new to programming, so I need help understanding why I keep getting either error 1004 or 5. I don’t even understand what these errors mean.

Cells(hotcell).Copy Cells.Offset(0, 1).PasteSpecial 

or ... Paste, ... PasteSpecial = xlpasteall, ... pastespecial Paste: = xlpasteall, Range (Cells ("B" and i)). paste, Range ("B" and i) .paste, etc., as described above.

I have a complete loss. Everything else in the program works fine. I just can’t get him to paste my copied values ​​into the desired cells (they are all shifted by a certain number of columns, but on the same row). Help and explanation were appreciated.

Edit Thanks for the answers I received, I was able to solve my problem. I really could not find a good answer anywhere I looked. Thanks!

The solution I used was one of the simplest:

 rng.Offset(0, 1) = rng.Text 

Thanks again to the posters that answered, and to those who commented on this. I did it too hard.

Answer 1

There are many ways to solve this problem, so I will try to list some of them that I use.

No paste approach

 Sub CP1() 'This basically just transfers the value without fuss. Dim Rng As Range Set Rng = Range("A1") Rng.Offset(0,1) = Rng.Value End Sub 

Simple paste method

 Sub CP2() 'This copies a cell exactly as it is. Dim Rng As Range Set Rng = Range("A1") Rng.Copy Rng.Offset(0,1) 'Read: Copy Rng to Rng.Offset(0,1). Application.CutCopyMode = False End Sub 

Special paste approach

 Sub CP3() 'This copies the format only. Dim sRng As Range, tRng As Range Set sRng = Range("A1") Set tRng = sRng.Offset(0, 1) sRng.Copy tRng.PasteSpecial xlPasteFormats Application.CutCopyMode = False End Sub 

Try to determine from the three above that you want and modify accordingly.;)

Hope this helps.

Answer 2

Explanation:

 Cells.Offset(0,1).PasteSpecial 

This will give Error 1004 , since Cells refers to the entire range of sheets, and you cannot compensate for it.

 Cells(hotcell).Copy 

This will give you Error 5 if the hotcell value hotcell not numeric.
I think Cells only accepts a numeric argument if you used the syntax above.

How to use cells: (Excel 2007 and later)

1. Define the syntax of R, C: cells (RowNumber, ColumnNumber)

 Cells(1,1) 'refers to Range("A1") Cells(1,2) 'refers to Range("B1") Cells(2,1) 'refers to Range("A2") 

2. Use only number

 Cells(1) 'refers to Range("A1") Cells(2) 'refers to RAnge("B1") and so on Cells(16385) 'refers to Range("A2") 

3. Using cells only

 Cells.Copy 'copies the whole range in a sheet Cells.Resize(1,1).Copy 'copies Range("A1") Cells.Resize(1,1).Offset(0,1).Copy 'copies Range("B1") Cells.Resize(2,1).Copy 'copies Range("A1:A2") 

4. The use of numbers and letters (this only works in the syntax Cells (RowNum, ColNum))

 Cells(1, "A").Copy 'obviously copies A1 Cells(1, "A").Resize(2).Copy 'copies A1:A2 

Now how to copy and paste.
Suppose you want to copy A1: A5 and paste it into the next column, which is the letter B.

 Cells.Resize(5,1).Copy Cells.Resize(5,1).Offset(0,1) 

The above will work because you Resize Cells first before doing Offset .
Values ​​A1: A5 will now be copied to B1: B5.
Hope this helps you.