-
1 Attachment(s)
Copying File issue
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]
-
It means that your object, sheetname, does not exist. You set the sheetnames to the activeworkbook.name which would be ThisWorkbook.Name. It is odd to name sheets with the workbook name.
You first need to move your public variables to the Module. Otherwise, you variable values will be "". After playing the two import macros, in VBE's Immediate Window, you can see the value of your two variables:
[VBA]?stringOfSheet1
VBA_Comparison.xlsm
?stringOfSheet2
VBA_Comparison.xlsm[/VBA]Debug would have shown you the same thing.
If you need a routine to see if the worksheet exists, we can add that.
Now that you know what is going on, use that knowledge to your benefit.
-
1 Attachment(s)
Hi Kenneth Hobs,
I've tried what you mention but still its not working? Maybe i am not able to interpret your explanation, rather quite new in excel-vba...
Below is a attachment for what i have tried out so far.. Could you tell me what I've have not done?
Regards,
imso