Area Between Two Cells

  • Thread starter Thread starter CribbsStyle
  • Start date Start date
C

CribbsStyle

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.
 
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
 
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.
 
Yes, so use
Set StartRange = ActiveSheet.Find("Quarterbacks", , xlValues, xlWhole)
'etc...

NickHK
 
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)
 
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!
 
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.
 
OK, sorry missed the Range, but seems have found it with some research. The
Help and Object Browser are good tools.

Depending where "J. Smith" is coming from, drop the "." for the initial.
Or
Dim SearchName As String
'C2, or where ever you search name is coming from
SearchName = Replace(Range("C2").Value, ". ", "*")

Set StartRange = Worksheets(1).Range("A:A").Find(SearchName, , xlValues,
xlPart)

Note the change from "xlWhole" to "xlPart"

NickHK

CribbsStyle said:
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.
Click to expand...
quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text --
Show quoted text -
 
How about this, this would be easier....is there a way to make the
selection a named range?
 
You can use ActiveCell or ActiveCell(1), if multiple cells are selected

NickHK

CribbsStyle said:
How about this, this would be easier....is there a way to make the
selection a named range?
Click to expand...
Showquoted text -- Hide quoted text -- Show quoted text -- Hide quoted
text --quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text --
Show quoted text -
 
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
 
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(LEFT(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.
 
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


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(LEFT(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.

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
Click to expand...
text -- Show quoted text -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to delete ALL cells containing the same data 1
Macro that delets empty cells between cells with text 2
macro which delets cell if the cell above has the same keyword 2
I want to highlight multiple cells using VB 3
Comparing a vba variable to a range of cells 5
REARRANGEMENT OF CELL VALUES !! 4
count names 10
sorting multiple-area selection 5

Back
Top