Results 1 to 5 of 5

Help with a offset macro script

This is a discussion on Help with a offset macro script within the Excel Questions forums, part of the Question Forums category; I want a offset macro that will go back -11 to -18 colums down 6000 rows and will copy/paste/format/value back ...

  1. #1
    Board Regular
    Join Date
    May 2004
    Posts
    268

    Default Help with a offset macro script

    I want a offset macro that will go back -11 to -18 colums down 6000 rows and will copy/paste/format/value back into there original cells but losing all there formulas , so really just wanting to get rid of the formulas, the first line does work but not the rest

    Cells(1, Cells(1, 256).End(xlToLeft).Column + 1).Select
    With ActiveCell.Offset(-11, 0).Resize(6000, 7)
    .Value = .Value
    End With
    End Sub

    Thanks for your help ..Bob

  2. #2
    Board Regular
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    3,459

    Default Re: Help with a offset macro script

    Welcome Bob,

    How about this:

    Code:
    Sub macro()
    Cells(1, Cells(1, 256).End(xlToLeft).Column).Select
    Application.ScreenUpdating = False
    Application.StatusBar = "Creating values. Please wait."
    counter = 0
    Do While counter < 6000
    ActiveCell.Offset(counter, -11).Formula = ActiveCell.Offset(counter, -11).Value
    ActiveCell.Offset(counter, -12).Formula = ActiveCell.Offset(counter, -12).Value
    ActiveCell.Offset(counter, -13).Formula = ActiveCell.Offset(counter, -13).Value
    ActiveCell.Offset(counter, -14).Formula = ActiveCell.Offset(counter, -14).Value
    ActiveCell.Offset(counter, -15).Formula = ActiveCell.Offset(counter, -15).Value
    ActiveCell.Offset(counter, -16).Formula = ActiveCell.Offset(counter, -16).Value
    ActiveCell.Offset(counter, -17).Formula = ActiveCell.Offset(counter, -17).Value
    ActiveCell.Offset(counter, -18).Formula = ActiveCell.Offset(counter, -18).Value
    counter = counter + 1
    Loop
    Application.ScreenUpdating = True
    Application.StatusBar = False
    End Sub
    There are three kinds of people - those that can count and those that can't.

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Help with a offset macro script

    Hi Bob:

    Welcome to MrExcel Board!

    Please try changing the following line in your code
    Code:
    With ActiveCell.Offset(-11, 0).Resize(6000, 7)
    to
    Code:
    With ActiveCell.Offset(0,-11).Resize(6000, 7)
    Does it help?
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    Board Regular
    Join Date
    May 2004
    Posts
    268

    Default Re: Help with a offset macro script

    With ActiveCell.Offset(0, -18).Resize(6000, 8)

    Changed it slightly to get it right, Brilliant..Thanks..Bob

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Help with a offset macro script

    Hi Bob:

    Good job in correctly sorting it out to work for you -- now let us keep EXCELing!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com