+ Reply to Thread
Results 1 to 4 of 4

Find all instances from Sheet1 Column B in Sheet2 Column B, Update Sheet2 Column A

  1. #1
    Registered User
    Join Date
    04-14-2015
    Location
    Lawrenceville, GA
    MS-Off Ver
    Office 2010
    Posts
    2

    Find all instances from Sheet1 Column B in Sheet2 Column B, Update Sheet2 Column A

    I'm pretty new to VBA but over the last couple of months I've learned quite a bit by searching this forum but I can't seem to get over this hurdle. I have a workbook which contains 2 sheets. I'm trying to search for a range of values in sheet1 column B in sheet2 column B. If the value is found I am changing sheet2 column A to Delete. The problem I'm currently having is the macro finds only the first instance of the value and moves on to the next. I would like to find all instances of the value from sheet 1. Thanks in advance, any assistance provided will be greatly appreciated.

    Below is my current code and attached is a copy of my test workbook.


    Sub UpdateColumnA()

    Dim cell As Range, rngFind As Range, Found As Range, Found1 As Range, counter As Long
    Dim strFirstAddress As String

    'List of values to search for from Sheet1 column B
    With Sheets("Sheet1")
    Set rngFind = .Range("B1", .Range("B" & Rows.Count).End(xlUp))
    End With

    For Each cell In rngFind

    'Search in Sheet2 Column B
    Set Found = Sheets("Sheet2").Range("B:B").Find(What:=cell.Value, _
    LookIn:=xlValues, _
    LookAt:=xlPart, _
    MatchCase:=False)

    If Not Found Is Nothing Then
    strAddress = Found.Address
    Set Found1 = Found
    Do
    'When a match is found, set Column A to Delete
    Found.Offset(, -1).Value = "Delete" 'Value in Column A
    counter = counter + 1

    Set Found = Found.FindNext(Found)

    Loop While Not Found Is Nothing And Found.Address <> strAddress

    End If
    Next cell
    MsgBox "Replacements made: " & counter, , "Replacements Complete"

    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Find all instances from Sheet1 Column B in Sheet2 Column B, Update Sheet2 Column A

    Try :-
    Please Login or Register  to view this content.
    Regards Mick

  3. #3
    Registered User
    Join Date
    04-14-2015
    Location
    Lawrenceville, GA
    MS-Off Ver
    Office 2010
    Posts
    2

    Re: Find all instances from Sheet1 Column B in Sheet2 Column B, Update Sheet2 Column A

    Thanks Mick, that worked perfectly.

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Find all instances from Sheet1 Column B in Sheet2 Column B, Update Sheet2 Column A

    You're welcome

+ 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: 2
    Last Post: 07-14-2015, 04:32 PM
  2. Replies: 9
    Last Post: 10-06-2014, 01:49 PM
  3. [SOLVED] copy data from sheet1 to sheet2 based on column (A) sheet1 and column (C) sheet2
    By AWITCHER in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-13-2014, 03:20 PM
  4. [SOLVED] Simple formula to match column A, sheet1, with column A, sheet2, return text answer
    By Connie5761 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-14-2012, 02:56 PM
  5. Search Sheet2 Column A For Value In Textbox On Sheet1 Then Offset Right By 1-5 Column
    By sweetrevelation in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-08-2009, 01:32 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