problem with Rows.Count and comparing cells

[PZipko]

I'm trying to make a simple program to take two columns of tags and
compare them to find any entries in the first column that aren't in
the second. I'm running into two problems that are giving me trouble.

The first is that the count function is counting all rows in Excel,
including the blank ones. I get an overflow error when I use integers
even though I only have about 900 records in each table. I can keep it
from crashing by changing the variables to a long. The value returned
is 65536, the maximum number of rows Excel supports. This shouldn't be
how the function works, and I'm not sure what I'm doing wrong with it.
With this huge value the program still crashes at the for loops when
the it runs out of memory. Is there a way to get the count function to
not count all those blank rows Excel throws in at the end?

Also, rather than use count, I just set the number of rows to
arbitrary numbers and tried running it. I have a problem when I try to
compare values in two cells. I get a "Run Time Error '1004':
Application-defined or object-defined error". This is my first attempt
at using at using VBA with excel, so if anyone can help me out I'd
apreciate it. Here's the code if it helps:

Dim sheet1_rows As Integer
Dim sheet2_rows As Integer
Dim counter As Integer
Dim add As Boolean
Sub compare()

sheet1_rows = Sheet1.Rows.count
sheet2_rows = Sheet2.Rows.count
count = 0

For i = 0 To sheet1_rows Step 1
add = True
For j = 0 To sheet2_rows Step 1
If Sheet1.Cells(i, 2).Value = Sheet2.Cells(j, 2).Value
Then
add = False
j = sheet2_rows
End If
Next j
If add Then
Sheet3.Cells(count, 1) = Sheet1.Cells(i, 2)
count = count + 1
End If
Next i
End Sub

 

[Dave Peterson]

Sheet1.Rows.count is the same as 65536.

You could make all your integers longs.

dim sheet1_rows as long

But before you do that, you may want to take a look at some of the ways Chip
Pearson handles duplicates.

http://www.cpearson.com/excel/duplicat.htm

There are some nice built in functions that can save a lot of time.

 

[Paul Zipko]

Thanks, that site had what I needed to get started. Now what I'd like to
do is expand it so that instead of just comparing individual cells, it
compares rows from different tables that have the same structure. I've
tried changing the criteria in COUNTIF to a range but it just returned
zero's for every entry. I've also worked at getting the VBA macro
working but it's still coming back with a run time error when it tries
to compare the rows. Any help on this would be apreciate,

Paul

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Dave Peterson]

I'd just cheat and concatenate each row into a cell.

=a1&"|"&b1&"|"&c1....
where | is a unique character that isn't in your data.

Then compare one column again.