How do I identify a range based on cell value?
 

I have data sorted by a column. I want to loop down thru the column
until the value = "A". I want to name that cell somthing like "start
of A". Then I want to loop through the column until the last value =
"A", and name that "end of "A". Then I want to select the range "start
of A:end of A"

Thanks for your help


--
HBF
------------------------------------------------------------------------
HBF's Profile: http://www.excelforum.com/member.php...o&userid=34019
View this thread: http://www.excelforum.com/showthread...hreadid=552410

How do I identify a range based on cell value?
 

Try

Sub AAA()
Dim RowNdx As Long
Dim StartFound As Boolean
For RowNdx = 1 To Rows.Count
If StrComp(Cells(RowNdx, 1).Value, "A", vbTextCompare) = 0
Then
If StartFound = False Then
Cells(RowNdx, 1).Name = "StartOfA"
StartFound = True
Else
Cells(RowNdx, 1).Name = "EndOfA"
End If
End If
Next RowNdx
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"HBF" wrote in
message ...

I have data sorted by a column. I want to loop down thru the
column
until the value = "A". I want to name that cell somthing like
"start
of A". Then I want to loop through the column until the last
value =
"A", and name that "end of "A". Then I want to select the
range "start
of A:end of A"

Thanks for your help


--
HBF
------------------------------------------------------------------------
HBF's Profile:
http://www.excelforum.com/member.php...o&userid=34019
View this thread:
http://www.excelforum.com/showthread...hreadid=552410

How do I identify a range based on cell value?
 

Here is something directly from the help files.

Use the Add method to create a name and add it to the collection.The
following example creates a new name that refers to cells A1:C20 on the
worksheet named "Sheet1."

Names.Add Name:="test", RefersTo:="=sheet1!$a$1:$c$20"

I hope that is what you need.


--
jdawson
------------------------------------------------------------------------
jdawson's Profile: http://www.excelforum.com/member.php...o&userid=32198
View this thread: http://www.excelforum.com/showthread...hreadid=552410

How do I identify a range based on cell value?
 

Private Sub CommandButton1_Click()
ActiveSheet.Range("A1").Select

With Cells
For Each cell In Columns

If ActiveCell.Offset(0, 0) = "A" Then
ActiveCell.Offset(0, 0) = "Start of A"
ActiveSheet.Range("A1").Activate
Set rng = .Range(.Cells(1, 1), .Cells(1, 1)).End(xlDown)
rng.Select
Exit For

Else
ActiveCell.Offset(1, 0).Activate
End If
Next
For Each cell In Columns
If ActiveCell.Offset(0, 0) = "A" Then
ActiveCell.Offset(0, 0) = "End of A"
Columns("A").Select
Selection.AutoFilter Field:=1, Criteria1:="=*of A*", Operator:=xlOr, _
Criteria2:="=A"
Exit Sub
Else
ActiveCell.Offset(-1, 0).Activate
End If
Next
End With
End Sub

Jim

"HBF" wrote:


I have data sorted by a column. I want to loop down thru the column
until the value = "A". I want to name that cell somthing like "start
of A". Then I want to loop through the column until the last value =
"A", and name that "end of "A". Then I want to select the range "start
of A:end of A"

Thanks for your help


--
HBF
------------------------------------------------------------------------
HBF's Profile: http://www.excelforum.com/member.php...o&userid=34019
View this thread: http://www.excelforum.com/showthread...hreadid=552410

How do I identify a range based on cell value?
 

maybe something simple like this....

if "A" is not in column 5 (ie. E), then change to fit.

Sub FindA()

Dim Afirst As Range, Alast As Range, Aall As Range

Set Afirst = Columns(5).Find(What:="A", LookIn:=xlValues,
LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Set Alast = Columns(5).Find(What:="A", LookIn:=xlValues,
LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:=False)
Set Aall = Range(Afirst, Alast)
Aall.Select

End Sub


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=552410

How do I identify a range based on cell value?
 

one thing I forgot to mention, my suggestion only works if your data is
sorted so that all "A's" are together....


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=552410

How do I identify a range based on cell value?
 

I tried your routine but got an error at "Aall.Select".

Jim

"Celt" wrote:


maybe something simple like this....

if "A" is not in column 5 (ie. E), then change to fit.

Sub FindA()

Dim Afirst As Range, Alast As Range, Aall As Range

Set Afirst = Columns(5).Find(What:="A", LookIn:=xlValues,
LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Set Alast = Columns(5).Find(What:="A", LookIn:=xlValues,
LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
MatchCase:=False)
Set Aall = Range(Afirst, Alast)
Aall.Select

End Sub


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=552410

How do I identify a range based on cell value?
 

Jim,

I realized as soon I as poted my suggestion that there was a problem,
so I edited it (not sure if my changes get sent to people following
this thread).

in the routine I suggested, make the following changes....

LookAt:=xlPart change to LookAt:=xlWhole &
MatchCase:=False change to MatchCase:=True

Do that for both Afirst and Alast.

As I said before, this routine assumes you rdata is sorted, (all the
"A's" are together ) and that worksheet containing the data is the
active worksheet.

Don't forget to adjust the column number if needed.

I ran this and it works for me perfectly (after my changes...sorry
about that).


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=552410

How do I identify a range based on cell value?
 

No problem Celt - thanks for your help. It works!!


--
HBF
------------------------------------------------------------------------
HBF's Profile: http://www.excelforum.com/member.php...o&userid=34019
View this thread: http://www.excelforum.com/showthread...hreadid=552410