+ Reply to Thread
Results 1 to 6 of 6

using vba to enter an excel formula by sheet position not sheet name

  1. #1
    Registered User
    Join Date
    07-14-2010
    Location
    chelmsford, england
    MS-Off Ver
    Excel 2007
    Posts
    16

    using vba to enter an excel formula by sheet position not sheet name

    Hi,

    I hope there's a very simple solution to this and its just my lack of vba knowledge thats giving me a problem finding it.

    I need to enter some formula into a sheet and these formula refer to data on the sheet two along. Unfortunately i don't know the sheets name as this is variable and defined during the course of the macro... only its position... 2 sheets along from the currently active sheet on which the formula is being entered.

    I would also be able to work out its absolute position in regards to the first sheet of the workbook if thats useful.

    I've put below an example of a piece of formula i want to enter using the macro...

    Please Login or Register  to view this content.
    So its basically just getting the max value from a column in sheet 'results_0.26pass', however i won't know that this is its name, only that it is 2 sheets along from the sheet in which i am asking vba to write the formula

    (alternatively I will know that it will be the name of the sheet in which the formula is being written ... "results_0.26pass" .... with the words "results_" removed... i.e. "0.26pass" in this case).

    I'd be greatful for any help!

    Ben
    Last edited by *benj; 07-28-2010 at 11:27 AM.

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: how do i ask vba to enter a "=Max('sheet1'!C[1])" knowing sheets position not nam

    This will return the name of the sheet 2 along:

    Please Login or Register  to view this content.

    and this the name of the sheet without 'results_':

    Please Login or Register  to view this content.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    07-14-2010
    Location
    chelmsford, england
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: how do i ask vba to enter a "=Max('sheet1'!C[1])" knowing sheets position not nam

    Sorry Dom, where do i position that within the formula I posted, I can't work out where to place it and get an error message wherever i try

  4. #4
    Registered User
    Join Date
    07-14-2010
    Location
    chelmsford, england
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: how do i ask vba to enter a "=Max('sheet1'!C[1])" knowing sheets position not nam

    Please Login or Register  to view this content.
    ??
    my problem is in getting the sheet name into the "MAX=" formula i'm asking vba to type into a cell
    Last edited by *benj; 07-28-2010 at 10:59 AM.

  5. #5
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: how do i ask vba to enter a "=Max('sheet1'!C[1])" knowing sheets position not nam

    Like this:

    Please Login or Register  to view this content.

    Dom

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: how do i ask vba to enter a "=Max('sheet1'!C[1])" knowing sheets position not nam

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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