+ Reply to Thread
Results 1 to 6 of 6

Excel function in VBA using the Replace function

  1. #1
    Forum Contributor
    Join Date
    05-30-2013
    Location
    Isreal
    MS-Off Ver
    Excel 2010
    Posts
    191

    Excel function in VBA using the Replace function

    I am trying to build a Excel function in VBA using the Replace function The function I build checks if the length of the text is bigger then a defined value by the user as long as it is bigger it goes through a series of if's.
    However it seems that only the first condition for replacing text is working.

    If you look at the example spreadsheet I attached to this question.
    That column "a" has the original text and column "b" the substitute text
    For an example in b2 the word "bin" is replaced with "no bin" however the other condition that replaces the word "blue" with "no blue".Seems not to be working.
    What am I doing wrong?
    Here is the code I am using

    Please Login or Register  to view this content.
    Function title_trim(title, length) As String
    If Len(title) > length Then
    title = Replace(title, "bin", "no bin")
    ElseIf Len(title) > length Then
    title = Replace(title, "blue", "no blue")
    ElseIf Len(title) > length Then
    title = Replace(title, "greem", "no green")
    ElseIf Len(title) > length Then
    title = Replace(title, "pink", "")
    End If

    title_trim = title
    End Function

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Excel function in VBA using the Replace function

    Use Independent If instead of ElseIf.

    ElseIf will end when any of the if matches the condition.

    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,445

    Re: Excel function in VBA using the Replace function

    Hi,

    I've amended your code - see if this helps.

    Please Login or Register  to view this content.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Excel function in VBA using the Replace function

    Even better than my Post #2 suggestion

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,554

    Re: Excel function in VBA using the Replace function

    Try this
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    05-30-2013
    Location
    Isreal
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Excel function in VBA using the Replace function

    Thank you.
    It seems to work.
    Have a nice day.

+ 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. Excel 2010: Replace Function Help, please
    By Taemex in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-27-2014, 12:19 AM
  2. Excel VBA replace certain cell(s) with RIGHT function
    By spoliquin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2013, 09:44 PM
  3. VBA Function to replace an excel formula
    By rakeshplb in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-30-2011, 04:26 AM
  4. Replace function in Excel
    By ravins23 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-22-2008, 02:10 AM
  5. Using REPLACE function in Excel
    By GarryG in forum Excel General
    Replies: 1
    Last Post: 06-16-2005, 11:05 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