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

**pawcoyote** · 03-17-2017, 01:34 PM

Hi, For some reason the this code isn't showing the Sheet where the info is missing as I thought it would.. It is just showing the following:

error_msg.jpg

MsgBox colHeader & " not found in row " & colRow & " on " & sh.Name
colNumber = CVErr(xlErrValue)

Originally Posted by Paul_Hossler

I changed the copy loop to use the values

                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

**pawcoyote** · 03-17-2017, 02:05 PM

One more things sorry.. I would like to be able to copy cells from the Deployment Sheet to the MAFFT as well. I added the info on the Sample8.

**pawcoyote** · 03-28-2017, 07:50 AM

Hi,

I would like to also be able to copy over these columns from the Deployment Sheet to the MAFFT Sheet based off of the same criteria as copying from the MAFFT to Deployment. Deployment sheet Headers, Company, First Name, Last Name, Address1, Address2, City, State, Zip, Phone, E-Mail Web Address from the Deployment Sheet to the MAFFT Sheet with Column Headers that are equal. See Sample 8... I would like this to be a Macro and not an automatic item..

Originally Posted by Paul_Hossler

I changed the copy loop to use the values

                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

**Paul_Hossler** · 03-28-2017, 12:04 PM

Originally Posted by pawcoyote

Hi, For some reason the this code isn't showing the Sheet where the info is missing as I thought it would.. It is just showing the following:

1. You hadn't asked for the sheet names. Didn't seem necessary since the macro only copies from MAFFT to Deployment, and only if the link field(s) do not have matching values you get the error message

2. Change to this

        s = "No match found on " & wsDeploy.Name & " for ... " & vbCrLf & vbCrLf & vbCrLf
        For i = 2 To wsCrossRef.Cells(1, 1).End(xlDown).Row
            s = s & wsCrossRef.Cells(i, 1) & " = " & rMAFFT.Cells(iMAFFT, aMatch(i, 1)) & vbCrLf
        Next I
        MsgBox s & vbCrLf & " from " & wsMAFFT.Name

3. I think this is what you were asking for

Capture.JPG

**pawcoyote** · 03-28-2017, 12:15 PM

I will try that and also if you could look at the new thing I am trying to do with copying from deployment to MAFFT...

**pawcoyote** · 03-29-2017, 10:01 AM

Hi, I found that when I remove either the Config SN or MFG SN on the Deployment Sheet it doesn't remove or clear the data from the cells when I run the macro. I fixed the Zip code issue. Also on the Copy of the other cells back to the MAFFT. I would love your feedback.

Originally Posted by Paul_Hossler

1. You hadn't asked for the sheet names. Didn't seem necessary since the macro only copies from MAFFT to Deployment, and only if the link field(s) do not have matching values you get the error message

2. Change to this

        s = "No match found on " & wsDeploy.Name & " for ... " & vbCrLf & vbCrLf & vbCrLf
        For i = 2 To wsCrossRef.Cells(1, 1).End(xlDown).Row
            s = s & wsCrossRef.Cells(i, 1) & " = " & rMAFFT.Cells(iMAFFT, aMatch(i, 1)) & vbCrLf
        Next I
        MsgBox s & vbCrLf & " from " & wsMAFFT.Name

3. I think this is what you were asking for

Capture.JPG

**pawcoyote** · 04-03-2017, 06:27 AM

Good morning,

I would like to try to do the following if possible.

1. If you remove Config SN or MFG SN on the Deployment Sheet I would like for it to remove or clear the data that was imported in from the MAFFT tool (Fields in the CrossRef Sheet) when I run the macro.
2. I would like to also be able to copy over these columns from the Deployment Sheet to the MAFFT Sheet based off of the same criteria as copying from the MAFFT to Deployment. Deployment sheet Headers, Company, First Name, Last Name, Address1, Address2, City, State, Zip, Phone, E-Mail Web Address from the Deployment Sheet to the MAFFT Sheet with Column Headers that are equal. See Sample9... I would like this to be a Macro and not an automatic item.

**Paul_Hossler** · 04-04-2017, 08:39 AM

Logic check - Is this correct?

1. Run macro MAFFT2Development

2. After current logic, add new logic to

If you remove Config SN or MFG SN on the Deployment Sheet I would like for it to remove or clear the data that was imported in from the MAFFT tool (Fields in the CrossRef Sheet) when I run the macro.

3. On Deployment if "Configured Serial #" OR "Mfg. Configured Serial #" is blank, then how to identify the lines on Deployment since those are the two link fields?

I would like to also be able to copy over these columns from the Deployment Sheet to the MAFFT Sheet based off of the same criteria as copying from the MAFFT to Deployment. Deployment sheet Headers, Company, First Name, Last Name, Address1, Address2, City, State, Zip, Phone, E-Mail Web Address from the Deployment Sheet to the MAFFT Sheet with Column Headers that are equal.

4. Is this a new macro?

5. Is this the logic:?

