+ Reply to Thread
Results 1 to 3 of 3

Long FormulaArray VBA

  1. #1
    Registered User
    Join Date
    10-30-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Long FormulaArray VBA

    I am trying to insert a long formula array through VBA. however, Replace part of the code does not seem to work. Any ideas why? Thanks

    [CODE][Sub Insert_All_Formulas()
    Dim sht As Worksheet
    Dim tbl As ListObject
    Dim FindColumn As Integer
    Dim rngData As Range
    Dim x, y, z As String

    Set sht = ThisWorkbook.Worksheets("INSERT_INITIAL_ASSET_LIST_HERE")


    x = "CONCATENATE(RC[-3],RC[-2],RC[-1])"
    y = "CONCATENATE(Table_Aggregated_ASSETLIST[[#All],[Description]],Table_Aggregated_ASSETLIST[[#All],[Manufacturer]],Table_Aggregated_ASSETLIST[[#All],[Model]])"
    z = "Table_Aggregated_ASSETLIST[[#All],[PPM PRICE]]"

    sht.Activate
    sht.Range("F2").Select
    Selection.FormulaArray = "=IFERROR(VLOOKUP(1111,CHOOSE({1,2},2222,3333),2,FALSE),""not found"")"


    Selection.Replace What:="x", Replacement:=x
    Selection.Replace What:="y", Replacement:=y
    Selection.Replace What:="z", Replacement:=z


    End Sub/CODE]

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,025

    Re: Long FormulaArray VBA

    I might be missing something but, at first glance, I can't see where the x, y znd z are in your formula for you to replace them with more complex elements.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,025

    Re: Long FormulaArray VBA

    You have inadvertently deleted one of the square brackets so, unfortunately ...


    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Long FormulaArray
    By XLVBA in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-08-2018, 03:23 PM
  2. .FormulaArray causes Run Time Error 1004, Unable to set the FormulaArray property
    By dluhut in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2017, 03:12 PM
  3. [SOLVED] .FormulaArray =
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-20-2016, 09:49 AM
  4. How to make Long FormulaArray macro to work? :(
    By Dahlia in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-12-2016, 08:50 PM
  5. [SOLVED] =IF and =SUMIF formulas creating long long long data processing times.
    By comp in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 03-26-2014, 02:59 PM
  6. long formula to vba macro - error 1004 unable to set formulaarray property
    By desibabuji in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-08-2013, 06:18 AM
  7. Using FormulaArray in VBA
    By marcelobf in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2006, 04:50 PM

Tags for this Thread

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