Results 1 to 3 of 3

Thread: Copying File issue

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Jul 2011
    Posts
    19
    Location

    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]
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •