Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
return a cell address?
I'm trying to loop through column of text for a word (SECTIONS) and
another (GROUPS). Where these are in the column varies although they appear in that order. The two words frame a range that includes from 4 to 15 cells. trycell = c.Value If trycell = "SECTIONS" Then c.Activate rngStart = c.Offset(1, 0) it's part of a for...next loop with a nested if. rngStart is dim'd as a range, but the c.Offset( 1,0) defaults to returning the value in the cell. I need the range from that and the range from the second word to assign it to a variable. I will be using the values from the selected range to create an array. I haven't found anything in 'help' about returning a cell address. Any ideas would be appreciated. Andy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
return a cell address?
c.address would give you the cell address
c.value would give you the value of the cell -- Gary "green fox" wrote in message oups.com... I'm trying to loop through column of text for a word (SECTIONS) and another (GROUPS). Where these are in the column varies although they appear in that order. The two words frame a range that includes from 4 to 15 cells. trycell = c.Value If trycell = "SECTIONS" Then c.Activate rngStart = c.Offset(1, 0) it's part of a for...next loop with a nested if. rngStart is dim'd as a range, but the c.Offset( 1,0) defaults to returning the value in the cell. I need the range from that and the range from the second word to assign it to a variable. I will be using the values from the selected range to create an array. I haven't found anything in 'help' about returning a cell address. Any ideas would be appreciated. Andy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
return a cell address?
Make sure you:
Dim RngStart as Range Then you'll get yelled at when you do this: rngStart = c.Offset(1, 0) you'll want: Set rngStart = c.Offset(1, 0) == If you didn't declare RngStart, then excel saw that variable as a Variant. By not using Set, it _knew_ you wanted to use the value (the default property of that range). green fox wrote: I'm trying to loop through column of text for a word (SECTIONS) and another (GROUPS). Where these are in the column varies although they appear in that order. The two words frame a range that includes from 4 to 15 cells. trycell = c.Value If trycell = "SECTIONS" Then c.Activate rngStart = c.Offset(1, 0) it's part of a for...next loop with a nested if. rngStart is dim'd as a range, but the c.Offset( 1,0) defaults to returning the value in the cell. I need the range from that and the range from the second word to assign it to a variable. I will be using the values from the selected range to create an array. I haven't found anything in 'help' about returning a cell address. Any ideas would be appreciated. Andy -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
return a cell address?
I tried using address, but it didn't appear to return anything...It
made sense to me that that was what address refered to, however I was unsure of what arguments to use after. I'll try again. Andy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
return a cell address?
Thanks Dave,
Even with the changes rngStart returns the value when the condition is met. I've tried using Set rngStart = c.Offset(1,0).Address but I get a type mismatch error. Do you think I would be better off assigning names to the cells, then using those to delineate the range? Andy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
return a cell address?
..Address will return a string.
It really depends on what you want and how you declared rngStart. dim rngStart as range set rngstart = c.offset(1,0) msgbox rngstart.address or dim RngStart as string rngstart = c.offset(1,0).Address How did you declare that rngStart variable? green fox wrote: Thanks Dave, Even with the changes rngStart returns the value when the condition is met. I've tried using Set rngStart = c.Offset(1,0).Address but I get a type mismatch error. Do you think I would be better off assigning names to the cells, then using those to delineate the range? Andy -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
return a cell address?
It depends on what and where those cells are.
Your code looks like you have to determine where the first cell is by checking where "Sections" is. If you knew where Sections was always going to be, then I would think a range name would work ok. But as long as you don't know exactly where it's gonna be, I don't think the range name will work. green fox wrote: Thanks Dave, Even with the changes rngStart returns the value when the condition is met. I've tried using Set rngStart = c.Offset(1,0).Address but I get a type mismatch error. Do you think I would be better off assigning names to the cells, then using those to delineate the range? Andy -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP to return Cell Address | Excel Worksheet Functions | |||
return the Cell address of a value | Excel Worksheet Functions | |||
Return a cell address | Excel Worksheet Functions | |||
Lookup, and Return Cell Address | Excel Worksheet Functions | |||
Return cell address value | Excel Programming |