Using Find Function in a Macro

[MrLookout9]

Long time viewer, first time poster.

I want to build a macro to push a value from an input cell to table on another tab. For the sake of this forum, I made a very simplified version of what I want to do.

On my second tab I have a table with TV shows and number of seasons:

Dexter 6
True Blood 4
Scrubs 9

On my first tab I have a data validation in A2, containing the titles. B2 is an open space where I would like the input to be. Next to that I have an image for which I want to assign the macro (Image of Staples Easy button). So in theory, I would select the show from the drop down list, enter the new number in B2 then click submit to update the table on tab 2.

Here is how I structured the Macro:
Select A2
Copy
Ctrl+F
Paste
Find Next
Close Find
Right Arrow
Copy B2
Ctrl+ Page Down
Ctrl+F
Find Next
Close Find
Right Arrow
Paste

Here is what the code looks like:
Range("A2").Select
Selection.Copy
Cells.Find(What:="True Blood", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
Cells.Find(What:="True Blood", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("B4").Select
ActiveSheet.Paste


Here are my two problems:
1. The find function has hard coded my selection "True Blood" into the code. I believe this should be a string. I have tried entering Range("A2") but that does not work. I also need to consider that on the second find I am on another tab.

2. After finding the show, click the Right arrow should select the cell in column B, instead, again this is hardcoded to "B4".

I appreciate anyones help.

Excel 2010
Windows 7

 

[bjurney]

You shouldnt need a macro to do this, a simple vlookup should do the trick

=vlookup(A2,Sheet2!A$1:B$10,2,false)

 

[MrLookout9]

That would find the value, but it would not update the value. In my example, I would like to select true blood on tab 1, enter the number 8, click submit, and have that value updated on tab 2.

thank you for the quick response!

 

[bjurney]

Still think a vlookup would work on this. Just need it in sheet2 and it will update when you update in Sheet1.

Something like this maybe?

Sub update
Dim FoundCell As Range
Dim rng As String
Dim rng2 As String
rng = ActiveCell.Offset(-1, -1)
rng2 = ActiveCell.Offset(-1, 0)
Application.ScreenUpdating = False
Worksheets("Sheet2").Select
With Worksheets("Sheet2").UsedRange
        Set FoundCell = .Find(What:=rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not FoundCell Is Nothing Then
        Application.GoTo FoundCell.Offset(0, 1), False
        ActiveCell.Value = rng2
        Sheets("Sheet1").Select
        Application.ScreenUpdating = True
            Exit Sub
        End If
    End With

End Sub