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