Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help : Code for selection non-contiguous cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help : Code for selection non-contiguous cells
Majeed,
See my comments in-line with your original post. HTH, Bernie MS Excel MVP "Majeed" wrote in message ... 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 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 - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Non-contiguous selection | Excel Discussion (Misc queries) | |||
Non-contiguous cells | Excel Discussion (Misc queries) | |||
counting cells that are 0 in a range of non-contiguous cells | Excel Worksheet Functions | |||
How do I count non-contiguous cells? | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions |