Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Offset calculation in VBA (2000)

    I am trying to create a routine in VBA which will copy "D555" 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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset calculation in VBA (2000)

    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

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset calculation in VBA (2000)

    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

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset calculation in VBA (2000)

    Just a word of caution:
    In the original post the user wanted to copy Range("D555") 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"))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •