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
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
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 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)
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