Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Validation question - allow -500%, 0%, "=50%*50%" but forbid "A", "", " ", "-" ? | Excel Programming | |||
Listbox header inside VBA (Array("Head1", "Head2", ...) | Excel Programming | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming |