Macro To Import Data & Compare

Hi Guys,

Brief description of what i want my macros to achieve: I've trying to import data from two Excel spreadsheets, and then compare the data on each list to find the missing elements in each list and then highlight the data row if it is missing. Still rather new in excel-VBA please go easy on me..

Kinda puzzled with the error i have here for my excel-vba codes while running the code below. Below i have attached a copy of my Excel file for you reference. Thanks for your help and time in advance.

Re: Macro To Import Data & Compare

Oh yes, i'm still building on the codes so i have not fully implement everything yet. The problem i faced was to get the program to run the comparison after importing, but it always run into some error when i try to run it..

Re: Macro To Import Data & Compare

I've tried moving stringOfSheet1 and stringOfSheet1up the line and the error gone but i'm still unable to run the Difference sub module with error subscript out of range? Any ideas, sorry rather new in programming

Public CheckBoolean1 As BooleanPublic CheckBoolean2 As BooleanPublic stringOfSheet1 As StringPublic stringOfSheet2 As StringSub SetDefaults()    CheckBoolean1 = False    CheckBoolean2 = FalseEnd Sub

Re: Macro To Import Data & Compare

Then how i should solve this problem, I've suspected it might be this problem but i do not know how to solve it rather quite an amateur in vba...

Re: Macro To Import Data & Compare

You mean i just replace it with this and it should work? But why i'm still having the same error despite the changes, have i missed out something?

Set ws2 = ThisWorkbook.Sheets(Sheets(2)) Set ws3 = ThisWorkbook.Sheets(Sheets(3))

Re: Macro To Import Data & Compare

Hi it works, but another problem surfaces when i try to delete the spreadsheet with the Reset Button. This was cause by Sheets(2) deleted? Is there anyway i can delete both sheets together to prevent this error? Thanks

Application.DisplayAlerts = False
Sheets(2).Delete
Sheets(3).Delete
Application.DisplayAlerts = True

Re: Macro To Import Data & Compare

Haha I've solve it its as simple as

Sheets(3).Delete
Sheets(2).Delete

But can i ask you is it possible for me that the flow of copying spreadsheet to Sheet(2) and (3) be controlled such that when the first import button is pressed its at sheets(2) and the second import button is pressed the sheet is at sheets(3). I know my explanation might be quite bad..

Dim Wkb1 As Workbook
Set Wkb1 = ActiveWorkbook




OpenFile = Application.GetOpenFilename( _
FileFilter:="Excel Files, *.xls; *.csv; *.xlsx", Title:="Importing Excel File 2")




If OpenFile = False Then
MsgBox "Please Select a Excel File"
Exit Sub
Else
Workbooks.Open OpenFile
End If




FileName2 = ActiveWorkbook.Name




Worksheets(1).Copy after:=Wkb1.Worksheets(1)




Workbooks(FileName2).Close SaveChanges:=False

Re: Macro To Import Data & Compare

Hi dbrown14,

Thanks for your help,

Question 1: But i have a query yet again why are my comparison module (Module1) which handles the differentiating of part number at column C does not work anymore? Did i happen to disable anything that i shouldn't? With puzzling result when i press Run comparison every single row gets highlighted red instead of the real differences between both sheets?

Question 2: Is there a possibility that i could set my various button to my desired with my below sub module in (Module1) as it kept giving me error because of undefined buttons called here.

Sub SetDefaults()    CheckBoolean1 = False    CheckBoolean2 = False'--------------------------------------------------------'Application.DisplayAlerts = False'With ActiveWorkbook    'If (.Sheets.Count) >= 3 Then .Sheets(3).Delete    'If (.Sheets.Count) >= 2 Then .Sheets(2).Delete'End With'Application.DisplayAlerts = TrueImportButton1.Enable = TrueImportButton2.Enable = FalseComparisonButton.Enable = FalseResetButton.Enable = False'--------------------------------------------------------End Sub

With my latest attachment below. Thanks for your time and help [Blocked Image: http://www.mrexcel.com/forum/images/smilies/icon_smile.gif] 

Regards,
imso

Re: Macro To Import Data & Compare

I've tried moving the Differences module to the worksheet as you mention but it still doesn't work? When i press the run button almost everything will be highlighted along the rows no matter the differences in part number but with no error massage occurring? Maybe you could take a look at my latest attachment to get what i mean?

Re: Macro To Import Data & Compare

Quote

Another is to do this for the find string. "=""" & temp.value & """"

Sorry for not understanding this sentence of yours above? How do you really implement this sentence into the codes?

Re: Macro To Import Data & Compare

Just confirm is that what you meant below but why is it still not working?

For Each temp In rng2
        If temp.Value <> "" Then
            Set found = rng2.Find(what:="=""" & temp.Value & """", lookat:=xlWhole)
            If found Is Nothing Then
              Set CompareSheet = temp.Worksheet
              CompareSheet.Range("A" & temp.Row, "P" & temp.Row).Interior.ColorIndex = 3
            End If
        End If
    Next temp
     
    For Each temp In rng3
        If temp.Value <> "" Then
            If found Is Nothing Then
              Set found = rng3.Find(what:="=""" & temp.Value & """", lookat:=xlWhole)
              Set CompareSheet = temp.Worksheet
              CompareSheet.Range("A" & temp.Row, "P" & temp.Row).Interior.ColorIndex = 6
            End If
        End If
    Next temp