Code not working: find with a variable. Please Help

[kislicins]

Hi everyone. I am not good with VBA at all and really hope that you can help! I need the macro to find and compare different peaces of data and if there are differences between them - paste out the row on a separate sheet. The problem is that I need a variable from where the data is taken in the first place so that the excel would go through all the data I need to search for.

So far I have this:

Sub Macro1()
Dim X As Integer
   For X = 2 To 10
    If Cells.Find(What:=Sheets("current month").Cells(X, 1), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate = True Then
        ActiveCell.EntireRow.Select
        Selection.Copy
        Sheets("Sheet1").Select
        Rows(X).Select
        ActiveSheet.Paste
        Else
        MsgBox ("AAA")
    End If
    Next X
End Sub

It works fine for the first time but after Next X it returns an error that Object variable or With Block is not set. Can anyone spot the mistake?
Thanks in advance!

 

[kislicins]

I think the error is popping up because excel sees my Sheets("current month") as a variable and I don't want it to. How can I identify that it is only a reference to a particular workbook?

 

[Richard Schollar]

Right, I think it would greatly help everyone if you could post a small example of the data you are working with so that we can see first hand what you are trying to do. Please use one of the html makers (see links in my siggy). With the sample, please explain with simple steps exactly what you are trying to compare with what, and what result you want to end up with.

Thank you

 

[kislicins]

Right. I need to compare two sheet of data for current and previous months and find out new starters/leavers and whether any details of continuing employees has changed. Because I am rubbish with VBA and not a programmer at all I thought that a good idea was to first of all search by employees number to identify starters/leavers. Therefore the code was aimed at picking up employee numbers from here:<SCRIPT language=JavaScript src=""></SCRIPT>

Book1
	A	B	C	D	E
1	EmloyeeID	FirstName	LastName	Address
2	22599	Mark	Brown	33AlbionClose
3	22601	Elly	Black	11MorningAvenue
4	22602	Robert	Black	55WeedStreet
current month

and searching them through this data to identify who is leaver/starter:<SCRIPT language=JavaScript src=""></SCRIPT>

Book1
	A	B	C	D	E
1	EmloyeeID	FirstName	LastName	Address
2	22599	Mark	Brown	33AlbionClose
3	22601	Elly	Black	11MorningAvenue
4	22602	Robert	Green	55WeedStreet
previous month

Obviously I understand that my code is just the beginning and I need to change the if funciton etc but my current problem is to make excel go through all employee numbers and search for each of them in the second data sheet (previous month). I beleive that from there I could figure something out but for now I am really stuck.

 

[kislicins]

The code should take the employee number from sheet current month cell A2 and search for it in the previos month. For now I want the excel to just copy the row if the employee number exists in the previous month. Ideally I would like it to copy the row from current month if there is no record of that employee number in the previous month but I think that's pretty easy to change. The problem is that I want the cell A2 of current month sheet (where the data comes from) to be a variable so that after searching for A2 data the macro would go to A3 and so on.

 

[kislicins]

I have managed the code to work... sort of, but it crashes when can't locate the data in the current month table. Any suggestion? How can I say to VBA: look for value of cell A20 and if you cannot find it, do something (let's say pop up msgbox)

 

[Richard Schollar]

Hi

Regarding your last question - Find usefully returns Nothing if the search term isn't found, so you can easily build in a logical expression to determine if anything was found:

Dim r As Range
Set r = Range("A:A").Find(What:=YourSearchTerm)  'set an object variable
If Not r Is Nothing Then   'check whether the search term was found in the specified range.  If found, then r will be something so "Not r Is Nothing" returns True
   'take appropriate action eg
   'r.Copy Destination:=Sheets("OtherSheet").Range("A1")
End If

'or alternatively:
If r Is Nothing Then
   Msgbox "Search term not Found!"
End If

I have looked at what you are doing and I must ask why you are doing this finding via a macro - I think it would be easier to have a formula column in an adjacent empty column along the lines of (in your current month sheet):

=ISNUMBER(MATCH('Current Month'!A2,'Previous Month'!A:A,0))

This will return True for existing employees, but False for any new ones - then you could filter on this column to extract the new employee data.

Similarly, in the previous month sheet, you could include in another empty ajacent column:


=ISNUMBER(MATCH('Previous Month'!A2,'Current Month'!A:A,0))

which will return True for all existing employees, but False for leavers. Again you could filter on the results.

Make sense?

 

[kislicins]

Thank you very much! It is working great!

I am not sure why I want to use macro honestly to say. Probably I enjoy learning about VBA bit by bit or because it does work more elegant. And I must admit that I didn't know how to do via formulas. Plus I do need the values that are canged, not only true/false.