+ Reply to Thread
Results 1 to 3 of 3

Problem with text functions

  1. #1
    Registered User
    Join Date
    02-11-2014
    Location
    Poland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Problem with text functions

    Hello All,

    I have a problem with text formulas in VBA. The goal of the formula is to remove some parts of formula and brackets when the certain condition is fulfilled. When the formula is related to the cell in the same sheet, all works excellent, but the problem appears when the reference is to another sheet. And how it goes:

    The formula starts with: =('Cost Drivers & Assumptions'!J60)/SEK

    After i.Formula = Left(i.Formula, Oko - 2) it look like that: =('Cost Drivers & Assumptions'!J60)
    After i.Formula = Right(i.Formula, Len(i.Formula) - 2) it is: 'Cost Drivers & Assumptions'!J60)
    After i.Formula = Left(i.Formula, Len(i.Formula) - 1) it is : 'Cost Drivers & Assumptions'!J60

    Oko = InStr(1, i.Formula, "SEK")

    Up to this point everything is ok and now there should only be added "=" sign at the beginning and everything would be fine but it isn't

    After i.Formula = "=" & Right(i.Formula, Len(i.Formula)) the "=" is not added and the result in cell is 'Cost Drivers & Assumptions'!J60

    Strangely when I add an apostrophe after "=" into code like that : i.Formula = "='" & Right(i.Formula, Len(i.Formula))

    It works fine but later on is got screwed after many calculations. Does anybody have an idea why "=" cannot be simply added at the beginning and what might lie behind it? I have no idea. your help is very appreciated.

    Best regards,

  2. #2
    Forum Expert
    Join Date
    02-22-2013
    Location
    London / Stockholm
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Problem with text functions

    If i is defined as a range, try this:

    Please Login or Register  to view this content.
    Cheers, berlan

  3. #3
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: Problem with text functions

    When building formulas, build the string first. You can then use MsgBox or Debug.Print to view the string to see if it was concatenated and built properly. Sometimes, one can use ()'s to force concatenation on the fly on the right side of the equality sign. I prefer the first method myself.

+ 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. Replies: 1
    Last Post: 02-25-2013, 04:25 PM
  2. [SOLVED] Multiple Functions Problem
    By komhs in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-18-2012, 10:43 AM
  3. [SOLVED] Removing text from scores in a data sheet using text functions
    By mrvp in forum Excel General
    Replies: 9
    Last Post: 07-15-2012, 05:33 PM
  4. Problem with Min and Max functions
    By curiousexcel in forum Excel General
    Replies: 3
    Last Post: 10-19-2008, 09:38 PM
  5. Problem using VBA functions....
    By Richard Latter in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 02-28-2005, 03:06 PM

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