To Define Name Instead of Range

[Mathew P Bennett]

Good Evening All,

I am a struggling newcomer to vba, and would like some help with the code below.

Columns("A:A").Find(What:="", After:=Range("A500"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Activate

My problem being, that instead of looking for the first blank cell after cell A500, I would like to be
able to name A500 eg "Prime" (as it appears in cell A500 in the worksheet), and hence the code
should accommodate the insertion of rows above 500).

I feel is if I have not explained this well, but any assistance most appreciated as usual.
Cheers,
Mathew

 

[Chip Pearson]

Mathew,

If you have named cell A500 "Prime", just change that reference
in your Find code. For example,

Columns("A:A").Find(What:="", After:=Range("Prime"),
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate


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

Good Evening All,

I am a struggling newcomer to vba, and would like some help with the code below.

Columns("A:A").Find(What:="", After:=Range("A500"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Activate

My problem being, that instead of looking for the first blank

cell after cell A500, I would like to be

able to name A500 eg "Prime" (as it appears in cell A500 in the worksheet), and hence the code
should accommodate the insertion of rows above 500).

I feel is if I have not explained this well, but any assistance most appreciated as usual.
Cheers,
Mathew

21/11/2003

 

[Mathew P Bennett]

Thank you Mr Pearson,
You pointed me in the right direction. Of course, I had to 'define', 'name'/'label' etc. my cells.
Works now,
Cheers,
Mathew
Mathew,

If you have named cell A500 "Prime", just change that reference
in your Find code. For example,

Columns("A:A").Find(What:="", After:=Range("Prime"),
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate


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

Good Evening All,

I am a struggling newcomer to vba, and would like some help with the code below.

Columns("A:A").Find(What:="", After:=Range("A500"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Activate

My problem being, that instead of looking for the first blank

cell after cell A500, I would like to be

able to name A500 eg "Prime" (as it appears in cell A500 in the worksheet), and hence the code
should accommodate the insertion of rows above 500).

I feel is if I have not explained this well, but any assistance most appreciated as usual.
Cheers,
Mathew

21/11/2003