intersecting ranges 1

[andrew299]

Hello Everyone
I am trying to use intersect but cant get the code right.

What I have is to rows of data from a1 to m2
row 1 acts as a header row to as the data

I have inherited a program which uses intersect to pick up the information required in the range c1 to m3 using the following code

ValuesFound = Intersect(.Offset(0, 0), .Offset(0, iColLocation))

x = ubound(valuesfound,2)
for g = 1 to x
y1(g) = valuesfound(1,g)
y2(g) = valuesfound(2,g)
next x

usually there is two rows of data and this works fine but occasionally there are three rows of data ( header + two rows of data) . So what I did whas write a small piece of code that averages the two rows of data to the fourth row and deletes the other two rows. This puts it back to the original layout


a a a a a a a a a a a a a (header)
d d 1 1 1 1 1 1 1 1 1 1 1 data
d d 3 3 3 3 3 3 3 3 3 3 3 data

turns into

a a a a a a a a a a a a a (how the data should look)
d d 2 2 2 2 2 2 2 2 2 2 2

The code works fine for the if the data starts off like the second case but as soon as I do my average delete macro the intersect code only picks up one row (a a a a ... ) in the valuesfound and not the second row (2 2 2 2 ...). Which crashes the code later on. What is happening here?
Is there a way of saying

if average code has been run
set range1= row1
set range2=row2
valuesfound= intersect(range1,range2)


if not then
valuesfound= (original method)

Hope this makes sense.
Thanks for any help

Andrew299











It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)

 

[SkipVought]

Andrew299,

I can't make any sense out of your post.

Intersect is a Range function. The syntax is...

Set TestRange = Application.Intersect(Range1, Range2, Range3...)
If TestRange is Nothing Then
  'there is no intersection
Else
  'TestRange is the INTERSECTING range.
End If

Your FIRST example (ValuesFound = Intersect(.Offset(0, 0), .Offset(0, iColLocation)))makes no sense, BOTH in syntax AND by virtue of the fact that .Offset returns a SINGLE CELL RANGE. If the syntax were correct, you would NEVER get an intersection!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi andrew299,

The shape of the ranges returned by .Offset is the same as the shape of the 'original' Range. As both offsets in the Intersect are by zero rows and a number of columns, the Intersection will be a number of columns spread over the number of Rows in the original selection (or whatever range you're working with). As you don't show the "With" in your post I can't tell but I guess you're somehow changing the base Range by deleting rows - you could try to Resize the Range before doing the Intersect - or post a bit more code and someone will help you a bit further.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[SkipVought]

Ah, yes, he did have reference to some other RANGE. A bit more code would be helpful.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[andrew299]

Thanks for the help.

I have been back to the code and there is a with statement

With shtTemp.UsedRange
.EntireColumn.AutoFit

Code


end with

I have used this again to reset the range

if averaging code has run then

With shtTemp.UsedRange
.EntireColumn.AutoFit

code

end with


and this has solved the problem.

Thanks again, this was a problem I couldn't get my head around and now I uderstand how to use this piece of code.



It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)