Help : Code for selection non-contiguous cells

[Majeed]

I have a workbook containg results of a soccer team reults. The main
workseeht ("games) includes the main data. The data includes columns with
the name of goal scorer and 9 columns offset to the right it the number of
goals that the player scored. I am trying to write a code to do the
following:

a- Use an InputBox to search for a particular player. ( no Problems there )
b- Write a code to search for the player by looping through each cell ( no
Problems there )
c- Copy the first 4 cells from that row , where player is found ( not the
EntireRow) ( problems here)
d- further select the cell that contains the player name and an offset cell
value of 5 to the right containg number of goals scored ( serious problems
here)
e- Open a seperate worksheet named after a player. ( not attempted yet).
f- Past the selection from c and d above , then going to the next empty row
for the next paste commend (problems here)

the following is a sample of games worksheet

A B C D E F G
H I J K L M
DATE TEAM OPP, GF GA SC1 SC2 SC3 SC4
S1 S2 S3 S4
12-12-98 England Argentina 2 1 Beckham Owens - -
1 1 0 0
10-01-99 England Germany 5 2 Owens Beckham Reid Jones
2 1 1 1
23-09-99 England Malta 8 0 Jones Owens Philips
Reid 1 4 2 1


Say I am looking for player Owens record.

Thanks

 

[Bernie Deitrick]

Majeed,

See my comments in-line with your original post.

HTH,
Bernie
MS Excel MVP

I have a workbook containg results of a soccer team reults. The main
workseeht ("games) includes the main data. The data includes columns with
the name of goal scorer and 9 columns offset to the right it the number of
goals that the player scored. I am trying to write a code to do the
following:

a- Use an InputBox to search for a particular player. ( no Problems there )
b- Write a code to search for the player by looping through each cell ( no
Problems there )

What you do when you find the cell impacts the further use of that
cell. the prefered method is along the lines of this, where it finds
a player's name that is stored in the variable PlayerName somewhere in
columns F to I of the activesheet:

Dim myCell As Range
Dim PlayerName As String
PlayerName = "Owen"
Set myCell = Range("F:I").Find(PlayerName)

c- Copy the first 4 cells from that row , where player is found not the
EntireRow) ( problems here)
d- further select the cell that contains the player name and an offset cell
value of 5 to the right containg number of goals scored ( serious problems
here)
e- Open a seperate worksheet named after a player. ( not attempted

yet).

For C and D & E:
Dim oldBook As Workbook
Dim newBook As Workbook

Set Oldbook = Activeworkbook
Set newBook = Workbooks.Open("C:\Folder\" & PlayerName & ".xls")
OldBook.Activate
Range(Cells(myCell.Row, 1).Resize(1, 4).Address & _
", " & myCell.Address & ", " & _
myCell(1, 6).Address).Copy
NewBook.Activate
'Assuming you want to paste starting the first column:
Range("A65536").End(xlUp)(2).Select
Activesheet.Paste