Copying and searching cell contents in VB (Excel Macro)

[sjc05]

Hey All,

I have the following problem: I am trying to copy a cell in sheet one, switch to sheet two, and search for the value of the cell i just copied. (ie. I have two spreadsheets of different data for company personnel, in spreadsheet A I want to copy the persons social security number, switch to sheet B, paste the social into the search box, when it finds it, i want to copy the cell next to it, switch back to sheet A and paste it there. I need to do this for every person on sheet A.) The search is the only part giving me trouble, how do i search for a changing variable?

This is my code thus far:

For x = 1 To 500

ActiveCell.Offset(0, 1).Select
y = Selection.Copy

Sheets("Sheet2").Select

Cells.Find(What:="y", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate

ActiveCell.Offset(0, -1).Select
Selection.Copy

Sheets("Sheet1").Select

ActiveCell.Offset(0, -1).Select
ActiveSheet.Paste

ActiveCell.Offset(1, 0).Select

Next x

End Sub


Thanks in advance,

Scott

 

[SkipVought]

Scott,

As a programmer, you ought to know the difference between a FUNCTIONAL requirement and a DESIGN requirement.

Let me see if I can define the functional requirement.

For a list of SSN's on Sheet A, find the ????? on sheet B using the SSN and return that value to Sheet A in the adjacent column.

This COULD be done with a VLOOKUP spreadsheet function. However, using VBA, here's how it can be done...

sub Assign?????Data
  dim r as range, rng as range
  with sheets("Sheet1")
    For each r in .Range(.[A1], .[A1]End(xldown))
       set rng = Sheets("Sheet2").cells.find(r.value)
       if not rng is nothing then
          .offset(0,1).value = rng.offset(0,1).value
       end if
    Next
  end with
End Sub

Skip,

_[red][/red]