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

"CribbsStyle" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

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" <TungChe...@Invalid.com> 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" <cribbsst...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
>
>
> > 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 -

Yes, so use
Set StartRange = ActiveSheet.Find("Quarterbacks", , xlValues, xlWhole)
'etc...

NickHK

"CribbsStyle" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <TungChe...@Invalid.com> 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" <cribbsst...@gmail.com> wrote in
messagenews:(E-Mail Removed)...
> >
> >
> >
> > > 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 -
>

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" <TungChe...@Invalid.com> wrote:
> Yes, so use
> Set StartRange = ActiveSheet.Find("Quarterbacks", , xlValues, xlWhole)
> 'etc...
>
> NickHK
>
> "CribbsStyle" <cribbsst...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
>
>
> > 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" <TungChe...@Invalid.com> 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" <cribbsst...@gmail.com> wrote inmessagenews:(E-Mail Removed)...
>
>
>
>
>
> > > > 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 -

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" <TungChe...@Invalid.com> wrote:
> Yes, so use
> Set StartRange = ActiveSheet.Find("Quarterbacks", , xlValues, xlWhole)
> 'etc...
>
> NickHK
>
> "CribbsStyle" <cribbsst...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
>
>
> > 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" <TungChe...@Invalid.com> 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" <cribbsst...@gmail.com> wrote inmessagenews:(E-Mail Removed)...
>
>
>
>
>
> > > > 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 -

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" <cribbsst...@gmail.com> 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" <TungChe...@Invalid.com> wrote:
>
>
>
> > Yes, so use
> > Set StartRange = ActiveSheet.Find("Quarterbacks", , xlValues, xlWhole)
> > 'etc...
>
> > NickHK
>
> > "CribbsStyle" <cribbsst...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
> > > 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" <TungChe...@Invalid.com> 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" <cribbsst...@gmail.com> wrote inmessagenews:(E-Mail Removed)...
>
> > > > > 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 -

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <cribbsst...@gmail.com> 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" <TungChe...@Invalid.com> wrote:
> >
> >
> >
> > > Yes, so use
> > > Set StartRange = ActiveSheet.Find("Quarterbacks", , xlValues, xlWhole)
> > > 'etc...
> >
> > > NickHK
> >
> > > "CribbsStyle" <cribbsst...@gmail.com> wrote in
messagenews:(E-Mail Removed)...
> >
> > > > 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" <TungChe...@Invalid.com> 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" <cribbsst...@gmail.com> wrote
inmessagenews:(E-Mail Removed)...
> >
> > > > > > 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 -
>

How about this, this would be easier....is there a way to make the
selection a named range?

On Nov 15, 2:53 am, "NickHK" <TungChe...@Invalid.com> wrote:
> 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" <cribbsst...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
>
>
> > 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" <cribbsst...@gmail.com> 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" <TungChe...@Invalid.com> wrote:
>
> > > > Yes, so use
> > > > Set StartRange = ActiveSheet.Find("Quarterbacks", , xlValues, xlWhole)
> > > > 'etc...
>
> > > > NickHK
>
> > > > "CribbsStyle" <cribbsst...@gmail.com> wrote inmessagenews:(E-Mail Removed)...
>
>
>
>
>
> > > > > 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" <TungChe...@Invalid.com> 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" <cribbsst...@gmail.com> wroteinmessagenews:(E-Mail Removed)...
>
>
>
>
>
> > > > > > > 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.- Hidequotedtext -- Show quoted text -
>
> > > > - Hide quoted text -- Show quoted text -- Hide quoted text -- Showquoted 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 -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

You can use ActiveCell or ActiveCell(1), if multiple cells are selected

NickHK

"CribbsStyle" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> How about this, this would be easier....is there a way to make the
> selection a named range?
>
> On Nov 15, 2:53 am, "NickHK" <TungChe...@Invalid.com> wrote:
> > 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" <cribbsst...@gmail.com> wrote in
messagenews:(E-Mail Removed)...
> >
> >
> >
> > > 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" <cribbsst...@gmail.com> 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" <TungChe...@Invalid.com> wrote:
> >
> > > > > Yes, so use
> > > > > Set StartRange = ActiveSheet.Find("Quarterbacks", , xlValues,
xlWhole)
> > > > > 'etc...
> >
> > > > > NickHK
> >
> > > > > "CribbsStyle" <cribbsst...@gmail.com> wrote
inmessagenews:(E-Mail Removed)...
> >
> >
> >
> >
> >
> > > > > > 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" <TungChe...@Invalid.com> 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" <cribbsst...@gmail.com>
wroteinmessagenews:(E-Mail Removed)...
> >
> >
> >
> >
> >
> > > > > > > > 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.-
Hidequotedtext -- Show quoted text -
> >
> > > > > - Hide quoted text -- Show quoted text -- Hide quoted text --
Showquoted 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 -- Hide quoted text -- Show quoted text -- Hide quoted text --
Show quoted text -
>