Working with moving dynamic ranges
 

I'm dealing with an excel file that's output from some software without any
regard for working with it; it's more laid our for printing. I've alreay
done quite a bit in terms of stripping out weird characters, unmerging
cells, and basically arranging it so at least things are in the same columns
more or less. My issue is that I need to perform all kind of operations on
the data, making a new sheet based on this sheet's data and various
vlookups/operations.

It seems to me the only way I can do this is look for a partiicular value,
which indicates that below it is a set of data I need, then I need to go
lower and find the same indicating value, do it again, etc.

So I have something like this:

4 or 5 blank rows, then B6 has "Mark1", then a couple blank rows, then (and
I'm showing Exel's column headers here)

A B C D
E
Pink 48 Ranking8
77
RadC 23 Decim3
14

Then some blank rows, then he whole thing starts again (only B would then be
"Mark2")

A complication is that there can be any number of data rows (although when
there IS data, it's always together, like it won't have blanks in A or in
the last column, at least; so I should be able to use the existence of data
at the borders of my Range), and there could be different numbers of columns
with data.

So it seems like what I need to do is find the value in B, then set a range
that starts at the first value in Column A underneath the value in B, find
the last used cell to the right and down, and set the END of the range
there. Then I can perform my operations (which all rely on this being the
first set of data), set a counter variable or something so I know what set
I'm on, add one to it, look for the NEXT value in B underneath the range I
just set, and do it all again, until there's no longer a "Mark[#]" value in
B.

Working with moving dynamic ranges
 

On Oct 8, 4:30*pm, "CompleteNewb" wrote:
I'm dealing with an excel file that's output from some software without any
regard for working with it; it's more laid our for printing. *I've alreay
done quite a bit in terms of stripping out weird characters, unmerging
cells, and basically arranging it so at least things are in the same columns
more or less. *My issue is that I need to perform all kind of operations on
the data, making a new sheet based on this sheet's data and various
vlookups/operations.

It seems to me the only way I can do this is look for a partiicular value,
which indicates that below it is a set of data I need, then I need to go
lower and find the same indicating value, do it again, etc.

So I have something like this:

4 or 5 blank rows, then B6 has "Mark1", then a couple blank rows, then (and
I'm showing Exel's column headers here)

A * * * * * * * * * *B * * * * * * * * * *C * * * * * * * * * *D
E
Pink * * * * * * 48 * * * * * * * * * * * * * * * * * * * * * * Ranking8
77
RadC * * * * * 23 * * * * * * * * * * * * * * * * * * * * * * Decim3
14

Then some blank rows, then he whole thing starts again (only B would then be
"Mark2")

A complication is that there can be any number of data rows (although when
there IS data, it's always together, like it won't have blanks in A or in
the last column, at least; so I should be able to use the existence of data
at the borders of my Range), and there could be different numbers of columns
with data.

So it seems like what I need to do is find the value in B, then set a range
that starts at the first value in Column A underneath the value in B, find
the last used cell to the right and down, and set the END of the range
there. *Then I can perform my operations (which all rely on this being the
first set of data), set a counter variable or something so I know what set
I'm on, add one to it, look for the NEXT value in B underneath the range I
just set, and do it all again, until there's no longer a "Mark[#]" value in
B.

Can anyone help me figure out how to do this? *I've tried getting through
the looping procedure, but I seem to be having an issue dealing with setting
ranges and using Vlookups on the values in it once it's set, then emptying
it and navigating and setting it again.

Your help is greatly appreciated, and thanks for reading.

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."