Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with Rows.Count and comparing cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with Rows.Count and comparing cells
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. PZipko wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with Rows.Count and comparing cells
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with Rows.Count and comparing cells
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. Paul Zipko wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count if comparing 2 cells | Excel Worksheet Functions | |||
Lookup/count blank cells problem | Excel Discussion (Misc queries) | |||
How do I count nonblank cells in rows within Excel? | New Users to Excel | |||
Comparing two cells in a colum with 2000 rows | Excel Discussion (Misc queries) | |||
Problem with UsedRange.Rows.Count | Excel Programming |