PDA

View Full Version : Solved: Match one worksheet with another worksheet



Shazam
01-14-2006, 12:16 PM
Hello eveyone,


I attach a sample workbook below. I have 2 worksheets one is name List and import. I import data from the database into the import worksheet. But I dont need all that data. So I created a list the ones I do want. So can the code look in column C in import and if it dont match in the worksheet List then delete the entire row. I created another worksheet that is named Results to give you a better visual.

mvidas
01-17-2006, 11:44 AM
Hello,
Give the following a try, should do what you need. The results were different than yours, since yours are sorted with a blank line between each part#. Did you want me to include this functionality in it?Sub SHAZAM()
Dim i As Long, CLL As Range, ImportWS As Worksheet, ListWS As Worksheet
Set ImportWS = Sheets("import")
Set ListWS = Sheets("list")
Application.ScreenUpdating = False
For i = ImportWS.Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
Set CLL = Nothing
Set CLL = ListWS.Columns("C").Find(ImportWS.Cells(i, 3), LookIn:=xlValues, _
LookAt:=xlWhole)
If CLL Is Nothing Then
ImportWS.Rows(i).Delete
End If
Next
Application.ScreenUpdating = True
End SubMatt

austenr
01-17-2006, 01:08 PM
Hey ya Matt,

Can't get this to compile. The error is on Rows.Count. Thought you would like to know.

Austen

mvidas
01-17-2006, 01:15 PM
Thanks Austen ... not sure why that would happen for you though. Could you be compiling it in a different VBA than excel? As written, even without an activeworkbook, it should still compile without error if done in excel.. though to be thorough I should have used ImportWS.Rows.Count (really shouldn't have made a difference though).
Do you remember what error you got on that line was?

austenr
01-17-2006, 01:32 PM
Expected function or variable

Austen

mvidas
01-17-2006, 01:39 PM
Hmmmm.. i dont know what to tell you! Should be fine as is, did you still get that error using ImportWS.Rows.Count instead?

austenr
01-17-2006, 01:41 PM
Yes. Compiling using Visual Basic 6.3

mvidas
01-17-2006, 01:51 PM
Hmmm.. if you're compiling in straight VB, you may have to qualify everything there to have a sheet/application reference. Another thing might be the use of the constant xlUp. You may want to try using .End(-4162) instead
Full code (hopefully), everything should be qualified on here:Sub austenrForVB63()
Dim i As Long, CLL As Object, ImportWS As Object, ListWS As Object, xlApp As Object
Set xlApp = CreateObject("excel.application")
xlApp.Visible = True
xlApp.Workbooks.Open "C:\workbook.xls"
Set ImportWS = xlApp.Sheets("import")
Set ListWS = xlApp.Sheets("list")
xlApp.ScreenUpdating = False
For i = ImportWS.Range("C" & xlApp.Rows.Count).End(-4162).Row To 2 Step -1
Set CLL = Nothing
Set CLL = ListWS.Columns(3).Find(ImportWS.Cells(i, 3), LookIn:=-4163, LookAt:=1)
If CLL Is Nothing Then
ImportWS.Rows(i).Delete
End If
Next
xlApp.ScreenUpdating = True
End SubThis wouldn't be necessary for you, Shazam, only for someone using vb

austenr
01-17-2006, 02:02 PM
Sorry Matt, I should have told you I was compiling in the VBE in EXCEL 2003. :banghead:

mvidas
01-17-2006, 02:04 PM
Then I definately have no idea :)

austenr
01-17-2006, 02:06 PM
Im going to send you my file.

mvidas
01-17-2006, 02:15 PM
Ahh, that makes more sense :) I had opened up Shazam's example file, and deleted the 4 modules inside it, as I wasn't using them. I inserted a new module, and created my code.

Shazam has a macro in his Module2 named "Rows", which is where your error is coming from. Either rename that macro or delete it, you're getting the error because he named it the same as a built in excel object :)

Thanks, I didn't think to try it in his original file, and was going a little crazy there trying to figure out why you'd get that compile error:bug:

austenr
01-17-2006, 02:21 PM
Sorry to cause you so much aggrevations!!! :eek:

mvidas
01-17-2006, 02:26 PM
No worries, I'd rather be challenge and get aggrivated than unchallenged and bored :)

austenr
01-17-2006, 02:59 PM
Funny me too. BTW, I tried the macro and it did not delete anything.

mvidas
01-17-2006, 03:15 PM
Did you use my original one, or the austenrvb63 one? You should be using my original one, the other wouldnt work as described unless you had the workbook at c:\workbook.xls and you made the app visible (something I just added to it)

Shazam
01-17-2006, 06:52 PM
Thank mvidas! For awhile I thought i was not going to get a response but thank you for responding.

Thanks!:thumb

austenr
01-17-2006, 09:43 PM
Hi Matt,

I take back what I siad that it did not delete rows. Was looking at my test version before your changes. LOL!! http://vbaexpress.com/forum/images/smilies/eek.gif

mvidas
01-18-2006, 06:06 AM
:)

Sorry about the delay, shazam, maybe your question disappeared for a little bit?