+ Reply to Thread
Results 1 to 14 of 14

FindReplace Macro replacing parts of number value instead of considering whole number

  1. #1
    Registered User
    Join Date
    05-06-2022
    Location
    Colorado
    MS-Off Ver
    365
    Posts
    8

    Question FindReplace Macro replacing parts of number value instead of considering whole number

    I've copied this macro for conducting a multi find and replace across a selection. I've also attached a reduced version of the spreadsheet this applies to.
    Please Login or Register  to view this content.
    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

    When the data in 'Sheet1:Owner Number' was instead a Zip Code (essentially a 5 digit number), the macro worked perfectly to replace the Zip Code in 'Registrations:ColumnB' with the appropriate name according to the name in 'Sheet1:ColumnB'

    But now that I've put the Owner Number (a variable 1-4 digit number) the macro doesn't work.
    It's taking an Owner Number like '45' and replacing it with the name "StecklerKoch" instead of "Goodman" as it should.

    What am I missing?
    Attached Files Attached Files
    Last edited by 6StringJazzer; 05-06-2022 at 09:43 AM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: FindReplace Macro replacing parts of number value instead of considering whole number

    Try this update to your code.

    I tried to test it but couldn't figure out what ranges I was being prompted for.

    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: FindReplace Macro replacing parts of number value instead of considering whole number

    How about
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    05-06-2022
    Location
    Colorado
    MS-Off Ver
    365
    Posts
    8

    Re: FindReplace Macro replacing parts of number value instead of considering whole number

    Quote Originally Posted by 6StringJazzer View Post
    Try this update to your code.

    I tried to test it but couldn't figure out what ranges I was being prompted for.

    Please Login or Register  to view this content.
    First range (Owner Number) would be the selection $B$2:$B$3315 and Second Range (Territory) would be both columns in Sheet1 including the headers, so Sheet1!$A$1:$B$785

  5. #5
    Registered User
    Join Date
    05-06-2022
    Location
    Colorado
    MS-Off Ver
    365
    Posts
    8

    Re: FindReplace Macro replacing parts of number value instead of considering whole number

    lookat:=x1Whole causes bug

    Sub MultiFindNReplaceNew()
    Dim Rng As Range
    Dim InputRng As Range, ReplaceRng As Range
    xTitleId = "Test"
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Owner Number Range ", xTitleId, InputRng.Address, Type:=8)
    Set ReplaceRng = Application.InputBox("Territory Range ", xTitleId, Type:=8)
    Application.ScreenUpdating = False
    For Each Rng In ReplaceRng.Columns(1).Cells
    InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, lookat:=x1Whole
    Next
    Application.ScreenUpdating = True
    End Sub

  6. #6
    Registered User
    Join Date
    05-06-2022
    Location
    Colorado
    MS-Off Ver
    365
    Posts
    8

    Re: FindReplace Macro replacing parts of number value instead of considering whole number

    Quote Originally Posted by Fluff13 View Post
    How about
    Please Login or Register  to view this content.
    Doesn't like your suggestion: x1Whole, , , , False, False

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: FindReplace Macro replacing parts of number value instead of considering whole number

    That is an l (lowercase L) not the number1.
    If you copy/paste rather than re-type it avoids such mistakes.

  8. #8
    Registered User
    Join Date
    05-06-2022
    Location
    Colorado
    MS-Off Ver
    365
    Posts
    8

    Re: FindReplace Macro replacing parts of number value instead of considering whole number

    Quote Originally Posted by Fluff13 View Post
    That is an l (lowercase L) not the number1.
    If you copy/paste rather than re-type it avoids such mistakes.
    Understood, but even xlWhole is bugging

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: FindReplace Macro replacing parts of number value instead of considering whole number

    In what way?

  10. #10
    Registered User
    Join Date
    05-06-2022
    Location
    Colorado
    MS-Off Ver
    365
    Posts
    8

    Re: FindReplace Macro replacing parts of number value instead of considering whole number

    Compile Error: Expected: named parameter

  11. #11
    Registered User
    Join Date
    05-06-2022
    Location
    Colorado
    MS-Off Ver
    365
    Posts
    8

    Re: FindReplace Macro replacing parts of number value instead of considering whole number

    Actually, when using 6StringJazzer's suggestion it worked. Just need to enter the code string correctly (Fluff13 - little "L" instead of "1"

    Sub MultiFindNReplaceNew()
    Dim Rng As Range
    Dim InputRng As Range, ReplaceRng As Range
    xTitleId = "Test"
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Owner Number Range ", xTitleId, InputRng.Address, Type:=8)
    Set ReplaceRng = Application.InputBox("Territory Range ", xTitleId, Type:=8)
    Application.ScreenUpdating = False
    For Each Rng In ReplaceRng.Columns(1).Cells
    InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, lookat:=xlWhole
    Next
    Application.ScreenUpdating = True
    End Sub

    Characters Matter!

  12. #12
    Registered User
    Join Date
    05-06-2022
    Location
    Colorado
    MS-Off Ver
    365
    Posts
    8

    Re: FindReplace Macro replacing parts of number value instead of considering whole number

    I'm new to the Forum, is there a way to close a thread or mark it as "Resolved/Answered"?

  13. #13
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: FindReplace Macro replacing parts of number value instead of considering whole number

    Please see my note in your first post. Your other posts still did not apply code tags.

    There are two ways to add code tags. One is to select the code part of the text so it is highlighted, then press the "#" button in the edit controls. The other is to simply type in the tags:

    [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]


    Why use code tags? Code tags:

    1. Are required by rules (see rule #2)
    2. Preserve spacing used to show code structure (otherwise leading spaces and repeated spaces are removed)
    3. Add scroll bars to navigate code with long lines or a lot of lines

  14. #14
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: FindReplace Macro replacing parts of number value instead of considering whole number

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

    If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.

    If you solved your own problem, please provide your solution so that somebody seeing this thread in a search for the same problem will see how it was solved.

+ 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: 3
    Last Post: 02-22-2021, 08:49 AM
  2. Making number value with two parts
    By warp765 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2020, 10:12 AM
  3. Replies: 0
    Last Post: 04-05-2013, 03:00 PM
  4. Replies: 5
    Last Post: 01-18-2013, 11:21 AM
  5. Replacing a UPC Number without Modifying a Model Number
    By SpyderPB6 in forum Excel General
    Replies: 4
    Last Post: 07-17-2012, 03:15 PM
  6. Data Security - Replacing first four digits of a membership number macro.
    By barksmith in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-04-2008, 11:16 AM
  7. Replies: 0
    Last Post: 06-28-2006, 01: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