Results 1 to 8 of 8

More help on Find and Replace from one sheet to the next

Threaded View

  1. #1
    Registered User
    Join Date
    06-26-2019
    Location
    California, US
    MS-Off Ver
    Office 365
    Posts
    36

    More help on Find and Replace from one sheet to the next

    I'm trying to find and replace cells by looking at a value from one sheet, and replacing it with a value from another sheet.
    I'm currently modifying a code from my last thread by jolivanes (thank you!)

    I think I'm running into an issue using this code because some of the cells are combined, so it's not properly searching down the column. But I'm not sure.

    The code should:
    1. Find a name from (Sheet 1 column A) in (Sheet 2 column E)
    2. If it's found, then replace (Sheet 2 Column K, same row) with the value from (Sheet 1 column B, same row)

    Dim s1 As Worksheet
    Dim s2 As Worksheet
    Dim al As Range
    
    Set s1 = Worksheets("Sheet1")
    Set s2 = Worksheets("Sheet2")
    
            For Each al In s1.Range("A2:A" & s1.Cells(Rows.Count, 1).End(xlUp).Row)
                If WorksheetFunction.CountIf(s2.Range("E2:E" & s2.Cells(Rows.Count, 1).End(xlUp).Row), al.Value) <> 0 Then
                    s2.Range("E2:E" & s2.Cells(Rows.Count, 1).End(xlUp).Row).Find(al.Value, , , 1).Offset(, 4).Value = al.Offset(, 1).Value
                End If
            Next al
    I've attached a sample worksheet to give you an idea of how the values might look.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find and replace from one sheet to another.
    By Newbuilder in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-26-2017, 11:09 AM
  2. Multiple Find/Replace on a specified sheet and outputtingin a separe sheet
    By scfn in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2013, 07:27 AM
  3. Multiple find and replace from Glossary sheet to the active sheet
    By Chandru71 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2011, 12:34 PM
  4. how to Find and Replace using different excel sheet?
    By rythem_of_the_nig in forum Excel General
    Replies: 1
    Last Post: 08-30-2011, 06:38 AM
  5. Find/Replace on a protected sheet
    By LAF in forum Excel General
    Replies: 1
    Last Post: 07-10-2009, 09:32 PM
  6. Find/Replace on a protected sheet
    By LAF in forum Excel General
    Replies: 1
    Last Post: 06-23-2009, 07:39 AM
  7. trying to find #REF! in a formula to replace with a sheet name
    By funkymonkUK in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-28-2006, 06:25 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