Excel VBA if 2 criteria then else function

2019-08-21 18:16发布

I have 2 files (+sheetnames): 1. Productiegegevens (registratie TAA) 2. operator template TAA (export registratie TAA)

I would like to write a macro that executes the following: Search the value of 'Export registratie TAA $A$2' in 'registratie TAA' column A Search the value of 'Export registratie TAA $D$2' in 'registratie TAA' column D

If both values are found in the same row then: copy 'operator template TAA row 2' to the row that is found in registratie TAA as above

If both values aren't found in the same row: insert new row at row 5 in registratie TAA Copy 'operator template TAA row 2' to the new row 5 in registratie TAA

I copied the following code as an example and beginning of the new code, but I don't know how to search for two values at the same time.

  Dim WbO As Workbook
    Dim WbW As Workbook
    Dim i As Long
    Dim LRA As Long
    Dim LRB As Long
    Dim RowToCopy As Long
    Dim Rowstr As Long

    Dim Searchstr As Long
    Dim Address As Range
    Dim Searchrng As Range

Dim x As Long
Dim Rowstr1 As Long
Dim searchstr1 As Long
Dim address1 As Range
Dim searchrng1 As Range

    Set WbO = Workbooks("productiegegevens.xlsm") '<= Set workbook to variables
    Set WbW = Workbooks("operator template TAA.xlsm")

    LRA = WbW.Worksheets("export registratie TAA").Range("A" & Rows.Count).End(xlUp).Row '<= Find Lastrow
LRB = WbW.Worksheets("export registratie TAA").Range("D" & Rows.Count).End(xlUp).Row

    For i = 2 To LRA '<= Loop column A (Workbook:operator template TAA)
        Searchstr = WbW.Worksheets("export registratie TAA").Range("A" & i).Value '<= Set what to search for
        Rowstr = i '<= Searchstr row
        Set Searchrng = WbO.Worksheets("registratie TAA").Columns("A") '<= Set where to search for
        Set Address = Searchrng.Find(What:=Searchstr, LookAt:=xlWhole) '<= Result of the search

    If Address Is Nothing Then
        'If what we search for not found
        WbO.Worksheets("registratie TAA").Rows("5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        WbW.Worksheets("export registratie TAA").Rows(Rowstr).EntireRow.Copy
        WbO.Worksheets("registratie TAA").Rows(5).PasteSpecial Paste:=xlPasteValues
Else
End If
Next i

    For x = 2 To LRB '<= Loop column D (Workbook:operator template TAA)
        searchstr1 = WbW.Worksheets("export registratie TAA").Range("D" & x).Value '<= Set what to search for
        Rowstr1 = x '<= Searchstr1 row
        Set searchrng1 = WbO.Worksheets("registratie TAA").Columns("D") '<= Set where to search for
        Set address1 = Searchrng.Find(What:=searchstr1, LookAt:=xlWhole) '<= Result of the search

If Address Is Nothing Then
        'If what we search for not found
        WbO.Worksheets("registratie TAA").Rows("5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        WbW.Worksheets("export registratie TAA").Rows(Rowstr).EntireRow.Copy
        WbO.Worksheets("registratie TAA").Rows(5).PasteSpecial Paste:=xlPasteValues

    Else
        'If what we search for found
        RowToCopy = Address.Row '<= Where we find the Searchstr
        WbW.Worksheets("export registratie TAA").Rows(x).EntireRow.Copy
        WbO.Worksheets("registratie TAA").Rows(RowToCopy).PasteSpecial Paste:=xlPasteValues
    End If

    Next x


End Sub

标签: excel vba
0条回答
登录 后发表回答