CODEWITHSUNDEEP
excelvba
Bart g
Bart g

Reputation: 585

Checking values in two different files in VBA

I am trying to verify column values in two separate files. The file paths for the two files are in two separate cells in the same sheet. I got it to work for one file but I need to verify column values in two separate sheets. This is what I have tried so far.

However, I am getting the error message. "Compile error:vInvalid Next control variable reference" And it highlights Next x. Why?

Sub ButtonSearch_Click()

Application.ScreenUpdating = False

' Name of columns to be found
JiraColumns = Array("CRM#", "status", "Key", "Resolution", "Assignee", "Customer Name", "Company")
SparksColumns = Array("QCCR", "Hpsw Status")

' Define boundaries
For x = LBound(JiraColumns) To UBound(JiraColumns)
    For y = LBound(SparksColumns) To UBound(SparksColumns)

        ' Search for column names on general_report Sheet
        With oWB.Sheets("general_report")
            Set foundRange = .Cells.Find(What:=JiraColumns(x), After:=.Cells(1, 1), _
                                         LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlRows, _
                                         SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        End With

        If foundRange Is Nothing Then
            ' Missing Column
            intRow = 1
            MsgBox "Jira is missing Column: " & (JiraColumns(x))
            oWB.Close False
            Exit Sub
        Else
            Count = Count + 1
        End If

    Next x

    ' *** *** Sparks verification *** ***  SECOND FILE, SECOND FILE, SECOND FILE *** *** ***

    With sWB.Sheets("Sample-Sparks")
        Set foundR = .Cells.Find(What:=SparksColumns(y), After:=.Cells(1, 1), _
                                 LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlRows, _
                                 SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    End With

    If foundR Is Nothing Then
        ' Missing Column
        intRow = 1
        MsgBox "Sparks is missing Column: " & (SparksColumns(y))
        sWB.Close False
        Exit Sub
    Else
        CountS = CountS + 1
    End If

Next y

If Not foundRange Is Nothing Then foundRange.Activate
If Not foundR Is Nothing Then foundR.Active

' close the source workbook without saving any changes
oWB.Close False

' free memory
Set wbFind = Nothing

' Show activity on screen
Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 49

Answers (1)

Netloh
Netloh

Reputation: 4378

You have mixed up your next-loops. Your y loop should be nested inside you x loop. I.e.

For x = LBound(JiraColumns) To UBound(JiraColumns)
    For y = LBound(SparksColumns) To UBound(SparksColumns)
    '...code...
    Next y
'...code...
Next x

The other problem regarding the error in the line SFile = Worksheets("Sheet1").Range("F17").Value occurs because you, in the line before that, opens the file on the JFilepath. Because the JFile is now you ActiveWorkook and that workbook doesn't contain a sheet named Sheet1 you receive the error message.

You need to be careful on which workbook is the active workbook when working with multiple workbooks. You could declare you workbooks like (in this case the ActiveWorkbook):

Dim ActWB As Workbook
Set ActWB = ActiveWorkbook

And then get the sFile value like:

sfile = ActWB.Sheets("Sheet1").Range("F17").Value

Likewise cells in the JFile can be referenced like (for example): oWB.sheets("Test").range("A1").value

Upvotes: 4

Related Questions