Question on determining "ROW" inside of a "For .. RANGE " loop
 

I think you will find (pun intended) what you want by look in vba help for
FINDNEXT. There is a good example. You find the row and subtract from row 1.

--
Don Guillett
SalesAid Software

"David Schrader" wrote in message
...
I'll be the first to admit that I'm not the most sophisticated of
Excel macro writers. I've written some macros, some better
than others and some demanding some fairly advanced skill.
But I've run up against something that's got me running in
a loop trying to find an answer which I haven't been able
to, as yet, find. Thus my appeal here.

I have a sheet which may be any length from 50 to1900
some entries. I have a routine which will find the last
"employ" row and place an "flag" in $A$1 so that all of my
future routines can test for it (see the example below).
I want to create three RANGEs, by name, and need to
be able to identify the number of ROWs between $A$2
and the cell $A$n which holds the flag (EOList) -1. So
far all of my efforts to code such a beast have failed.

Any suggestions? (Here's my general structure.)

David


For Each Cell_in_loop In Range("A2:A2000")
If Cell_in_loop.Offset(0, 0).Value = "EOList" Then
Exit For
Else
' Error msg = "Object required...'
' Looper = System.Row(Cell_in_loop.Offset(0, 0))
End If
Next
' With the cell value stored in *Looper* we can now
' create the ranger
' ThisWorkbook.Names.Add Name:="SortName1", _
' RefersTo:=("=$B$2$B$" & CStr(Looper) _
' & Chr(34)), Visible:=True

Question on determining "ROW" inside of a "For .. RANGE " loop
 

I'll be the first to admit that I'm not the most sophisticated of
Excel macro writers. I've written some macros, some better
than others and some demanding some fairly advanced skill.
But I've run up against something that's got me running in
a loop trying to find an answer which I haven't been able
to, as yet, find. Thus my appeal here.

I have a sheet which may be any length from 50 to1900
some entries. I have a routine which will find the last
"employ" row and place an "flag" in $A$1 so that all of my
future routines can test for it (see the example below).
I want to create three RANGEs, by name, and need to
be able to identify the number of ROWs between $A$2
and the cell $A$n which holds the flag (EOList) -1. So
far all of my efforts to code such a beast have failed.

Any suggestions? (Here's my general structure.)

David


For Each Cell_in_loop In Range("A2:A2000")
If Cell_in_loop.Offset(0, 0).Value = "EOList" Then
Exit For
Else
' Error msg = "Object required...'
' Looper = System.Row(Cell_in_loop.Offset(0, 0))
End If
Next
' With the cell value stored in *Looper* we can now
' create the ranger
' ThisWorkbook.Names.Add Name:="SortName1", _
' RefersTo:=("=$B$2$B$" & CStr(Looper) _
' & Chr(34)), Visible:=True

Question on determining "ROW" inside of a "For .. RANGE " loop
 

Thank you Don. I'll go look there immediately.

David

(And I love puns.)

"Don Guillett" wrote in message
...
I think you will find (pun intended) what you want by look in vba help for
FINDNEXT. There is a good example. You find the row and subtract from row
1.

--
Don Guillett
SalesAid Software

"David Schrader" wrote in message
...
I'll be the first to admit that I'm not the most sophisticated of
Excel macro writers. I've written some macros, some better
than others and some demanding some fairly advanced skill.
But I've run up against something that's got me running in
a loop trying to find an answer which I haven't been able
to, as yet, find. Thus my appeal here.

I have a sheet which may be any length from 50 to1900
some entries. I have a routine which will find the last
"employ" row and place an "flag" in $A$1 so that all of my
future routines can test for it (see the example below).
I want to create three RANGEs, by name, and need to
be able to identify the number of ROWs between $A$2
and the cell $A$n which holds the flag (EOList) -1. So
far all of my efforts to code such a beast have failed.

Any suggestions? (Here's my general structure.)

David


For Each Cell_in_loop In Range("A2:A2000")
If Cell_in_loop.Offset(0, 0).Value = "EOList" Then
Exit For
Else
' Error msg = "Object required...'
' Looper = System.Row(Cell_in_loop.Offset(0, 0))
End If
Next
' With the cell value stored in *Looper* we can now
' create the ranger
' ThisWorkbook.Names.Add Name:="SortName1", _
' RefersTo:=("=$B$2$B$" & CStr(Looper) _
' & Chr(34)), Visible:=True