+ Reply to Thread
Results 1 to 3 of 3

Using Formulas/Functions in VBA code

  1. #1
    Registered User
    Join Date
    03-04-2005
    Posts
    34

    Using Formulas/Functions in VBA code

    Hello All,

    Since I got such great help the first time around, will give it another go. Am putting a macro together that will create an index sheet for the report being opened. Nothing fancy, just finds the sheet names, copies the names, and creates links to those specific sheets within the workbook. What I would like to do is to add an additional line which will add a title to the index based on the title in the summary sheet. I have the following formula which, if inserted directly into a cell will give the disired results

    Please Login or Register  to view this content.
    I would like to be able to add this into the VBA code but have not had any success adding it using a line such as:

    Please Login or Register  to view this content.
    An error is generated when trying to run the above code and looking for alternatives if possible...

  2. #2
    Registered User
    Join Date
    08-24-2010
    Location
    California
    MS-Off Ver
    Excel 2007, 2010
    Posts
    45

    Re: Using Formulas/Functions in VBA code

    mike703,

    When you create the string for the formula, you need to replace all of the " (double quote) symbol with chr(34) so that Excel will use it as a symbol rather than evaluating it. In addition, you also need a "=" sign in front of the formula.

    So you might want something like:

    dim strTemp
    strTemp = "= " & chr(34) & "INDEX for " & chr(34) & " & Right(Summary!A12, Find(" & chr(34) & "M" & chr(34) & ",Summary!A12)-1) & " & chr(34) & " Financials" & chr(34)

    You can add a msgbox statement to validate the formula so it reads right to you:
    Msgbox "formula content is: " & strTemp

    m3atball

  3. #3
    Registered User
    Join Date
    03-04-2005
    Posts
    34

    Re: Using Formulas/Functions in VBA code

    BooYah! That worked like a champ! Thanks for the help....would not have figured out the "chr(34)" trick on my own.

+ 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