+ Reply to Thread
Results 1 to 3 of 3

VBA Text to Columns wipes out array formulas

  1. #1
    Registered User
    Join Date
    06-26-2014
    Location
    PA, USA
    MS-Off Ver
    2010
    Posts
    2

    VBA Text to Columns wipes out array formulas

    I have a workbook with several rows of array formulas (Rows 181:187) that reference a row of entity numbers (in Row 180). Because I've made updates in other parts of the workbook, I need the entity numbers in Row 180 to be numbers instead of the text values that they currently are. If I manually do text to columns on the individual cells this is fine and solves my problems. But of course I'd prefer to automate this process. I'm including this in a larger macro that I run to update the workbook:

    Dim LstCo As Long, i As Long
    LstCo = Cells.Find(What:="*", SearchOrder:=xlColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
    For i = 1 To LstCo
    Rows("180:180").Select
    Columns(i).TextToColumns Destination:=Cells(1, i), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.NumberFormat = "General"
    Next i

    Works great to solve my text to number issue. However, it wipes out all of the {} on the array formulas in rows 181:187 for each column. I'd prefer to fix the problem through this piece of code instead of trying to add array formulas to the macro as they're too long and would need to be split. Any ideas???? Thanks in advance!

  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
    48,150

    Re: VBA Text to Columns wipes out array formulas

    Untested, but try:

    Please Login or Register  to view this content.
    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
    Registered User
    Join Date
    06-26-2014
    Location
    PA, USA
    MS-Off Ver
    2010
    Posts
    2

    Re: VBA Text to Columns wipes out array formulas

    Thanks so much! That worked like a charm!

+ 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. Question re Array Formulas to Check Date Ranges from multiple columns
    By SouthpawInRelief in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-19-2013, 02:23 PM
  2. [SOLVED] Populate a unique list from two columns- array formulas to slow!
    By strud in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-08-2013, 11:44 AM
  3. Array Formulas instead of helper columns
    By ElmerS in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-25-2009, 03:52 PM
  4. Array Formulas and Text String LookUps
    By a8vry in forum Excel General
    Replies: 1
    Last Post: 04-09-2009, 12:35 PM
  5. Array Formulas and Text String LookUps
    By a8vry in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-09-2009, 12:10 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