+ Reply to Thread
Results 1 to 5 of 5

Convert CSE formula to a macro

  1. #1
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,124

    Convert CSE formula to a macro

    I am using the following Control Shift Enter (CSE) formula to find a value.

    Please Login or Register  to view this content.

    I was trying to modify as following using variable LastRow to define my range.
    Please Login or Register  to view this content.
    Is there a way to convert this so I can use it in a Macro?
    Last edited by maacmaac; 04-22-2009 at 10:16 PM.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Convert CSE formula to a macro

    Do you need a macro? You could create a dynamic named range, and then modify the formula.

    Define

    frmLastRow refers to =MATCH(9E+307, RawData!$A:$A, 1)

    tbl refers to =INDEX(RawData!$A:$A, 2):INDEX(RawData!$G:$G, frmLastRow)

    Then the formula changes to =INDEX(INDEX(tbl, 0, 7), MATCH(1, (INDEX(tbl, 0, 1) = 60612105) * (INDEX(tbl, 0, 6) = Data!D2), 0 ) )
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,124

    Re: Convert CSE formula to a macro

    I would prefer using a macro as I have most of the code now working except for this part and I am taking my data from an external Excel file.

    To come to the end result, the code is going through the following steps:

    1. Open outside data file (source workbook)
    2. Copy rows which are part of a "block trade" to destination workbook
    3. Lookup trade ticket number (sub TradeTicketNumber)
    4. Close outside data file

    It is the third step that I having trouble with the code. I am not sure if the way I approached this is the most effecient way to do this so if you have any comments on a different/more effective approach I would appreciate it.

    Destination file and raw data file with full code attached if needed.

    Please Login or Register  to view this content.

  4. #4
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,124

    Re: Convert CSE formula to a macro

    Shg,

    I followed your advice and used the formulas you recommended. It is working outside the marcro but I am having trouble within the macro. I am using in a Marcro as follows:

    Please Login or Register  to view this content.
    The only issue I have is how to increment the data in column D. I tried using the following but it didn't work. Also, is it possible to pass a value instead of the formula?

    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,124

    Re: Convert CSE formula to a macro

    After many hours of frustration, I finally figured out how to fix formula to include in a macro by searching many examples from this forum. Changed the formula from:
    Please Login or Register  to view this content.
    changed to
    Please Login or Register  to view this content.
    Thanks for original code.

+ 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