If 'Deployment' [Configured Serial #] = 'MAAFT' [Oracle Config Serial Number (custom12)] AND 'Deployment' [Mfg. Serial Number] = 'MAAFT' [Serial Number] Then

Copy the Col D fields for that line from Deployment to MAAFT

?

**pawcoyote** · 04-04-2017, 09:53 AM

I hope this helps.

Logic check - Is this correct?

I had to update the Config Serial Number header name and I had to move column to a new location. “Config Serial Number (custom12)” (Sample10)

1. Run macro MAFFT2Development

2. After current logic, add new logic to

If you remove Config SN or MFG SN on the Deployment Sheet I would like for it to remove or clear the data that was imported in from the MAFFT tool (Fields in the CrossRef Sheet) when I run the macro.

3. On Deployment if " Oracle Config Serial Number (custom12)" OR "Mfg. Serial Number” is blank, then how to identify the lines on Deployment since those are the two link fields?

3.a. If "Oracle Config Serial Number (custom12)" or "Mfg. Serial Number" on the Deployment sheet are Blank then clear.contents (not formatting) in the fields listed in the CrossRef Sheet (Column C). This can be a separate Macro. IT does not have to be in the MAFFT2Development Macro.

I would like to also be able to copy over these columns from the Deployment Sheet to the MAFFT Sheet based off of the same criteria as copying from the MAFFT to Deployment. Deployment sheet Headers, Company, First Name, Last Name, Address1, Address2, City, State, Zip, Phone, E-Mail Web Address from the Deployment Sheet to the MAFFT Sheet with Column Headers that are equal.

4. Is this a new macro?

4.a. Yes new macro to keep it simple unless building it into the first Macro is easier.

5. Is this the logic:?

Same logic as the MAFFT2Development Macro, If 'Deployment' [Oracle Config Serial Number (custom12)]= 'MAAFT' [Oracle Config Serial Number (custom12)] AND 'Deployment' [Mfg. Serial Number] = 'MAAFT' [Serial Number] Then

Copy the Col D fields on the CrossRef Sheet for that match from Deployment to MAAFT

**Paul_Hossler** · 04-06-2017, 09:08 AM

I tried running the macro and the column headers were changed

I stopped after fixing the first 3:

Capture.JPG

MAFFT col header is 'Device', not 'Devices'
Deployment and MAFFT col headers were 'Montly' and not 'Monthly'
Deployment and MAFFT col header were 'Site/Location', and not 'Site Location'

Since the macros match on column headers to copy data, they have to be matched

**pawcoyote** · 04-06-2017, 11:20 AM

That is very odd I only changed one header I will fix and repost. I am sorry about that...

Originally Posted by Paul_Hossler

I tried running the macro and the column headers were changed

I stopped after fixing the first 3:

Capture.JPG

MAFFT col header is 'Device', not 'Devices'
Deployment and MAFFT col headers were 'Montly' and not 'Monthly'
Deployment and MAFFT col header were 'Site/Location', and not 'Site Location'

Since the macros match on column headers to copy data, they have to be matched

**pawcoyote** · 04-10-2017, 07:39 AM

I have updated the sample to have the correct headers on both sheets.

I would like to keep this simple. I would like to add the Copy from the Deployment Sheet to the MAFFT Sheet based on the same criteria as the original copy and paste. I identified the fields in the CrossRef tab which fields I wish to copy from the Deployment to the MAFFT.

**pawcoyote** · 04-13-2017, 01:47 PM

Hi Paul, I added an updated Sample 11 in a previous post. I hope that help. All I want to do is be able to do is create another macro to copy the items in the CrossRef Column 4 from Deployment to MAFFT.

Originally Posted by Paul_Hossler

I tried running the macro and the column headers were changed

I stopped after fixing the first 3:

Capture.JPG

MAFFT col header is 'Device', not 'Devices'
Deployment and MAFFT col headers were 'Montly' and not 'Monthly'
Deployment and MAFFT col header were 'Site/Location', and not 'Site Location'

Since the macros match on column headers to copy data, they have to be matched

**Paul_Hossler** · 04-15-2017, 06:30 AM

OK

Didn't forget, just busy

I started to pull out all the common code make it general

Probably soon

**pawcoyote** · 04-16-2017, 11:06 AM

Hi not a problem I totally understand. I just wanted to be sure I kept updating as I made a change.

**snb** · 04-16-2017, 12:33 PM

Is this a question or an assignment ? (to be paid for)

**Paul_Hossler** · 04-17-2017, 06:19 AM

Originally Posted by snb

Is this a question or an assignment ? (to be paid for)

https://en.wikipedia.org/wiki/Feature_creep

This started out as using a simple event handler to take a US State in one column and enter an associated US Region in another

http://www.vbaexpress.com/forum/show...ered&highlight=

There have more than a few additional requests

I don't mind implementing them (on a 'when I get around to it' basis) but if I had known the final (OK, current) scope I would have either

1. Have passed it by, or

2. Planned the architecture a lot better

I really don't mind doing it, but had I had a better long range view of the requirements, I would have done things a lot more gracefully

**snb** · 04-17-2017, 09:12 AM

The absence of any input from the part of the OP stikes me as non compatible with the goal of a forum.

**Paul_Hossler** · 04-19-2017, 07:17 PM

@pawcoyote --

I generalized the original sub

There's Deployment2MAFFT and MAFFT2Deployment to check out

Please use enough real / representitive data to check it out

You have line numbers in col A and formulas to the far right in otherwise empty lines which cause a lot of extra lines to be checked. The line numbers in A I can handle but do you need the far right formulas?

**pawcoyote** · 04-20-2017, 05:32 AM

Good morning,

No I do not need the formulas and the line numbers are not necessary as well... Thank you!

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

Thread Tools

Display

Posting Permissions