+ Reply to Thread
Results 1 to 8 of 8

Use variable column reference in VBA worksheet function

Hybrid View

  1. 07-09-2014, 08:01 AM #1
    MariaM02
    MariaM02 is offline
    Registered User
    Join Date
    06-25-2014
    Location
    Michigan, US
    MS-Off Ver
    2010
    Posts
    8

    Use variable column reference in VBA worksheet function

    I've been stuck on this for a while now, I hope someone can help. I have the following line in VBA which works.




    ActiveSheet.Cells(2, 3).Formula = "=INDEX(Matrix!D:D,MATCH(" & ActSheet & "!$A2,Matrix!$A:$A,0))*INDEX(Matrix!$V:$V, MATCH(" & ActSheet & "!C$1,Matrix!$S:$S,0))"
    I need to replace the range $V:$V because different sheets require different columns as a reference. I have a variable that will change depending on the sheet. How can I work it into the formula using the correct syntax?

    Any help is greatly appreciated!

    Thanks!
    Maria
    Reply With QuoteRegister To Reply
  2. 07-09-2014, 08:08 AM #2
    haripopuri
    haripopuri is offline
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: Use variable column reference in VBA worksheet function 

    ActiveSheet.Cells(2, 3).Formula = "=INDEX(Matrix!D:D,MATCH(" & ActSheet & "!$A2,Matrix!$A:$A,0))*INDEX(Matrix!" & YourVariableName & ", MATCH(" & ActSheet & "!C$1,Matrix!$S:$S,0))"
    Last edited by JBeaucaire; 07-11-2014 at 10:53 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.
    ________________________________________________________
    If your problem is solved, update the thread as SOLVED: Go to the top of the first post-Select Thread Tools-Select Mark thread as Solved OR - Go to the first post - Click edit- Click Advance- Just below the word "Title:" you will see a dropdown with the word No prefix.- Change to Solve- Click Save.

    Show your gratitude to the person who helped you solving your problem by clicking on star button at the bottom of such post.
    Reply With QuoteRegister To Reply
  3. 07-09-2014, 09:25 AM #3
    MariaM02
    MariaM02 is offline
    Registered User
    Join Date
    06-25-2014
    Location
    Michigan, US
    MS-Off Ver
    2010
    Posts
    8

    Re: Use variable column reference in VBA worksheet function

    Haripoupuri,


    Thank you, this helps, but how would I do this if my variable is just the column number? Do I have to have it in a different form?
    Reply With QuoteRegister To Reply
  4. 07-09-2014, 08:08 AM #4
    nathansav
    nathansav is offline
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Use variable column reference in VBA worksheet function 

    strColumn="$V:$V"
ActiveSheet.Cells(2, 3).Formula = "=INDEX(Matrix!D:D,MATCH(" & ActSheet & "!$A2,Matrix!$A:$A,0))*INDEX(Matrix!" & strColumn & ", MATCH(" & ActSheet & "!C$1,Matrix!$S:$S,0))"
    Last edited by JBeaucaire; 07-11-2014 at 10:53 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me
    Reply With QuoteRegister To Reply
  5. 07-09-2014, 09:28 AM #5
    MariaM02
    MariaM02 is offline
    Registered User
    Join Date
    06-25-2014
    Location
    Michigan, US
    MS-Off Ver
    2010
    Posts
    8

    Re: Use variable column reference in VBA worksheet function

    Nathansav,

    I understand what you did, I guess my question now is the variable is driven by the active sheet so I don't know I would get it to the format you wrote it in.
    Reply With QuoteRegister To Reply
  6. 07-09-2014, 09:48 AM #6
    nathansav
    nathansav is offline
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Use variable column reference in VBA worksheet function

    Are you not still using ActSheet in my way?
    Reply With QuoteRegister To Reply
  7. 07-09-2014, 10:14 AM #7
    haripopuri
    haripopuri is offline
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: Use variable column reference in VBA worksheet function 

    Sub test()
MyStr = Left(Cells(, 7).Address, 2) & ":" & Left(Cells(, 7).Address, 2) 'where 7 is the column number
ActiveSheet.Cells(2, 3).Formula = "=INDEX(Matrix!D:D,MATCH(" & actsheet & "!$A2,Matrix!$A:$A,0))*INDEX(Matrix!" & MyStr & ", MATCH(" & actsheet & "!C$1,Matrix!$S:$S,0))"
End Sub
    Reply With QuoteRegister To Reply
  8. 07-10-2014, 12:38 PM #8
    MariaM02
    MariaM02 is offline
    Registered User
    Join Date
    06-25-2014
    Location
    Michigan, US
    MS-Off Ver
    2010
    Posts
    8

    Re: Use variable column reference in VBA worksheet function

    Nathansav,

    I tried that but it still doesn't work. My variable is the name of the sheet, which matches the name of the column that needs to be refereneced, how would I write in into the variable like you did MyStr?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Use variable column reference in VBA worksheet function
    By MariaM02 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-08-2014, 08:29 AM
  2. OFFSET function - column reference over the edge of the worksheet
    By ronaldev in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2013, 10:09 PM
  3. how do i reference a worksheet variable in a formula?
    By chemeng1T3 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-06-2012, 08:27 AM
  4. Is it possible to reference a variable worksheet name in a formula?
    By HelpExcel in forum Excel General
    Replies: 2
    Last Post: 10-06-2009, 04:32 AM
  5. [SOLVED] how to reference a worksheet with a variable vs a name
    By ibbm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-17-2006, 03:00 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1