Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro troubles - relative referencing, but not really.

    Hello,

    My problem is relatively simple, yet I think my phrasing whilst searching for answers is off.

    I have a macro that references relatively but I need it to do so with +3 on the rows...

    I.e. I have two tables, and I want to transfer data from the first table to the second however the second table has extra variables per row and as such the macro fills in the blanks with data that is meant to be 3 rows later.

    How do I offset this?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts

    Use of Offset Property

    Use the Offset property to move values

    Range("A1").Offset(3, 0).Value = Range("A1").Value 'Offset 3 down, 0 to the right
    Range("A1").Offset(0, 3).Value = Range("A1").Value 'Offset 0 down, 3 to the right
    Range("D1").Offset(-3, 0).Value = Range("D1").Value 'Offset 3 up, 0 to the right
    Range("A4").Offset(0, -3).Value = Range("A4").Value 'Offset 0 down, 3 to the left

    Or
    you can just do a copy and paste of the table

    Public Sub MoveTable()
    Range("A1:C3").Select
    Range("A1:C3").Copy
    Range("D6:F9").Select
    ActiveSheet.Paste
    End Sub

    HTH,
    Maud

  3. #3
    New Lounger
    Join Date
    Jan 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is almost what I needed, instead of offsetting the values entering the new table I want to offset the relative reference the macro is making.

    I.e. The macro references Cell G15 when I want it referring to G12, this problem arises because the second table has three more rows per initial variable.

    Hope that make sense :P
    Last edited by fraser852; 2013-01-09 at 01:45.

  4. #4
    New Lounger
    Join Date
    Jan 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My Macro is as follows:

    ActiveCell.FormulaR1C1 = "=Sheet1!R[145]C[-2]"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[96]C[-2]"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[47]C[-2]"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[-2]"
    ActiveCell.Offset(-3, 0).Range("A1:A4").Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:AG4"), Type:= _
    xlFillDefault
    ActiveCell.Range("A1:AG4").Select

    The problem lies with [R], on the second data set it is 3 off, on the 3rd it is 6 off and so on.
    Last edited by fraser852; 2013-01-09 at 02:50.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts

    Overlapping source row

    The only problem I see is when the autofill statement directs entry into the cells of the fourth row from a source starting at row 2 rows up and 2 to the left. The source row runs into the second table. Follow the formulas on the fourth row of the table starting at its first cell. When you get to the third table cell, it starts to duplicate the entire second table row. Was this intentional?

    Maud

  6. #6
    New Lounger
    Join Date
    Jan 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your reply, the autofill is intentional, and the macro in question isn't the problem its the function it performs when I run it on the next "row."

    Blank Simplified Screenshot :http://i.imgur.com/lCpUr.jpg

    What the macro does is take the first region, denoted by the first row of each table labelled with a month, from each table and pastes it into the corresponding month for the first region in the consolidated table, which is the first merged cell row of the consolidated table.

    When I run this macro for the second region, the values the macro inputs into the cells are for regions/rows on the source tables that are three below the second region/the actual cell I want to reference.

    I'm sorry if my description is terrible, it's 11pm and it's been a long day.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't follow what you are doing. Your macro is putting in relative formulas, but since the activecell is not listed, I don't know what formulas re being entered. As to your diagram, I am not clear what cells need formulas. If you want to read from H22:W41 using formulas in G49:V52 and just extracting the October rows you can put in G49:
    =OFFSET($G$22,4*(ROW()-49)+1,COLUMN()-6)

    And copy it into the other cells in the range.

    For the range starting in G60 for November you could use:
    =OFFSET($G$22,4*(ROW()-59)+2,COLUMN()-6)

    Steve

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Also, it you wanted to have a formula in the larger table, combine the 2 lower tables you could use in H22:
    =OFFSET($A$1,(ROW()-23)/4+3+MATCH($G22,$C:$C,0),COLUMN()-2)

    And copy it throughout the table (you will get errors if there is no MATCHing table for the Months.

    Steve

  9. #9
    New Lounger
    Join Date
    Jan 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The problem isn't the formula it's the different structures of the two tables.

    As I said before it's the function the macro performs when run on the next "region" below the first.

    In the original tables, the second region is exactly one row beneath the first i.e. they are sequential.

    In the consolidated table the second region is actually four rows beneath the first since I've merged the cells in the first column.

    This means that when I run the macro to populate the second region with data from the second rows of the original tables, it instead uses data from the fourth row to populate that region.

    Hence, the macro needs to think the active cell is four rows above what it actually is.

    I really can't be clearer than that.

    Also the SS, is a very very very simplified version, the other tables are on other sheets and are much much larger.

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Instead of offsetting the values entering the new table I want to offset the relative reference the macro is making
    Fraser,

    Are you looking for something like this?
    Cells(1, 1).Value = Cells(7, 7).Offset(1, 0).Value

    HTH,
    Maud
    Last edited by Maudibe; 2013-01-09 at 23:36. Reason: spelling

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    My point is that since the macros are just creating formulas, you can skip the macro altogether and just create the formulas directly...

    The offset formulas can be adapted to modified setup much easier than a macro could be. If you want help adapting a macro, you will have to be much more specific on what exactly you want the code to do and give us the exact setup to do any coding.

    Steve

Posting Permissions

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