CODEWITHSUNDEEP
vbaexcel
durba138
durba138

Reputation: 37

using columns from 2 different workbooks to make an if statement in VBA excel

Let's say I have variables, yes_n and yes_d. D for denominator and n for numerator. I have workbook A, the flat file, and workbook B, the reference file. I am trying to pull columns from two different workbooks and then if the words in a cell in workbook A, sheet A, column A = the words in a cell in workbook B, sheet, column B and the words in the column to the left of cell B are yes then add one to my variable yes_d. Then after, if the words in cell D of workbook A are "some value" then add 1 to the numerator also. How do I say something like "If cellA(workbookA.SheetA.columnA) = cellB(workbookB.SheetB.columnB) AND the offset of B = "yes" then yes_d + 1. If cellDworkbookA.SheetA.columnD) = "some value" then yes_n + 1.

For M = 2 To 1000
    For R = 2 To 60

        If Workbooks("\flatfile.xlsb").Worksheets("ifyesorno").Range("A" & M) = _
           Workbooks("\referencefile.xlsm").Worksheets("reference1").Range("B" & R)_
            And Workbooks("\referencefile.xlsm").Worksheets("reference1").Range("A"_
            & R).Value = "yes" Then

            yes_d = yes_d + 1

            If Workbooks("\flatfile.xlsb").Worksheets("ifyesorno").Range("D" & M) = "some value" Then

                yes_n = yes_n + 1

            End If
        End If

    Next
Next

Upvotes: 0

Views: 214

Answers (1)

user6432984
user6432984

Reputation:

The logic of your code already meet your criteria. You were improperly referencing the open workbooks. If the workbooks are not open then you'll have to open them. A space is needed between the code and line continuation _.

Incorrect: .Range("B" & R)_

Correct .Range("B" & R) _

Creating variables to shorten references will greatly improve the readability of your code. 

Dim wsFlat As Worksheet, wsReference As Worksheet
Set wsFlat = Workbooks("flatfile.xlsb").Worksheets("ifyesorno")
Set wsReference = Workbooks("referencefile.xlsm").Worksheets("reference1")

For M = 2 To 1000
    For R = 2 To 60

        If wsFlat.Range("A" & M) = wsReference.Range("B" & R) And wsReference.Range("A" & R).Value = "yes" Then

            yes_d = yes_d + 1

            If wsFlat.Range("D" & M) = "some value" Then
                yes_n = yes_n + 1
            End If
        End If

    Next
Next

Upvotes: 1

Related Questions