+ Reply to Thread
Results 1 to 5 of 5

VBA code to convert absolute to semi-relative referenced range

  1. #1
    Registered User
    Join Date
    10-29-2011
    Location
    EU
    MS-Off Ver
    Excel for Mac 2011
    Posts
    2

    VBA code to convert absolute to semi-relative referenced range

    I want to create a function in VBA that has a cell and a range as input like this:
    MyFunction (cell1, range1)
    ...
    End function
    Calling MyFunction with input parameters cell1 = A1 and range1 = $A$1:$A$10 MyFunction should evaluate the following formula:
    RANK(A1,$A$1:$A$10,0)+COUNTIF(A$1:A1,A1)-1
    The difficulty with this is that the input range is specified as an absolute reference ($A$1:$A$10) but the formula should use a semi-relative range in the count if statement (A$1:A1).

    Can someone please show me the code to do this?

    Many Thanks !
    Last edited by gpk; 10-31-2011 at 04:24 AM.

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,370

    Re: VBA code to convert absolute to semi-relative referenced range

    If you need a formula as a string, then something like this:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-29-2011
    Location
    EU
    MS-Off Ver
    Excel for Mac 2011
    Posts
    2

    Re: VBA code to convert absolute to semi-relative referenced range

    Thanks for your post. To clarify further: I do not want a string as output. Output should be the result of the formula, so in this case an integer.

  4. #4
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,370

    Re: VBA code to convert absolute to semi-relative referenced range

    Let's try to use Evaluate. In any case, see attached file.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by nilem; 10-29-2011 at 08:54 AM. Reason: use Evaluate

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: VBA code to convert absolute to semi-relative referenced range

    Please Login or Register  to view this content.
    e.g. in cell B1
    PHP Code: 
    =gpk(A1,A1:A10,C1:C7
    PS. I have no idea what A$1:A1 is different from A1 or $A1 or $A$1 or A$1



+ 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