MACRO: IF THEN Statement (Multiple Criteria Match then Copy)

Howdy,

I would like to be able to do the following in a macro:

I would like an IF statement to meet two criteria if possible before copying cells from the MAFFT sheet to the Deployment sheet.

• I would like to match the “Configured Serial #” and the “Mfg. Serial Number” from the Deployment sheet to the “Oracle Config Serial Number (custom12)” and “Serial Number” on the MAFFT sheet.
  
  • I would like matching based off Column header name and not the position since the columns could change
    
    • i.e. We could add in another column with a new header that is needed.
  
  
• If they match then it will copy the matching Cells from the MAFFT sheet to the Deployment sheet
  
  • The cells are highlighted in Bright Green on the Deployment Sheet and MAFFT Sheet
    
    • Deployment Sheet: Starting Column/Row: E10, AE10 – BZ10, CM10 – DJ10, DX10. Ending Row: 5173
    • MAFFT Sheet: Starting Column/Row: G3 – BL3, CA3 – CT3, CV3 – CZ3. No Ending Row to look in. But if there has to be one then go to 5173.
  
  
• If they do NOT match then you get a message telling you which item does not match
  
  • i.e. Configured Serial # does not match on the MAFF Sheet or The Serial Number does not match on the Deployment Sheet

I have attached a Sample of the Workbook.

Thank you for any help you can provide.

Quote:

---

I would like matching based off Column header name and not the position since the columns could change

---

That is possible to do all in code, BUT, it will be slower than:
Another sheet with a cross reference for Column Numbers. This XRef can be partially maintained with code(inserting the Column numbers,) but adding columns to be copied and pasted and Running the Maintenance code would have to be done manually. ie, Insert, delete or move any columns means you would have to Add or delete any such column header names from the XRef and Run the Macro, Probably by clicking a command button on the XRef sheet.

Note that MAFFT has ten more Green Columns than Deployment

Meter Read Date	AI
B&W Total Meter	AJ
B&W Print Total Meter	AK
B&W Copy Total Meter	AL
Color Total Meter	Am
Color Print Total Meter	An
Color Copy Total Meter	AO
Duplex Total Meter	AP
Double Click (11 X 17) Total Meter	AQ
Scan Total Meter	AR

Deployment DF9: Shop, Encryption Key uploaded to TRAC (custom28) Is not in MAFFT

"Notes" and "UpDate Notes" Are not Identical. If one must be copied over to the other, I changed the XRef Key to simply Notes.

I did find another pair that wasn't identical, but it was a minor edit. Attached is what I have done so far

I am looking into what you posted. I might have highlighted more than needed. I will fix the name of one of the columns. But I am not seeing how it does the check and then the copy..

Quote:

---

Originally Posted by SamT

Deployment DF9: Shop, Encryption Key uploaded to TRAC (custom28) Is not in MAFFT

"Notes" and "UpDate Notes" Are not Identical. If one must be copied over to the other, I changed the XRef Key to simply Notes.

I did find another pair that wasn't identical, but it was a minor edit. Attached is what I have done so far

---

I have updated the Sample with the correct Headers and on the XRef I updated the column names on the Dev and the numbers that they both line up with..

Need a Decision

It is easiest to code as if this book is going to be used in Production. That will require you to put all your data in this book. Even that can be automated.

To use the code on another book will require you to insure that all the Named Ranges mentioned in this XRef sheet be duplicated in any and all other book(s) the code must operate on.The other books will each require their own XRef sheet to handle any differences or changes in all books.

Your Choice. I prefer #1. Of Course :D

I change XRef to the pair of column headers to match and the fields to copy

I wasn't sure since Deployment had no test data, so I guessed and added some to have something to match from MAFFT

Code:

---

Option Explicit
Const headerMAFFT As Long = 2
Const headerDeploy As Long = 9

Dim wsMAFFT As Worksheet, wsDeploy As Worksheet, wsCrossRef As Worksheet
Dim aMatch() As Long, aCopy() As Long
Dim aCleanMAFFT As Variant, aCleanDeploy As Variant

