[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

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

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.

1 Solution
Saqib Husain, SyedEngineerCommented:
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

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

Andy (Spudmcc)

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now