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.
Kinda puzzled with the error i have here for my excel-vba codes while running the code below. With error --> subscript out of range
[VBA]Set ws2 = ThisWorkbook.Sheets(stringOfSheet1)
Set ws3 = ThisWorkbook.Sheets(stringOfSheet2)[/VBA]
Could someone enlighten me on how should i get my codes running?
Main set of code below with an attached excel file.
[VBA]Option Explicit
Public CheckBoolean1 As Boolean
Public CheckBoolean2 As Boolean
Public stringOfSheet1 As String
Public stringOfSheet2 As String
Public dynamicSheet1 As Worksheet
Public dynamicSheet2 As Worksheet
Private Sub ComparisonButton_Click()
ComparisonButton.Enabled = False
Call Differences
ResetButton.Enabled = True
End Sub
Private Sub ImportButton1_Click()
Dim OpenFile As Variant
Dim FileName1 As String
CheckBoolean1 = True
If CheckBoolean2 = False Then
ComparisonButton.Enabled = False
ElseIf CheckBoolean1 = True And CheckBoolean2 = True Then
ComparisonButton.Enabled = True
End If
Dim Wkb1 As Workbook
Set Wkb1 = ActiveWorkbook
OpenFile = Application.GetOpenFilename( _
FileFilter:="Excel Files, *.xls; *.csv; *.xlsx", Title:="Importing Excel File 1")
If OpenFile = False Then
MsgBox "Please Select a Excel File"
Exit Sub
Else
Workbooks.Open OpenFile
End If
FileName1 = ActiveWorkbook.Name
Worksheets(1).Copy after:=Wkb1.Worksheets(1)
Workbooks(FileName1).Close SaveChanges:=False
stringOfSheet1 = ActiveWorkbook.Name
End Sub
Private Sub ImportButton2_Click()
Dim OpenFile As Variant
Dim FileName2 As String
CheckBoolean2 = True
If CheckBoolean1 = False Then
ComparisonButton.Enabled = False
ElseIf CheckBoolean1 = True And CheckBoolean2 = True Then
ComparisonButton.Enabled = True
End If
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
stringOfSheet2 = ActiveWorkbook.Name
End Sub
Private Sub ResetButton_Click()
ComparisonButton.Enabled = False
ResetButton.Enabled = False
CheckBoolean1 = False
CheckBoolean2 = False
ImportButton1.Enabled = True
ImportButton2.Enabled = True
Application.DisplayAlerts = False
dynamicSheet1.Delete
dynamicSheet2.Delete
Application.DisplayAlerts = True
End Sub
Sub Differences()
Dim ws2 As Worksheet, ws3 As Worksheet, CompareSheet As Worksheet
Dim lastRow2 As Integer, lastRow3 As Integer
Dim rng2 As Range, rng3 As Range, temp As Range, found As Range
Application.ScreenUpdating = False
Set ws2 = ThisWorkbook.Sheets(stringOfSheet1)
Set ws3 = ThisWorkbook.Sheets(stringOfSheet2)
lastRow2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
lastRow3 = ws3.Range("A" & Rows.Count).End(xlUp).Row
Set rng2 = ws2.Range("C21:C" & lastRow2)
Set rng3 = ws3.Range("C21:C" & lastRow3)
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
Set found = rng3.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
End Sub[/VBA]
Reply With Quote
