Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Find-Replace Formula to VBA

    The closest answer I could find was at this URL at Step 2's example.
    HTML Code:
    http://smallbusiness.chron.com/replace-text-parts-ms-excel-39709.html
    Works fine except if I have a blank cell in H10 or no "M", it will sow an error.

    I thought the; 1,"") part of the formula would leave it blank, preferably a 0 would be fine.
    It also leaves me with a formula that remains dormant as it's only used once.

    The data-feed has numerical ranges either as 500M or 1000M, meaning it's either 3 or 4 numerricals then the letter M
    I need to remove only the letter M.

    Formula I tried located in Cell H11 is: =REPLACE(H10,FIND("M",H10),1,"")'Step 2's example.



    I tried to run this in VBA by changing a few things around to suit code to;

    Code:
    Sub TEST_FINDREPLACE()
     Sheets("Sheet1").Range("H12") = Application.Replace=(H10,FIND("M",H10),1,"")
    End Sub
    
    'Error message says;, "Sub or Function not defined" and hi-lites FIND in the formula.
    How can I make this work using VBA and to also include a "0" if Cell H10 is blank or if Cell H10 has no M after the numerics it will show the Value anyway?


    Thanks

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi XP

    ..if you tell us what the actual formula is in the cell, and what you want it to be, we could perhaps give you a simpler alternative vba solution.

    zeddy

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The "" part of the equation is what to replace the FIND part. It will replace 1 character with a null so will always remove 1 character. The location of the removed character is from where the FIND is.

    The FIND will give an error is nothing is found (ie no M)

    Perhaps you are looking for the SUBSTTITUTE:
    =Substitute(H10,"M","")

    This will substitute any Ms in the string with null (essentially removing all M from the string). You can also replace certain Ms but you have to know the location:
    =Substitute(H10,"M","",3)

    Will replace the 3rd M from H10.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    zeddy, the actual formula in Cell H11 is; =REPLACE(H10,FIND("M",H10),1,"")
    The Value in H10 can be 3 or 4 numerics ending with the letter M ( Metres )
    For example. 200M or 1000M
    Because it's part of a VBA process, I'm trying to eliminate as many formula in the workbook as possible, and use VBA where I can at a minimal where possible.

    Code:
    'code
    Sheets("Sheet1").Range("H11").FormulaR1C1 = "=REPLACE(R[-1]C,FIND(""M"",R[-1]C),1,"""")"
    ' then "remove" formula
    Sheets("Sheet1").Range("H11").Value = Sheets("Sheet1").Range("H11").Value
    'code continues....,
    I'm hoping to do this with 1 line of code.
    -------------,
    sdckapr
    =Substitute(H10,"M","",3)
    That works OK when there is 1000M, but not when there is 100M

    It needs to be for both scenarios 3 or 4 numerics.

    Thanks

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How many Ms are in H10 and which ones do you want to replace? Can you give us information on what H10 is?

    The should be no difference if it is 1000M or 100M the difference would be in the formula =Substitute(H10,"M","",3) is where the 3rd M is in H10. If you want to replace all the Ms then just use =Substitute(H10,"M","")

    If the number has a zero before the M you could also use something like:
    =Substitute(H10,"0M","0")

    to Only replace Ms after a zero and other Ms would not be replaced. As mentioned earlier, it depends on what H10 looks like and what you want the result to be.

    If you are going to replace the formula, there is no need to enter and change the formula. Just enter the value to begin with. but this will depend on what you want the results to be. the advantage of a formula is that it is live and will change as H10 changes and can also be copied down a column if you have many changes to be made. It seems overkill to me to have VBA just enter a formula and change the results for one cell. It would seem to be as efficient to to edit the contents yourself.

    Steve
    PS. FYI
    =REPLACE(H10,FIND("M",H10),1,"") is identical to =Substitute(H10,"M","",1)

    They will both replace only the 1st M in H10, the substitute will however NOT give an error if there is no M in H10, which I understood to be one of the issues...
    Last edited by sdckapr; 2013-08-18 at 11:14.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    XP,

    This code will trim the "M" off of any value and place a zero if blank or no "M".

    TrimM2.png

    HTH,
    Maud

    Code:
    Public Sub TrimM()
    'DECLARE AND SET VARIABLES
    Dim cell As Range
    Dim rng As Range
    Dim num As Variant
    Set rng = [b1:b9]
    '----------------------------------------------
    'TRIM THE M FROM THE END AND PLACE VALUE IN ADJACENT CELL
    For Each cell In rng
        If cell.Value = "" Then
            cell.Offset(0, 1).Value = 0
        Else:
            If Right(cell.Value, 1) = "M" Then
                num = Val(Str(Left(cell.Value, Len(cell.Value) - 1)))
                cell.Offset(0, 1).Value = num
            Else:
                cell.Offset(0, 1).Value = 0
            End If
        End If
    Next
    End Sub
    Last edited by Maudibe; 2013-08-18 at 16:17.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    XP,

    If you wanted to do this with one line of code then you can pass the parameter to a User defined function using the following line:

    Code:
    '----code----
    x=TrimM([b1])
    '----code----
    Code:
    Public Function TrimM(num As Variant) As Long
            If Right(num, 1) = "M" Then
                TrimM = Val(Str(Left(num, Len(num) - 1)))
            Else:
                TrimM = 0
            End If
    End Function
    Last edited by Maudibe; 2013-08-18 at 16:31.

  8. #8
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    sdckapr
    How many Ms are in H10 and which ones do you want to replace? Can you give us information on what H10 is?
    There is only 1 M, the information is web query import and this particular information ends in Cell H10
    It can range from 300M to 3000M. The M stands for Metres
    Once the M is removed, the Value is used for a Filter in another part of the process.
    That Filter has Greater Than or Less Than, Variable setting and also forms part of long term statistical references to fine tune the Variable.

    Maudibe

    Thanks for the codes, I'll test them all and hope to use the one-liner.

    Thanks all,

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi XP

    You can use this:
    Code:
    zValue = [H10]
    If [zValue] = "" Then
    [H11] = 0
    Else
    [H11] = Application.Substitute(zValue, "M", "")
    End If
    zeddy

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi XP

    If you want to do it in one vba line, use this:
    [H11] = Replace("0M" & [H10], "M", "")

    ..try it!
    ..in cell [H11] it will display a zero if [H10] is blank
    ..in cell [H11] it will display the same value as in cell [H10] if there is no M, otherwise..
    ..in cell [H11] it will display the value from cell [H10] with any "M" removed.

    zeddy
    Last edited by zeddy; 2013-08-18 at 17:13.

  11. #11
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    If you want to do it in one vba line, use this:
    [H11] = Replace("0M" & [H10], "M", "")

    ..try it!
    Works good.

    Question...
    The use of the square brackets [ ] is new to me, what do they signify or replace ?
    Or where's the "connection" where you have [H11] and [H10] ?

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    XPDH,

    In Excel VBA the [ ] are shorthand for a cell value like Range("H11").value. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks RG

    VBA short hand....

    I am definitely in the right place, Windows Secrets!

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi XP

    As RG says, square brackets [ ] are shorthand for a cell (or range e.g. [C4:AK47].
    Saves a lot of typing in my opinion.
    It assumes the current sheet you are on, unless you tell it otherwise, e.g. Sheet("Fred").[H4]
    Also, if you have a named range, e.g. "dataBlock", you can use [dataBlock] in your vba code.
    Named ranges can refer to a single cell, e.g. [spinnerLinkcell] as well as blocks of data.

    Did you like my prefix of "0M" in my previous reply?
    The zero in "0M" is required to ensure that a zero is left when the cell [H11] is blank.
    It has no effect if the cell [H11] contains just a number e.g. 3000, and the "M" in "0M" gets replaced anyway.
    It took me a while to get that without triggering a vba error!

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •