Offset calculation in VBA (2000)

Topic

New Reply

I am trying to create a routine in VBA which will copy “D5:D55” to another location, calculated calculated via an OFFSET with the value of the offset taken from cell Q1.

Range(“D5:K55”).Select
Selection.Copy
ActiveCell.Offset(17 + (q1.Value * 10), 0).Range(“A1”).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I know it is probably something basic, but how can I use the value of cell Q1 in the calculation above.
For values of Q1 it shuld give the following results:
Q1 Result
1 27
2 37
3 47
4 57
5 67

Any help appreciated

Reply | Quote

Replies

To refer to cell Q1 in VBA, you must use Range(“Q1”), just as you are already using Range(“D55:K55”) to refer to D55:K55.
In general, it is not necessary to select cells to copy and paste them. Code works more efficiently if you don’t select cells.

Range(“D55:K55”).Copy
Range(“D55”).Offset(17 + 10 * Range(“Q1”), 0).PasteSpecial Paste:=xlValues

Reply | Quote

Thanks Hans
Doesn’t seem to matter if it’s Access or Excel, you are always there to help.
Thanks for the tip about not selecting – I’d created macro’s and then was converting – hence the extra code.
John

Reply | Quote

Just a word of caution:
In the original post the user wanted to copy Range(“D5:D55”) then offset from D5 (the activecell). If Q1 contains either 1 or 2 then

Activecell.Offset(17 + 10 * Range(“Q1”), 0).PasteSpecial Paste:=xlValues

will overwrite part of the original data– further down column D. Not sure if the poster wanted to offset down col D or over to the right a certain number of columns, in which case the offset arguments would need to be reversed:
Offset(0,17 + 10 * Range(“Q1”))

Reply | Quote