# Convert CSE formula to a macro

1. ## 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?

2. ## 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 ) )

3. ## 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. ## 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. ## 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.

##### Users Browsing this Thread

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

#### 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