| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Area Between Two Cells
How ould I reference the cells between two certain cells. The number of
cells between the two cells change all the time. Example.... A1: Quarterbacks A2: BLAH A3: BLAH A4: BLAH A5: BLAH A6: Kickers How would I reference only the cells between Quarterbacks and Kickers. I need it to look for the name quarterbacks in a cell and go through the cells until it reaches the name kickers in a cell. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Area Between Two Cells
Depending how you are defining the start and end ranges:
Dim StartRange As Range Dim EndRange As Range 'Or .Find, or ... Set StartRange = Range("A1") 'Or .Find, or ... Set EndRange = Range("A6") Range(StartRange.Offset(1, 0), EndRange.Offset(-1, 0)).Select NickHK "CribbsStyle" wrote in message s.com... How ould I reference the cells between two certain cells. The number of cells between the two cells change all the time. Example.... A1: Quarterbacks A2: BLAH A3: BLAH A4: BLAH A5: BLAH A6: Kickers How would I reference only the cells between Quarterbacks and Kickers. I need it to look for the name quarterbacks in a cell and go through the cells until it reaches the name kickers in a cell. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Area Between Two Cells
Ok but I need the range to be set be set by the value of the cell, it's
football positions by the way. I'm searching for names of quarterbacks and only want it to search between the cell labeled quarterbacks and the cell labeled kickers. Like I said...the number of cells between the two will change, which is why I need the range based on the text in the cell. On Nov 15, 1:38 am, "NickHK" wrote: Depending how you are defining the start and end ranges: Dim StartRange As Range Dim EndRange As Range 'Or .Find, or ... Set StartRange = Range("A1") 'Or .Find, or ... Set EndRange = Range("A6") Range(StartRange.Offset(1, 0), EndRange.Offset(-1, 0)).Select NickHK "CribbsStyle" wrote in legroups.com... How ould I reference the cells between two certain cells. The number of cells between the two cells change all the time. Example.... A1: Quarterbacks A2: BLAH A3: BLAH A4: BLAH A5: BLAH A6: Kickers How would I reference only the cells between Quarterbacks and Kickers. I need it to look for the name quarterbacks in a cell and go through the cells until it reaches the name kickers in a cell.- Hide quoted text -- Show quoted text - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Area Between Two Cells
Yes, so use
Set StartRange = ActiveSheet.Find("Quarterbacks", , xlValues, xlWhole) 'etc... NickHK "CribbsStyle" wrote in message oups.com... Ok but I need the range to be set be set by the value of the cell, it's football positions by the way. I'm searching for names of quarterbacks and only want it to search between the cell labeled quarterbacks and the cell labeled kickers. Like I said...the number of cells between the two will change, which is why I need the range based on the text in the cell. On Nov 15, 1:38 am, "NickHK" wrote: Depending how you are defining the start and end ranges: Dim StartRange As Range Dim EndRange As Range 'Or .Find, or ... Set StartRange = Range("A1") 'Or .Find, or ... Set EndRange = Range("A6") Range(StartRange.Offset(1, 0), EndRange.Offset(-1, 0)).Select NickHK "CribbsStyle" wrote in legroups.com... How ould I reference the cells between two certain cells. The number of cells between the two cells change all the time. Example.... A1: Quarterbacks A2: BLAH A3: BLAH A4: BLAH A5: BLAH A6: Kickers How would I reference only the cells between Quarterbacks and Kickers. I need it to look for the name quarterbacks in a cell and go through the cells until it reaches the name kickers in a cell.- Hide quoted text -- Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Area Between Two Cells
Ok, tried that and I get this error....
"Object Doesnt Support this property or method" With this line highlighted... Set StartRange = ActiveSheet.Find("Quarterbacks", , xlValues, xlWhole) On Nov 15, 2:07 am, "NickHK" wrote: Yes, so use Set StartRange = ActiveSheet.Find("Quarterbacks", , xlValues, xlWhole) 'etc... NickHK "CribbsStyle" wrote in ooglegroups.com... Ok but I need the range to be set be set by the value of the cell, it's football positions by the way. I'm searching for names of quarterbacks and only want it to search between the cell labeled quarterbacks and the cell labeled kickers. Like I said...the number of cells between the two will change, which is why I need the range based on the text in the cell. On Nov 15, 1:38 am, "NickHK" wrote: Depending how you are defining the start and end ranges: Dim StartRange As Range Dim EndRange As Range 'Or .Find, or ... Set StartRange = Range("A1") 'Or .Find, or ... Set EndRange = Range("A6") Range(StartRange.Offset(1, 0), EndRange.Offset(-1, 0)).Select NickHK "CribbsStyle" wrote oglegroups.com... How ould I reference the cells between two certain cells. The number of cells between the two cells change all the time. Example.... A1: Quarterbacks A2: BLAH A3: BLAH A4: BLAH A5: BLAH A6: Kickers How would I reference only the cells between Quarterbacks and Kickers. I need it to look for the name quarterbacks in a cell and go through the cells until it reaches the name kickers in a cell.- Hide quotedtext -- Show quoted text - - Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Area Between Two Cells
OK, Got it...changed it too...
Set StartRange1 = ActiveSheet.Range("A:A").Find("Quarterbacks", , xlValues, xlWhole) Thanks alot for the help, I really appreciate it! On Nov 15, 2:07 am, "NickHK" wrote: Yes, so use Set StartRange = ActiveSheet.Find("Quarterbacks", , xlValues, xlWhole) 'etc... NickHK "CribbsStyle" wrote in ooglegroups.com... Ok but I need the range to be set be set by the value of the cell, it's football positions by the way. I'm searching for names of quarterbacks and only want it to search between the cell labeled quarterbacks and the cell labeled kickers. Like I said...the number of cells between the two will change, which is why I need the range based on the text in the cell. On Nov 15, 1:38 am, "NickHK" wrote: Depending how you are defining the start and end ranges: Dim StartRange As Range Dim EndRange As Range 'Or .Find, or ... Set StartRange = Range("A1") 'Or .Find, or ... Set EndRange = Range("A6") Range(StartRange.Offset(1, 0), EndRange.Offset(-1, 0)).Select NickHK "CribbsStyle" wrote oglegroups.com... How ould I reference the cells between two certain cells. The number of cells between the two cells change all the time. Example.... A1: Quarterbacks A2: BLAH A3: BLAH A4: BLAH A5: BLAH A6: Kickers How would I reference only the cells between Quarterbacks and Kickers. I need it to look for the name quarterbacks in a cell and go through the cells until it reaches the name kickers in a cell.- Hide quotedtext -- Show quoted text - - Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Area Between Two Cells
Ok now that I got that...how would I use that to search for an
Abbreviated name in the selection and match it with a full name on another sheet and then rename the abbreviated name to the full name. Example... Sheet: Stats Range: A24:A38 Name in Range: John Smith Sheet: HiddenStats Range: Using the code you provided me, the range between quarterbacks and running backs Name in Range: J.Smith Need it to see that J.Smith is actually John Smith and rename J.Smith to John Smith. On Nov 15, 2:25 am, "CribbsStyle" wrote: OK, Got it...changed it too... Set StartRange1 = ActiveSheet.Range("A:A").Find("Quarterbacks", , xlValues, xlWhole) Thanks alot for the help, I really appreciate it! On Nov 15, 2:07 am, "NickHK" wrote: Yes, so use Set StartRange = ActiveSheet.Find("Quarterbacks", , xlValues, xlWhole) 'etc... NickHK "CribbsStyle" wrote in ooglegroups.com... Ok but I need the range to be set be set by the value of the cell, it's football positions by the way. I'm searching for names of quarterbacks and only want it to search between the cell labeled quarterbacks and the cell labeled kickers. Like I said...the number of cells between the two will change, which is why I need the range based on the text in the cell. On Nov 15, 1:38 am, "NickHK" wrote: Depending how you are defining the start and end ranges: Dim StartRange As Range Dim EndRange As Range 'Or .Find, or ... Set StartRange = Range("A1") 'Or .Find, or ... Set EndRange = Range("A6") Range(StartRange.Offset(1, 0), EndRange.Offset(-1, 0)).Select NickHK "CribbsStyle" wrote oglegroups.com... How ould I reference the cells between two certain cells. The number of cells between the two cells change all the time. Example.... A1: Quarterbacks A2: BLAH A3: BLAH A4: BLAH A5: BLAH A6: Kickers How would I reference only the cells between Quarterbacks and Kickers. I need it to look for the name quarterbacks in a cell and go through the cells until it reaches the name kickers in a cell.- Hide quotedtext -- Show quoted text - - Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Area Between Two Cells
Elsewhere in the thread you mentioned named range, try this
Name: Players Refersto: =OFFSET($A:$A,MATCH("Quarterbacks",$A:$A,0),,MATCH ("Kickers",$A:$A,0)-MATCH( "Quarterbacks",$A:$A,0)-1) This should return a reference to players in Col-A no matter which cell you use a formula, eg =Sum(Players) If the formula does not include the absolute addressing it will be relative to the cell selected when you define the refersto. IOW can return a ref to any column relative to the cell used in a formula. Regards, Peter T Regards, Peter T "CribbsStyle" wrote in message s.com... How ould I reference the cells between two certain cells. The number of cells between the two cells change all the time. Example.... A1: Quarterbacks A2: BLAH A3: BLAH A4: BLAH A5: BLAH A6: Kickers How would I reference only the cells between Quarterbacks and Kickers. I need it to look for the name quarterbacks in a cell and go through the cells until it reaches the name kickers in a cell. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Area Between Two Cells
OK, I dont know..I'm confused, here lets try this...
Sheet: Stats A24:John Smith A25:John Harper Sheet: HiddenStats A20:Quarterbacks A21:Name A22:J. Smith A23:J. Harper A24:Key: GP=Games Played, GS=Games Started, ATT=Pass Attempts, CP=Completions, CP%=Completion %, YDS=Passing Yards, TD=Passing Touchdowns A25: A26:Running Backs A27:Name A28:J. Smith A29:A. Norris See how there is a J.Smith under Quarterbacks and Running Backs, one is John Smith the other is Jason Smith. I use the following line of code on sheet STATS to pull the stats of the players which are to the right of the names from HiddenStats to Stats... =IF(ISERROR(INDEX(HiddenStats!$A$20:$N$150,MATCH(L EFT(A24)&". "&MID(A24,FIND(" ",A24)+1,255),HiddenStats!$A$20:$A$150,FALSE),2)), "",INDEX(HiddenStats!$A$20:$N$150,MATCH(LEFT(A24)& ". "&MID(A24,FIND(" ",A24)+1,255),HiddenStats!$A$20:$A$150,FALSE), 2)) That makes it where it matches John or Jason Smith to J. Smith. This causes a problem. I need it to only look for John Smith under quarterbacks. The stats in HiddenStats are pulled from a webquery, some teams might have more quarterbacks, which in turn would mean more cells under quarterbacks. How can I have it where it only looks for matches below the word Quarterbacks and Above the word Running Backs. If there is an easier way to do this, I would love to hear it. On Nov 15, 6:58 am, "Peter T" <peter_t@discussions wrote: Elsewhere in the thread you mentioned named range, try this Name: Players Refersto: =OFFSET($A:$A,MATCH("Quarterbacks",$A:$A,0),,MATCH ("Kickers",$A:$A,0)-MATCH*( "Quarterbacks",$A:$A,0)-1) This should return a reference to players in Col-A no matter which cell you use a formula, eg =Sum(Players) If the formula does not include the absolute addressing it will be relative to the cell selected when you define the refersto. IOW can return a ref to any column relative to the cell used in a formula. Regards, Peter T Regards, Peter T "CribbsStyle" wrote in legroups.com... How ould I reference the cells between two certain cells. The number of cells between the two cells change all the time. Example.... A1: Quarterbacks A2: BLAH A3: BLAH A4: BLAH A5: BLAH A6: Kickers How would I reference only the cells between Quarterbacks and Kickers. I need it to look for the name quarterbacks in a cell and go through the cells until it reaches the name kickers in a cell.- Hide quoted text -- Show quoted text - |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Area Between Two Cells
Start by simplifying things, eg remove the Is iserror etc
Define the Name exactly as I posted Put "Quarterbacks" somewhere in col-a and "Kickers" some cells below. Fill in all the cells between, ie no empty cells for the next test =COUNTA(Players) This should return the count of the 'filled in' cells; if not the named formula is not correctly entered or spellings not correct. Enter "J. Smith" in one of the between cells =MATCH("J. Smith",Players) This should return the number of rows below Quarterbacks. Now to return the actual row =ROW(Players)+MATCH("J. Smith",Players,0)-1 Try substituting "Players" with the name's formula, without all the sheet names that automatically get added (unless you're on another sheet I would suggest instead of trying to create a massive formula in one go, split into 3 or 4 cells and check each one. If and when all working combine them if you really need to. I defined the Offset as a single column in Col-A. This could of course be any number of columns, the first of which might be off to the right of Col-A. If you are need to return the contents cell to the right of "J. Smith" instead of your Match try VLookup. Regards, Peter T "CribbsStyle" wrote in message ups.com... OK, I dont know..I'm confused, here lets try this... Sheet: Stats A24:John Smith A25:John Harper Sheet: HiddenStats A20:Quarterbacks A21:Name A22:J. Smith A23:J. Harper A24:Key: GP=Games Played, GS=Games Started, ATT=Pass Attempts, CP=Completions, CP%=Completion %, YDS=Passing Yards, TD=Passing Touchdowns A25: A26:Running Backs A27:Name A28:J. Smith A29:A. Norris See how there is a J.Smith under Quarterbacks and Running Backs, one is John Smith the other is Jason Smith. I use the following line of code on sheet STATS to pull the stats of the players which are to the right of the names from HiddenStats to Stats... =IF(ISERROR(INDEX(HiddenStats!$A$20:$N$150,MATCH(L EFT(A24)&". "&MID(A24,FIND(" ",A24)+1,255),HiddenStats!$A$20:$A$150,FALSE),2)), "",INDEX(HiddenStats!$A$20 :$N$150,MATCH(LEFT(A24)&". "&MID(A24,FIND(" ",A24)+1,255),HiddenStats!$A$20:$A$150,FALSE), 2)) That makes it where it matches John or Jason Smith to J. Smith. This causes a problem. I need it to only look for John Smith under quarterbacks. The stats in HiddenStats are pulled from a webquery, some teams might have more quarterbacks, which in turn would mean more cells under quarterbacks. How can I have it where it only looks for matches below the word Quarterbacks and Above the word Running Backs. If there is an easier way to do this, I would love to hear it. On Nov 15, 6:58 am, "Peter T" <peter_t@discussions wrote: Elsewhere in the thread you mentioned named range, try this Name: Players Refersto: =OFFSET($A:$A,MATCH("Quarterbacks",$A:$A,0),,MATCH ("Kickers",$A:$A,0)-MATCH* ( "Quarterbacks",$A:$A,0)-1) This should return a reference to players in Col-A no matter which cell you use a formula, eg =Sum(Players) If the formula does not include the absolute addressing it will be relative to the cell selected when you define the refersto. IOW can return a ref to any column relative to the cell used in a formula. Regards, Peter T Regards, Peter T "CribbsStyle" wrote in legroups.com... How ould I reference the cells between two certain cells. The number of cells between the two cells change all the time. Example.... A1: Quarterbacks A2: BLAH A3: BLAH A4: BLAH A5: BLAH A6: Kickers How would I reference only the cells between Quarterbacks and Kickers. I need it to look for the name quarterbacks in a cell and go through the cells until it reaches the name kickers in a cell.- Hide quoted text -- Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Set print area over locked cells | Excel Worksheet Functions | |||
| DIVIDE BY A CELL TO AN AREA OF CELLS | Excel Worksheet Functions | |||
| Area charts and blank cells | Charts and Charting in Excel | |||
| Sorting Area with Merged Cells | New Users to Excel | |||
| Select Cells in Print Area | Excel Programming | |||
Hybrid Mode
