Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro relative vs absolute reference (2000)

    I recorded a visual basic macro that used absolute cell reference
    #1. How can I change in the "record macro dialog box" to relative cell references? My pop up does not give me the option to change between absolute and relative references.

    #2. What I recorded was: (I was "in" cell C14 at the time
    Range("A14:C14").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    (and then a bunch of other stuff to do next)

    what I want is: (from whatever cell I may be in at the time)
    Range(Cells(-3, 0), Cells(0,0)).Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    when I made the above change, the macro bombed. Help was useless.
    Thanks,
    Rob

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: macro relative vs absolute reference (2000)

    What exactly are you trying to do?

    Sub Shift3Down()
    Range(ActiveCell, ActiveCell.Offset(-3, 0)).Insert Shift:=xlDown
    End Sub

    or

    Sub Fill3Down()
    Range(ActiveCell, ActiveCell.Offset(3, 0)).FillDown
    End Sub

    or something else?

    To record macros in relative mode, when the Record Macro button shows, click the right side which looks like a mini-spreadsheet, as attached below.

    Does this help?
    Attached Images Attached Images
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    Jul 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro relative vs absolute reference (2000)

    John - Thanks.
    - Your description unlocked the code. Note that in my example I switched the columns and rows, but your code helped to fix the problem.
    - I found the absolute vs relative toggle where you said it would be, in the ??? stop ??? macro button. First I had to make the button visible, then, in that button the spreadsheet is the toggle. Tell me where that is documented???
    - I have a very large spreadsheet from the beginning of the year. Then another from this month. I am comparing the two. I simply copied three columns from the newer to the older, and want to subtract the difference between two columns. The problem is: new rows are added and old ones deleted. Sometimes I need to insert some cells on the right, sometimes on the left. You have to look at the key reference number to know which to do when. Sometimes you add one row at a time, sometimes several. Then, when you insert cells, you mess up the math formula in another column, so the macro cleans that up as it goes. Each file is over 12,000 rows. Once I get through this nosebleed, I'll do some pivot tables to see who is adding and who is going away, etc. I can also do a pivot to see where things are happening.

    Do you know a better way to accomplish my objective?

    Thanks, Rob

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: macro relative vs absolute reference (2000)

    Rob, these answers may be less helpful. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    On your second point, in Excel 97, Help, Record a macro, part 5 explains the "Stop" button and relative and absolute macro recording. Not exactly immediately obvious. A better source of documentation is here, the Lounge. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    On your third point it all depends. If your data is all higgledy-piggledy, you may have to invest some time in either cleaning it manually or writing =IF(,,) statements (or macros) to get aligned colums of data where the data is arranged as one field per column. Then there are a few techniques for comparison, a common formula approach is to pick the common data elements between the two files (in database lingo it would be called the key field) and use Vlookup to compare the one recordset to the other using this key field as the lookup item, and then comparing whatever needs to be compared. There are other approaches, it depends on your data.

    I would also advise you to consider that if this data is significant in volume you may want to think carefully about if it should be entered and maintained in Access rather than Excel. Excel for analysis, Access for data, and the ability to move recordsets from one to the other is pretty good.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro relative vs absolute reference (2000)

    This arose not long ago, see This Thread

Posting Permissions

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