find from one tab and replace on another---Urgent!

Posted on 2011-05-05
Last Modified: 2012-05-11
I have a workbook that consists of two tabs, table and data.  What I am needing to do is take the accts on the "table" tab and find them on the "data" tab.  When it finds one, change the content from what it shows on "table" and replace it in "data".  If it can't find any acct then just leave things alone.  

I know we can use lookups and such but what i would like to do is to be able to automate this process using a VBA script so that it is done without formula's.  

I have attached a test workbook that I think explains things better than I have done.  Any help would be greatly appreciated.

Question by:spudmcc
    LVL 43

    Accepted Solution

    Try this sub
    Sub tbl2dat()
    Set dat = ActiveWorkbook.Sheets("Data")
    Set tbl = ActiveWorkbook.Sheets("Table")
    On Error Resume Next
    For Each cel In tbl.Range("A:A")
    If cel.Value <> "" Then
    dat.Range("A:A").Find(cel.Value, , , xlWhole).Offset(0, 3) = cel.Offset(0, 1)
    End If
    Next cel
    On Error GoTo 0
    End Sub

    Open in new window


    Author Closing Comment

    That worked perfectly!  Thank you so much for your time, talent and knowledge.  I so much appreciate it!

    Andy (Spudmcc)

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now