+ Reply to Thread
Results 1 to 4 of 4

VBA if value in column A then replace in column B

  1. #1
    Registered User
    Join Date
    04-14-2014
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    28

    VBA if value in column A then replace in column B

    Hi
    Im currently using this VBA code that looks at values in column A in Sheet2 and if found it removes this value in column A in Sheet1.

    Please Login or Register  to view this content.
    So, on Sheet1 in column A I have:

    a white

    b black

    d white

    c orange

    On Sheet2 in column A I have:

    a

    b

    d

    c

    When I run the VBA, as a result I have White, Black, Orange, White, without a, b, c, d, as these values are in Sheet2.

    But if I have these in Sheet1 in column A:

    white a

    black b

    white d

    orange c

    the code does not work properly.

    Cheers

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,647

    Re: VBA if value in column A then replace in column B

    It worked fine for me. I'll bet that your real file is not using the same data that you are showing in your post.
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: VBA if value in column A then replace in column B

    Agreed with 6StringJazzer, those combos work.

    Where you will run into issues is things like c black >> clack or banana d >> banand

    This is because your values in each row are just strings. You mentioned yesterday you only wanted to replace "exact matches", but a concept like this doesn't exist natively in VBA inside a string; i.e. VBA doesn't understand the concept of "whole words" within a string. At least I am not aware that it does.

    Just off the top of my head you'd probably have to ditch the find and replace and maybe instead use range.Find, then check using ASCII values to see what's before and after each string. But this sounds like a headache.

    Maybe someone knows a better way with regular expressions or something.
    I'm interested in starting a career working with VBA, if anyone knows of any opportunities!

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,647

    Re: VBA if value in column A then replace in column B

    Quote Originally Posted by walruseggman View Post
    Where you will run into issues is things like c black >> clack or banana d >> banand
    This is very perceptive. It might be that a more sophisticated solution such as regular expressions would be the right fit. However, ghostexcel, we need much more detail about these strings you are working with. I suspect that the example in your first post is a toy example and that is oversimplifies your actual problem.

    If you can give more detail and a truly rigorous specification for what strings you want to find and remove then we can look at a more comprehensive solution, whether it uses regular expressions, Like, or something else.

+ 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: 9
    Last Post: 11-08-2015, 09:48 AM
  2. [SOLVED] Replace data in one column automatically based on another column
    By jzacharias in forum Excel General
    Replies: 1
    Last Post: 06-22-2015, 01:30 PM
  3. [SOLVED] Replace cell in Column A with value in Column B if contains similar character string
    By oybombadil in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-17-2015, 08:49 AM
  4. Replies: 3
    Last Post: 11-25-2014, 06:08 AM
  5. Replies: 4
    Last Post: 12-16-2013, 06:29 PM
  6. Replies: 4
    Last Post: 03-12-2012, 10:46 AM
  7. Replies: 2
    Last Post: 04-29-2011, 10:53 AM

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