PDA

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



pawcoyote
03-13-2017, 08:37 AM
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.

SamT
03-13-2017, 10:53 AM
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

SamT
03-13-2017, 11:29 AM
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

pawcoyote
03-13-2017, 01:33 PM
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..
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

pawcoyote
03-13-2017, 01:47 PM
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..

SamT
03-13-2017, 04:37 PM
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

Paul_Hossler
03-13-2017, 07:34 PM
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




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.Trans pose(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.Trans pose(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

pawcoyote
03-14-2017, 06:49 AM
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..

Paul_Hossler
03-14-2017, 07:28 AM
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

pawcoyote
03-14-2017, 07:51 AM
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.
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

Paul_Hossler
03-14-2017, 09:38 AM
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

pawcoyote
03-14-2017, 09:45 AM
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....

pawcoyote
03-14-2017, 12:25 PM
BTW love the new message!
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

Paul_Hossler
03-14-2017, 01:41 PM
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

pawcoyote
03-15-2017, 06:11 AM
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?
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

Paul_Hossler
03-16-2017, 11:09 AM
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





'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

pawcoyote
03-16-2017, 11:34 AM
Roger that thank you,
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





'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

pawcoyote
03-16-2017, 11:45 AM
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.
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





'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

Paul_Hossler
03-16-2017, 11:59 AM
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, 07:46 AM
Very cool thanks for showing me that...

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:

18670


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


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..

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
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

18795

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.
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

18795

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:

18873

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...
I tried running the macro and the column headers were changed

I stopped after fixing the first 3:

18873

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.
I tried running the macro and the column headers were changed

I stopped after fixing the first 3:

18873

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
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/showthread.php?58303-VBA-Code-to-Match-US-Region-to-the-State-that-is-entered&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!

Paul_Hossler
04-20-2017, 06:13 AM
Line numbers are easy to handle -- the function FindLastRowWithData





Function LastRowWithData(ws As Worksheet, Optional MaxNumCellsUsed As Long = 1) As Long
Dim i As Long

For i = ws.UsedRange.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(ws.Rows(i)) > MaxNumCellsUsed Then
LastRowWithData = i
Exit Function
End If
Next i
End Function


'backs up' the used range until it finds a row with more than MaxNumCellsUsed (default = 1) of cells with something in them and assumes that that is the row where data ends

The For loops than can stop checking at that row since that's the end of 'meaningful' data

pawcoyote
04-20-2017, 06:31 AM
Ok I get that.. Roger that...

Paul_Hossler
04-20-2017, 04:27 PM
OK,

1. When I made the Deployment2MAFFT macro from the MAAFT2Deployment, I didn't reverse everything that should have been

2. It looks like the format changed a little, and that messed up the Const values that hold the headers in the module Globals

Try this version -- it should be closer

pawcoyote
04-21-2017, 08:57 AM
Ok, I added in data on both sheets 41 on MAFFT and 68 on the deployment. The Copy from the MAFFt to the Deployment works great! But I get an error when I run the Deployment2MAFFT Macro.


IF iMAFFT = 4 And iDeploy = 10 THEN Stop

I am not sure what that is looking at and why the error...

Paul_Hossler
04-21-2017, 08:59 AM
Oops - my bad - I forgot to delete that

That was for debugging - just delete the line

pawcoyote
04-21-2017, 09:10 AM
Roger that, one question what was that looking at so I can go to the sheets and see what data it was looking at. Edification. :-)

pawcoyote
04-21-2017, 09:30 AM
I have been testing. I am getting the following errors when I go to run the Macro to copy the information from the Deployment to the MAFFT.

1898618987

It seems like it is looking at column BJ Contract Type on the Deployment sheet for some data.

Paul_Hossler
04-21-2017, 12:44 PM
Roger that, one question what was that looking at so I can go to the sheets and see what data it was looking at. Edification. :-)


Row 4 on MAFFT and row 10 on Deployment were a match on the 2 two fields, but were not copying the fields over

I stopped the macro there and then single stepped through to see why the fields were not being copied over

That's how I found the line where I missed reversing the MAFFT and Deployment references

Paul_Hossler
04-21-2017, 01:08 PM
I have been testing. I am getting the following errors when I go to run the Macro to copy the information from the Deployment to the MAFFT.

It seems like it is looking at column BJ Contract Type on the Deployment sheet for some data.


Polished the error message wording a little bit and corrected the text index

18993