Sub MAFFT2Development()
    Dim r As Range, rMAFFT As Range, rDeploy As Range
    Dim i As Long, iMAFFT As Long, iDeploy As Long, iCopy As Long
    Dim s As String
    'set the sheets
    Set wsMAFFT = Worksheets("MAFFT")
    Set wsDeploy = Worksheets("Deployment")
    Set wsCrossRef = Worksheets("CrossRef")

    'build arrays with clean headers because the LF and spaces cause mis-match
    With wsMAFFT
        Set r = Range(.Cells(headerMAFFT, 1), .Cells(headerMAFFT, .Columns.Count).End(xlToLeft))
    End With
    aCleanMAFFT = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(r.Value))
    For i = LBound(aCleanMAFFT) To UBound(aCleanMAFFT)
        aCleanMAFFT(i) = Clean(CStr(aCleanMAFFT(i)))
    Next i
   
    With wsDeploy
        Set r = Range(.Cells(headerDeploy, 1), .Cells(headerDeploy, .Columns.Count).End(xlToLeft))
    End With
    aCleanDeploy = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(r.Value))
    For i = LBound(aCleanDeploy) To UBound(aCleanDeploy)
        aCleanDeploy(i) = Clean(CStr(aCleanDeploy(i)))
    Next i
 
    'build array of col numbers with the MAFFT and Deployment fields to match
    Set r = wsCrossRef.Cells(1, 1)
    Set r = Range(r, r.End(xlDown)).Resize(, 2)
    ReDim aMatch(1 To r.Rows.Count, 1 To 2)
    For i = 2 To r.Rows.Count  '  skip headers
        aMatch(i, 1) = colNumber(Clean(r.Cells(i, 1).Value), aCleanMAFFT, headerMAFFT, wsMAFFT)
        aMatch(i, 2) = colNumber(Clean(r.Cells(i, 2).Value), aCleanDeploy, headerDeploy, wsDeploy)
    Next i
       
    'build array of the column numbers to copy
    Set r = wsCrossRef.Cells(1, 3)
    Set r = Range(r, r.End(xlDown))
    ReDim aCopy(1 To r.Rows.Count, 1 To 2)
    For i = 2 To r.Rows.Count  '  skip headers
        aCopy(i, 1) = colNumber(Clean(r.Cells(i, 1).Value), aCleanMAFFT, headerMAFFT, wsMAFFT)
        aCopy(i, 2) = colNumber(Clean(r.Cells(i, 1).Value), aCleanDeploy, headerDeploy, wsDeploy)
    Next i

    'set the data ranges
    Set rMAFFT = wsMAFFT.Cells(headerMAFFT, 1).CurrentRegion    'starts in row 1, 1 = filler, 2 = col headers
    Set rDeploy = wsDeploy.Cells(headerDeploy, 1).CurrentRegion            'starts in row 8, 8 = filler, 9 = col headers

    'go down MMAFFT and check each group of aMatch against Deploy
    Application.ScreenUpdating = False
   
    For iMAFFT = 3 To rMAFFT.Rows.Count
        For iDeploy = 3 To rDeploy.Rows.Count
            If CheckMatchs(rMAFFT.Rows(iMAFFT), rDeploy.Rows(iDeploy)) Then
                For iCopy = LBound(aCopy, 1) + 1 To UBound(aCopy, 1)
                    rMAFFT.Cells(iMAFFT, aCopy(iCopy, 1)).Copy rDeploy.Cells(iDeploy, aCopy(iCopy, 2))
                Next iCopy
                GoTo GetNextMAFFT
           
            End If
        Next iDeploy
   
        s = "No match found for " & vbCrLf
        For i = 2 To wsCrossRef.Cells(1, 1).End(xlDown).Row
            s = s & wsCrossRef.Cells(i, 1) & vbCrLf
        Next i
        MsgBox s
   
GetNextMAFFT:
    Next iMAFFT

    Application.ScreenUpdating = True
