Understanding Issues with INTERSECT function in VBA
 

Good morning.

In an application I'm currently developing, I read two two pieces of data from a list (x,y), then map each x,y pair into a table which has X columns and Y rows.

Everything works fine except I've found that if I don't select the row and column prior to using the intersect function the routine fails with the following error:

Run-time error 13: Type mismatch

My pseudo code looks like:

do for the list of values

find the column in the table corresponding to the X value in the list
set column as rngX
rngX.Select

find the row in the table correcponding to the Y value in the list
set row as rngY
rngY.select

application.Intersect (rngX, rngY) = application.Intersect (rngX, rngY)+1

loop

If I don't include both rngX.Select and rngY.Select commands in exactly the sequence shown the code does not work. Any explanation why?

Art

Understanding Issues with INTERSECT function in VBA
 

Showing pseudo code isn't going to cut it here. Give us the real stuff
so we know exactly what it does and the how of it!

As for .Select.., you don't need to select anything to act on it as
long as you have a fully qualified ref to the cell address/index in the
table.

Not knowing the purpose for finding the coordinates, nor why you think
you need to use the Intersect() function, there are more efficient ways
to go about getting a cell index!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Understanding Issues with INTERSECT function in VBA
 

It will take some time for me to digest this as I'm busy with a few
projects at the moment. First read raises the following...

Row indexes are Long data type, not Integer, and so re-type your
variables to reflect this.

Your loop counter should also be type Long.

I can't see what the layout is for using .CurrentRegion like you do. It
would be helpful if you upload a sample file to a public share and post
a download link so we can do hands on with your real project.

The line using .Intersect has no documentation explaining what you're
trying to do, so please add comments there!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Understanding Issues with INTERSECT function in VBA
 

Hi,

Am Tue, 17 Feb 2015 05:45:31 -0800 (PST) schrieb :

For i = iFirstRow To iLastRow

tbl.Range("A" & iRow & ":M" & iRow).Find(What:=Month(Cells(i, 20)), _
After:=tbl.Range("A" & iRow)).Activate

to find the values and select the range try (modify the ranges for your
table):

Sub Test()
Dim LRow As Long
Dim tbl As Range
Dim myCol As Long
Dim myRow As Long
Dim myCell As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set tbl = .Range("A1:M" & LRow)
myCol = WorksheetFunction.Match(Month(.Cells(1, 20)),
..Range("A1:M1"), 0)
myRow = WorksheetFunction.Match(.Cells(1, 21), .Range("A1:A" & LRow), 0)
Set myCell = .Cells(myRow, myCol)
Application.Goto Union(.Rows(myCell.Row), .Columns(myCell.Column))
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Understanding Issues with INTERSECT function in VBA
 

Garry,

No rush - I'll post to Google Share tonight - (blocked from my work computer)

Art

On Tuesday, February 17, 2015 at 9:10:27 AM UTC-5, GS wrote:
It will take some time for me to digest this as I'm busy with a few
projects at the moment. First read raises the following...

Row indexes are Long data type, not Integer, and so re-type your
variables to reflect this.

Your loop counter should also be type Long.

I can't see what the layout is for using .CurrentRegion like you do. It
would be helpful if you upload a sample file to a public share and post
a download link so we can do hands on with your real project.

The line using .Intersect has no documentation explaining what you're
trying to do, so please add comments there!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Understanding Issues with INTERSECT function in VBA
 

Hi again,

Am Tue, 17 Feb 2015 16:48:44 +0100 schrieb Claus Busch:

to find the values and select the range try (modify the ranges for your
table):

if the Group occurs more than once try this:

Sub Test()
Dim LRow As Long
Dim tbl As Range
Dim myCol As Long, myRow As Long, CountR As Long

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set tbl = .Range("A1:M" & LRow)
tbl.Sort key1:=.Range("A1"), order1:=xlAscending, Header:=xlYes
myCol = WorksheetFunction.Match(Month(.Cells(1, 20)), .Range("A1:M1"), 0)
myRow = WorksheetFunction.Match(.Cells(1, 21), .Range("A1:A" & LRow), 0)
CountR = WorksheetFunction.CountIf(.Range("A:A"), .Cells(1, 21))
Application.Goto Union(.Rows(myRow).Resize(CountR), .Columns(myCol))
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Understanding Issues with INTERSECT function in VBA
 

Garry,

here's the link

https://drive.google.com/file/d/0B8D...ew?usp=sharing

I've streamlined the file down to just the issue at hand.

I also realized a few minutes ago I've got some redundant calls in the routine (like .currentregion), so ... it's not as clean as I would like it.

Art

On Tuesday, February 17, 2015 at 9:10:27 AM UTC-5, GS wrote:
It will take some time for me to digest this as I'm busy with a few
projects at the moment. First read raises the following...

Row indexes are Long data type, not Integer, and so re-type your
variables to reflect this.

Your loop counter should also be type Long.

I can't see what the layout is for using .CurrentRegion like you do. It
would be helpful if you upload a sample file to a public share and post
a download link so we can do hands on with your real project.

The line using .Intersect has no documentation explaining what you're
trying to do, so please add comments there!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Understanding Issues with INTERSECT function in VBA
 

Thanks!
Did you try Claus' solution?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Understanding Issues with INTERSECT function in VBA
 

On Tuesday, February 17, 2015 at 7:19:32 PM UTC-5, GS wrote:
Thanks!
Did you try Claus' solution?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I'm still studying his code to understand how its constructed

Understanding Issues with INTERSECT function in VBA
 

Hi,

Am Wed, 18 Feb 2015 08:11:03 -0800 (PST) schrieb :

I'm still studying his code to understand how its constructed

forget this code. When I wrote it I did not really know what you want to
do.

Look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "Test"
and download it because macros are disabled in OneDrive.
There are two suggestions in that workbook.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional