Match and copy

[BuddyB]

Trying to do this in VBA so I can use a button to do all at once.

Example: I have a sheet named payouts. Another named results.

What I would like to do is select a cell in the range AB2:ab100 on payout sheet (this range is all text cells)and check one at a time (ie ab2, then ab3, etc)

I would then like to search on results sheet and find the match to the cell on payout sheet. Once that match ( there will always be a match) is found then offset by 2 rows, copy that information, then past that information (number) back to the payout sheet.

Thanks

Buddy

[MSP77079]

Try this:

Sub PayoutResults()
Dim myRange As Range, c As Range, rWS As Worksheet
Dim searchRange As Range, matchCell As Range, pWS As Worksheet

Set pWS = Sheets("payout")
Set myRange = pWS.Range("AB2:AB100")
Set rWS = Sheets("results")
Set searchRange = rWS.Cells

For Each c In myRange.Cells
myVal = c.Value
Set matchCell = Nothing
On Error Resume Next
Set matchCell = searchRange.Find(What:=myVal)
If Not matchCell Is Nothing Then
matchRow = matchCell.Row
matchCol = matchCell.Column
myRow = c.Row
myCol = c.Column
pWS.Cells(myRow, myCol + 1) = rWS.Cells(matchRow, matchCol + 2)
End If
Next c

End Sub

[BuddyB]

Worked like a champ....thanks very much for the help.


I've posted here a couple of times before and never got a reply. You've restored my hope on this site.

[MSP77079]

You are most welcome. I usually hang out in the Excel programming group, since my thing is Visual Basic. I only popped in over here because things seem slow this evening.

- Pete