End Sub
Private Function CheckMatchs(rMAFFT As Range, rDeploy As Range) As Boolean
    Dim i As Long
    Dim sMAFFT As String, sDeploy As String
   
    CheckMatchs = False
    For i = LBound(aMatch, 1) + 1 To UBound(aMatch, 1)
        sMAFFT = rMAFFT.Cells(1, aMatch(i, 1))
        sDeploy = rDeploy.Cells(1, aMatch(i, 2))
       
        If LCase(sMAFFT) <> LCase(sDeploy) Then Exit Function
    Next i
    CheckMatchs = True
End Function

Private Function Clean(s As String) As String
    s = Application.WorksheetFunction.Clean(s)
    Clean = Replace(s, " ", vbNullString)
End Function

Private Function colNumber(colHeader As String, colArray As Variant, colRow As Long, sh As Worksheet) As Variant
    Dim m As Long
   
    m = 0
    On Error Resume Next
    m = Application.WorksheetFunction.Match(colHeader, colArray, 0)
    On Error GoTo 0
   
    If m > 0 Then
        colNumber = m
        Exit Function
    End If
   
    MsgBox colHeader & " not found in row " & colRow & " on " & sh.Name
   
 colNumber = CVErr(xlErrValue)
End Function

---

Hi,

1. Can we have the script run only if the Field "Date Order Booked" on the Deployment Sheet has data entered?
2. Is there a way on the error to tell you what Column and what Row item is missing or wrong?
3. Is there a way to clear the filled in information if the Config SN and MFG SN are removed? So that it is made blank?
4. I am also looking at putting in a VLookup on the Deployment Sheet Column B "Match to MAFFT" where it shows if the MFG SN Matches to the Serial Number on the MAFFT.



Thanks for the help. I do like the CrossRef or XRef Idea as well..

The error message was because Deployment did not have Config and SN that matched what was on MAFFT

Both Config and SN have to match on both sheets for the rest to be copied over from MAFFT to Deployment

1. I added longer / better 'Not Found' error message

2. "Is there a way to clear the filled in information if the Config SN and MFG SN are removed? So that it is made blank?"

Not sure what you mean. Remove on which sheet? Cleared on which sheet?

Possible to clear everything on Deployment except for Config and SN and that copy over from MAFFT only the matching data

Hi and thank you,

If we delete on the Deployment sheet the Config SN and the MFG SN it would then remove the items that were copied into the Deployment sheet from the MAFFT sheet. I don't want anything ever deleted or changed on the MAFFT sheet.

Quote:

---

Originally Posted by Paul_Hossler

The error message was because Deployment did not have Config and SN that matched what was on MAFFT

Both Config and SN have to match on both sheets for the rest to be copied over from MAFFT to Deployment

1. I added longer / better 'Not Found' error message

2. "Is there a way to clear the filled in information if the Config SN and MFG SN are removed? So that it is made blank?"

Not sure what you mean. Remove on which sheet? Cleared on which sheet?

Possible to clear everything on Deployment except for Config and SN and that copy over from MAFFT only the matching data

---

Quote:

---

If we delete on the Deployment sheet the Config SN and the MFG SN it would then remove the items that were copied into the Deployment sheet from the MAFFT sheet

---

How is that different from just deleting the row on the Deployment sheet?


If the macro doesn't find that Config SN and the MFG SN on Deployment, it won't copy anything over even if it's on MAFFT

My thought was since someone else will be filling in the other fields and if they decide to not have a specific Config SN and Serial Number assigned to that area then it would remove anything that might have been copied over in a prior macro run. I.e. they remove the Config SN and Serial Number but leave the other fields filled in until they decide which to assign. I would like to be able to remove anything that was copied over in a prior macro run.. Did that explain what I wanted to do?

This could also be a second macro to remove the filled in info if there are no Config SN and Serial Number....

BTW love the new message!

Quote:

---

Originally Posted by Paul_Hossler

How is that different from just deleting the row on the Deployment sheet?


If the macro doesn't find that Config SN and the MFG SN on Deployment, it won't copy anything over even if it's on MAFFT

---

Quote:

---

Originally Posted by pawcoyote

