Help with Excel VB

  PTB007 11:50 17 Aug 04
Locked

Dear All,

I'm just starting out writing some code in Visual Basic so please excuse me if this is quite simple.
I want to write a code in VB that will allow me to select any cell within a worksheet and then copy a range (three cells) immediately to the left and paste special (transpose)the three cells onto the selected cell.

Any help appreciated.

  VoG II 13:01 17 Aug 04

The following will do what you want though not using Copy and PasteSpecial. This code could be tidied up by using a loop and eliminating the variables val1 etc.

===================

Sub test()

Dim val1, val2, val3

val1 = ActiveCell.Offset(0, -3).Value

val2 = ActiveCell.Offset(0, -2).Value

val3 = ActiveCell.Offset(0, -1).Value

ActiveCell.Offset(1, 0).Value = val1

ActiveCell.Offset(2, 0).Value = val2

ActiveCell.Offset(3, 0).Value = val3

End Sub

  Simsy 14:34 17 Aug 04

that although what VoG™ has suggested will work, (of course!!), it is ONLY the value that will be copied...

If you wanted to copy the format as well, (e.g. you might be copying dates or currencies), the formats wouldn't copy with this method.

A good way of learning VB code is to record a macro doing what you want, then examine the code generated in the VB editor.

That's how I did most of my VB learning. (And using VoG™ as well!!)

Good luck,

Regards,

Simsy

  VoG II 15:13 17 Aug 04

Point well taken. I agree that recording macros is a good way to learn. However, in examples like this, the macro recorder will produce absolute cell references rather than relative to the current cell as here.

Another way.

Sub test3()

Range(ActiveCell.Offset(0, -3), ActiveCell.Offset(0, -1)).Copy

ActiveCell.Offset(1, 0).PasteSpecial Transpose:=True

Application.CutCopyMode = False

End Sub

  PTB007 20:56 17 Aug 04

VOG/Simsy,

Many thanks for the assistance. I got it working in the end by using a combination of both methods.
I try to learn VB by recoring and then viewing macros, I was able to do this but the problem I had was that the cells to be copied would always be hard coded into the macro, the paste worked fine.
I rather crudely wrote it copying one cell at a time using the ActiveCell.Offset method, but VOG's last method was what I was trying to achieve.
Thanks once again.

  PTB007 20:56 17 Aug 04

VOG/Simsy,

Many thanks for the assistance. I got it working in the end by using a combination of both methods.
I try to learn VB by recoring and then viewing macros, I was able to do this but the problem I had was that the cells to be copied would always be hard coded into the macro, the paste worked fine.
I rather crudely wrote it copying one cell at a time using the ActiveCell.Offset method, but VOG's last method was what I was trying to achieve.
Thanks once again.

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…