+ Reply to Thread
Results 1 to 7 of 7

Recording Macro using relative reference.... need to have a ROWS function unchanged

Hybrid View

bmbalamurali Recording Macro using... 11-04-2013, 11:23 AM
JOHN H. DAVIS Re: Recording Macro using... 11-04-2013, 12:44 PM
bmbalamurali Re: Recording Macro using... 11-04-2013, 01:06 PM
HaHoBe Re: Recording Macro using... 11-05-2013, 02:10 PM
bmbalamurali Re: Recording Macro using... 11-06-2013, 06:02 AM
HaHoBe Re: Recording Macro using... 11-06-2013, 02:05 PM
bmbalamurali Re: Recording Macro using... 11-11-2013, 01:23 AM
  1. #1
    Forum Contributor
    Join Date
    10-08-2013
    Location
    Chennai, INDIA
    MS-Off Ver
    Excel 2010
    Posts
    157

    Question Recording Macro using relative reference.... need to have a ROWS function unchanged

    Hi All,

    I have a complex formula to retrieve data as i desired, the formula contains ROWS funtion Ex: Rows($1:1) to get 1,2,3,..... when i autofill it in below rows...

    My question is i clicked relative reference button and started recording macro from cell A2.. with rows($1:1) and dragged down and got answer...
    why i used relative refernce is wherever i click and run the macro it should do the steps without cell specifications...

    but here when i run this macro in some other cell, Ex: in H6 its not working that is its not giving me the answer 1,2,3... its giving 6,7,8....

    How can i make a macro without this wrong answer... i need the maro to give me answer as 1,2,3.... wherever i clivked and run it...

    hope i explained clearly... am not able to upload example excel.... If my question is not clarifiable without example excel.. please let me know...

    Thanks...
    Bala

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Recording Macro using relative reference.... need to have a ROWS function unchanged

    Can you post your code?

  3. #3
    Forum Contributor
    Join Date
    10-08-2013
    Location
    Chennai, INDIA
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Recording Macro using relative reference.... need to have a ROWS function unchanged

    Hi Davis,

    Thanks....Pls find.

    Sub PRC()
    '
    ' PRC Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+H
    '
        ActiveCell.FormulaR1C1 = "Processor"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "Reviewer"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "Comments"
        ActiveCell.Offset(1, -2).Range("A1").Select
        ActiveCell.FormulaR1C1 = _
            "=INDEX(Sheet2!C1,MATCH(ROWS(R1:R[-1])-1,Sheet2!C3)+1)"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = _
            "=INDEX(Sheet2!C4,MATCH(ROWS(R1:R[-1])-1,Sheet2!C6)+1)"
        ActiveCell.Offset(0, -1).Range("A1:B1").Select
        Selection.AutoFill Destination:=ActiveCell.Range("A1:B29"), Type:= _
            xlFillDefault
        ActiveCell.Range("A1:B29").Select
        Selection.AutoFill Destination:=ActiveCell.Range("A1:B30"), Type:= _
            xlFillDefault
        ActiveCell.Range("A1:B30").Select
        Selection.AutoFill Destination:=ActiveCell.Range("A1:B35"), Type:= _
            xlFillDefault
    End Sub

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 365 on Win11 (365 on Win11 (desktop), 365 on Win11 (notebook)
    Posts
    8,206

    Re: Recording Macro using relative reference.... need to have a ROWS function unchanged

    Hi, bmbalamurali,

    maybe something like this:
    Sub PRC()
    '
    ' PRC Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+H
    '
      With ActiveCell
        .Value = "Processor"
        .Offset(0, 1).Value = "Reviewer"
        .Offset(0, 2).Value = "Comments"
        .Offset(1, 0).FormulaR1C1 = _
              "=INDEX(Sheet2!C1,MATCH(ROW()-" & .Row & ",Sheet2!C3))"
        .Offset(1, 1).FormulaR1C1 = _
              "=INDEX(Sheet2!C4,MATCH(ROW()-" & .Row & ",Sheet2!C6))"
        .Offset(1, 0).Resize(1, 2).AutoFill Destination:=.Offset(1, 0).Resize(35, 2), Type:= _
              xlFillDefault
      End With
    End Sub
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  5. #5
    Forum Contributor
    Join Date
    10-08-2013
    Location
    Chennai, INDIA
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Recording Macro using relative reference.... need to have a ROWS function unchanged

    Hi HaHoBe,

    I tried your macro its giving wrong values in formulas ie: its not allocating persons name correctly...

    request your support in my other post which same as this one....
    http://www.excelforum.com/excel-prog...r-of-data.html

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 365 on Win11 (365 on Win11 (desktop), 365 on Win11 (notebook)
    Posts
    8,206

    Re: Recording Macro using relative reference.... need to have a ROWS function unchanged

    Hi, bmbalamurali,

    I tried your macro its giving wrong values in formulas ie: its not allocating persons name correctly...
    You didn´t supply a sample workbook, the code was doing fine in the book I created for this.

    Regarding your link:

    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. You may continue in your other thread.

    Ciao,
    Holger

  7. #7
    Forum Contributor
    Join Date
    10-08-2013
    Location
    Chennai, INDIA
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Recording Macro using relative reference.... need to have a ROWS function unchanged

    Thanks HaHoBe,

    Regret for violating forum rules... Have found solution with my other post... Solved..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Recording Macro with Relative Reference
    By a1chirag in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-02-2013, 08:32 AM
  2. How to use relative reference with a Function?
    By GSM in forum Excel General
    Replies: 0
    Last Post: 07-21-2011, 11:19 AM
  3. Named formulas to reference relative rows?
    By atreyu55 in forum Excel General
    Replies: 12
    Last Post: 06-17-2009, 06:14 AM
  4. [SOLVED] Recording Macro, toggle absolute/relative reference, button disapp
    By Kayos in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-18-2005, 04:05 PM
  5. Recording new excel macro, relative reference button doesn't show
    By Dano in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 08-23-2005, 03:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1