My thought was since someone else will be filling in the other fields and if they decide to not have a specific Config SN and Serial Number assigned to that area then it would remove anything that might have been copied over in a prior macro run. I.e. they remove the Config SN and Serial Number but leave the other fields filled in until they decide which to assign. I would like to be able to remove anything that was copied over in a prior macro run.. Did that explain what I wanted to do?

This could also be a second macro to remove the filled in info if there are no Config SN and Serial Number....

---

If they delete the Config SN and SN for an entry on 'Deployment' but leave the other fields, there's no sure way to reference back to the source on MAFFT

Since someone could make manual changes to a 'Deployment' line, there's no easy way to see if something was copied over or manually changed/entered.

A second macro to delete rows in 'Deployment' if Config SN and SN are blank is easy

I see what your saying without a reference point. I don't want to delete the rows just the info in the cells that could have copied over data. How about a separate Macro that looks at the Config SN and MFG Sn on the Deployment sheet. IF those are empty it clears that same cells as we are copying over too? So same macro but no ref to MAFFT just to itself and to clear/delete the cells contents if Config and MFG SN are empty or blank?

Quote:

---

Originally Posted by Paul_Hossler

If they delete the Config SN and SN for an entry on 'Deployment' but leave the other fields, there's no sure way to reference back to the source on MAFFT

Since someone could make manual changes to a 'Deployment' line, there's no easy way to see if something was copied over or manually changed/entered.

A second macro to delete rows in 'Deployment' if Config SN and SN are blank is easy

---

Added this bit of code to the macro to delete any 'copy' fields if the 'match' fields are blank. Any fields NOT on the 'copy' list are left

Code:

---

    'delete 'fields to copy' on Deployment if all the 'fields to match' are blank
    For iDeploy = 3 To rDeploy.Rows.Count
        If CheckBlanks(rDeploy.Rows(iDeploy)) Then
            For iCopy = LBound(aCopy, 1) + 1 To UBound(aCopy, 1)
                rDeploy.Cells(iDeploy, aCopy(iCopy, 2)).Clear
            Next iCopy
        End If
    Next iDeploy

---

Roger that thank you,

Quote:

---

Originally Posted by Paul_Hossler

Added this bit of code to the macro to delete any 'copy' fields if the 'match' fields are blank. Any fields NOT on the 'copy' list are left

Code:

---

    'delete 'fields to copy' on Deployment if all the 'fields to match' are blank
    For iDeploy = 3 To rDeploy.Rows.Count
        If CheckBlanks(rDeploy.Rows(iDeploy)) Then
            For iCopy = LBound(aCopy, 1) + 1 To UBound(aCopy, 1)
                rDeploy.Cells(iDeploy, aCopy(iCopy, 2)).Clear
            Next iCopy
        End If
    Next iDeploy

---

---

I found an slight issue... Column CD on the MAFFT Oracle Site Reference is a =Cell link. When it copies over to Column CZ on the Deployment it doesn't bring in the info but the =Cell link and the wrong one. It needs to pull either the info from the cell or the building info from the MAFFT Sheet.

Quote:

---

Originally Posted by Paul_Hossler

Added this bit of code to the macro to delete any 'copy' fields if the 'match' fields are blank. Any fields NOT on the 'copy' list are left

Code:

---

    'delete 'fields to copy' on Deployment if all the 'fields to match' are blank
    For iDeploy = 3 To rDeploy.Rows.Count
        If CheckBlanks(rDeploy.Rows(iDeploy)) Then
            For iCopy = LBound(aCopy, 1) + 1 To UBound(aCopy, 1)
                rDeploy.Cells(iDeploy, aCopy(iCopy, 2)).Clear
            Next iCopy
        End If
    Next iDeploy

---

---

I changed the copy loop to use the values

Code:

---

                For iCopy = LBound(aCopy, 1) + 1 To UBound(aCopy, 1)
                    rMAFFT.Cells(iMAFFT, aCopy(iCopy, 1)).Copy
                    rDeploy.Cells(iDeploy, aCopy(iCopy, 2)).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                    rDeploy.Cells(iDeploy, aCopy(iCopy, 2)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                Next iCopy

---

Very cool thanks for showing me that...