VBA Needed - Search through a Sheet with 100 rows of Data, Identify "N" Indicators, Return Specific Information on Another Tab

[SanFelippo]

Hello all,

This might get a little complicated to explain, but I will do my best. I have two sheets in a workbook, one called “New Extract” and another called “Error Tabulation.” The “New Extract” tab contain 100 rows of data (101 if you include the header row), and 266 columns. The columns are setup so that the actual data is in the first column, and then the next column directly to the right is a “Validation” column that will have a “Y”, “N”, or a 0 in it. The pattern continues that way across the spreadsheet, data column,validation column, data column, validation column, etc. I will provide anexample at the bottom of this post.

On the “Error Tabulation” tab, I have the following column headers in this order starting in Cell A1:
Finding or Observation, HMDA Field, Observation, Application Number, Last Name (Applicant), Channel, Notes, Action Taken, Loan Purpose, Certified By, Certified Date,CBB Risk Comments, Follow-ups, CCS Response, CCS Comments.

The column headers I bolded in that list that are on the “Error Tabulation” tab can also be found on the “New Extract,” and given a certain situation, the information contained needs to be brought over to the “Error Tabulation” tab.

Here is where it may get difficult to explain what I need. On the “New Extract” tab, all of the “Validation” columns will be filled out with a “Y”, “N”, or a 0, indicating whether the information in the cell to the left of them is correct or not.
What I need is a macro written and attached to a button that can be pressed on the “Error Tabulation” tab that will search through those 100 rows of data on the “New Extract” tab and look for any “N” in the validation columns. In the event that an “N” is found in a row, I need the macro to do the following:

1. Grab the header name of the column to the left of the column it found the “N” in and paste it in cell B2 on the “Error Tabulation” tab.
2. Grab the Application Number and paste that in cell D2 on the "Error Tabulation" tab.
3. Grab the Last Name (Applicant) and paste that in cell E2 on the "Error Tabulation" tab.
4. Grab the Channel and paste that in cell F2 on the "Error Tabulation" tab.
5. Grab the Notes and paste that into cell G2 on the "Error Tabulation" tab.
6. Grab the Action Taken and paste that into cell H2 on the "Error Tabulation" tab.
7. Grab the Loan Purpose and paste that into cell I2 on the "Error Tabulation" tab.
8. Grab the Certified By and paste that into cell J2 on the "Error Tabulation" tab.
9. Grab the Certified Date and paste that into cell K2 on the "Error Tabulation" tab.
   

After it has finished doing that, I need the macro to continue on searching the row for any more “N” in the validation columns for that row. In the event that another “N” is found in the same row, then the macro will repeat the process I numbered above, but this time paste the data in the next row on the “Error Tabulation” tab. If no more “N” are found in the validation columns for that row, then it will move on to the next row and go through that one and so on and so forth until it reaches the final row.

Trying to explain how I need it to work without showing you guys makes it seem complicated but I don’t think it will be all that complicated to code. I will try to show below a simple example of the data I have setup, and what the “Error Tabulation” tab would look like if the macro was run. Note, I made this data up, so the column positions are not indicative of their true locations.

I was thinking since the headers for the data that needs tobe pulled over are named the exact same between the two tabs, I wouldn’t needto list out the exact columns the data resides in, but let me know if thatwould make it easier.


"New Extract Tab"
[TABLE="width: 1019"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A
[/TD]
[TD="width: 111, bgcolor: transparent"]B
[/TD]
[TD="width: 115, bgcolor: transparent"]C
[/TD]
[TD="width: 121, bgcolor: transparent"]D
[/TD]
[TD="width: 94, bgcolor: transparent"]E
[/TD]
[TD="width: 82, bgcolor: transparent"]F
[/TD]
[TD="width: 80, bgcolor: transparent"]G
[/TD]
[TD="width: 74, bgcolor: transparent"]H
[/TD]
[TD="width: 79, bgcolor: transparent"]I
[/TD]
[TD="width: 76, bgcolor: transparent"]J
[/TD]
[TD="width: 95, bgcolor: transparent"]K
[/TD]
[TD="width: 84, bgcolor: transparent"]L
[/TD]
[TD="width: 64, bgcolor: transparent"]M
[/TD]
[TD="width: 83, bgcolor: transparent"]N
[/TD]
[TD="width: 64, bgcolor: transparent"]O
[/TD]
[TD="width: 72, bgcolor: transparent"]P
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Certified Date
[/TD]
[TD="width: 111, bgcolor: transparent"]Certified Date Validation
[/TD]
[TD="width: 115, bgcolor: transparent"]Channel
[/TD]
[TD="width: 121, bgcolor: transparent"]Channel Validation
[/TD]
[TD="width: 94, bgcolor: transparent"]Application Number
[/TD]
[TD="width: 82, bgcolor: transparent"]Application Number Validation
[/TD]
[TD="width: 80, bgcolor: transparent"]Loan Purpose
[/TD]
[TD="width: 74, bgcolor: transparent"]Loan Purpose Validation
[/TD]
[TD="width: 79, bgcolor: transparent"]Last Name (Applicant)
[/TD]
[TD="width: 76, bgcolor: transparent"]Last Name (Applicant) Validation
[/TD]
[TD="width: 95, bgcolor: transparent"]Notes
[/TD]
[TD="width: 84, bgcolor: transparent"]Notes Validation
[/TD]
[TD="width: 64, bgcolor: transparent"]Action Taken
[/TD]
[TD="width: 83, bgcolor: transparent"]Action Taken Validation
[/TD]
[TD="width: 64, bgcolor: transparent"]Certified By
[/TD]
[TD="width: 72, bgcolor: transparent"]Certified By Validation
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]20180206
[/TD]
[TD="width: 111, bgcolor: transparent"]Y
[/TD]
[TD="width: 115, bgcolor: transparent"]ConsumerHELOC
[/TD]
[TD="width: 121, bgcolor: transparent"]Y
[/TD]
[TD="width: 94, bgcolor: transparent"]1
[/TD]
[TD="width: 82, bgcolor: transparent"]Y
[/TD]
[TD="width: 80, bgcolor: transparent"]1
[/TD]
[TD="width: 74, bgcolor: transparent"]N
[/TD]
[TD="width: 79, bgcolor: transparent"]Smith
[/TD]
[TD="width: 76, bgcolor: transparent"]Y
[/TD]
[TD="width: 95, bgcolor: transparent"]okay
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[TD="bgcolor: transparent"]JH
[/TD]
[TD="width: 72, bgcolor: transparent"]Y
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]20180205
[/TD]
[TD="width: 111, bgcolor: transparent"]Y
[/TD]
[TD="width: 115, bgcolor: transparent"]ConsumerHELOC
[/TD]
[TD="width: 121, bgcolor: transparent"]N
[/TD]
[TD="width: 94, bgcolor: transparent"]2
[/TD]
[TD="width: 82, bgcolor: transparent"]Y
[/TD]
[TD="width: 80, bgcolor: transparent"]4
[/TD]
[TD="width: 74, bgcolor: transparent"]Y
[/TD]
[TD="width: 79, bgcolor: transparent"]Joel
[/TD]
[TD="width: 76, bgcolor: transparent"]Y
[/TD]
[TD="width: 95, bgcolor: transparent"]no prob
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]N
[/TD]
[TD="bgcolor: transparent"]JH
[/TD]
[TD="width: 72, bgcolor: transparent"]Y
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]20180212
[/TD]
[TD="width: 111, bgcolor: transparent"]N
[/TD]
[TD="width: 115, bgcolor: transparent"]ConsumerHELOC
[/TD]
[TD="width: 121, bgcolor: transparent"]Y
[/TD]
[TD="width: 94, bgcolor: transparent"]3
[/TD]
[TD="width: 82, bgcolor: transparent"]Y
[/TD]
[TD="width: 80, bgcolor: transparent"]4
[/TD]
[TD="width: 74, bgcolor: transparent"]Y
[/TD]
[TD="width: 79, bgcolor: transparent"]Johnson
[/TD]
[TD="width: 76, bgcolor: transparent"]Y
[/TD]
[TD="width: 95, bgcolor: transparent"]sweet
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[TD="bgcolor: transparent"]JH
[/TD]
[TD="width: 72, bgcolor: transparent"]Y
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]20180213
[/TD]
[TD="width: 111, bgcolor: transparent"]N

[/TD]
[TD="width: 115, bgcolor: transparent"]ConsumerHELOC
[/TD]
[TD="width: 121, bgcolor: transparent"]N
[/TD]
[TD="width: 94, bgcolor: transparent"]4
[/TD]
[TD="width: 82, bgcolor: transparent"]Y
[/TD]
[TD="width: 80, bgcolor: transparent"]4
[/TD]
[TD="width: 74, bgcolor: transparent"]Y
[/TD]
[TD="width: 79, bgcolor: transparent"]Murphy
[/TD]
[TD="width: 76, bgcolor: transparent"]Y
[/TD]
[TD="width: 95, bgcolor: transparent"]jelly
[/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"]N
[/TD]
[TD="bgcolor: transparent"]JH
[/TD]
[TD="width: 72, bgcolor: transparent"]Y
[/TD]
[/TR]
</tbody>[/TABLE]

"Error Tabulation" Tab
[TABLE="width: 1021"]
<tbody>[TR]
[TD="width: 89, bgcolor: transparent"]A
[/TD]
[TD="width: 111, bgcolor: transparent"]B
[/TD]
[TD="width: 115, bgcolor: transparent"]C
[/TD]
[TD="width: 121, bgcolor: transparent"]D
[/TD]
[TD="width: 94, bgcolor: transparent"]E
[/TD]
[TD="width: 119, bgcolor: transparent"]F
[/TD]
[TD="width: 80, bgcolor: transparent"]G
[/TD]
[TD="width: 74, bgcolor: transparent"]H
[/TD]
[TD="width: 79, bgcolor: transparent"]I
[/TD]
[TD="width: 76, bgcolor: transparent"]J
[/TD]
[TD="width: 95, bgcolor: transparent"]K
[/TD]
[TD="width: 84, bgcolor: transparent"]L
[/TD]
[TD="width: 64, bgcolor: transparent"]M
[/TD]
[TD="width: 83, bgcolor: transparent"]N
[/TD]
[TD="width: 77, bgcolor: transparent"]O
[/TD]
[/TR]
[TR]
[TD="width: 89, bgcolor: transparent"]Finding or Observation
[/TD]
[TD="width: 111, bgcolor: transparent"] HMDA Field
[/TD]
[TD="width: 115, bgcolor: transparent"]Observation
[/TD]
[TD="width: 121, bgcolor: transparent"]Application Number
[/TD]
[TD="width: 94, bgcolor: transparent"]Last Name (Applicant)
[/TD]
[TD="width: 119, bgcolor: transparent"]Channel
[/TD]
[TD="width: 80, bgcolor: transparent"]Notes
[/TD]
[TD="width: 74, bgcolor: transparent"]Action Taken
[/TD]
[TD="width: 79, bgcolor: transparent"]Loan Purpose
[/TD]
[TD="width: 76, bgcolor: transparent"]Certified By
[/TD]
[TD="width: 95, bgcolor: transparent"]Certified Date
[/TD]
[TD="width: 84, bgcolor: transparent"]CBB Risk Comments
[/TD]
[TD="width: 64, bgcolor: transparent"]Follow-Up
[/TD]
[TD="width: 83, bgcolor: transparent"]CCS Response
[/TD]
[TD="width: 77, bgcolor: transparent"]CCS Comments
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Loan Purpose
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="width: 94, bgcolor: transparent"]Smith
[/TD]
[TD="width: 119, bgcolor: transparent"]ConsumerHELOC
[/TD]
[TD="width: 80, bgcolor: transparent"]okay
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="width: 79, bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]JH
[/TD]
[TD="width: 95, bgcolor: transparent"]20180206
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Channel
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 121, bgcolor: transparent"]2
[/TD]
[TD="width: 94, bgcolor: transparent"]Joel
[/TD]
[TD="width: 119, bgcolor: transparent"]ConsumerHELOC
[/TD]
[TD="width: 80, bgcolor: transparent"]no prob
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="width: 79, bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent"]JH
[/TD]
[TD="width: 95, bgcolor: transparent"]20180205
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Action Taken
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 121, bgcolor: transparent"]2
[/TD]
[TD="width: 94, bgcolor: transparent"]Joel
[/TD]
[TD="width: 119, bgcolor: transparent"]ConsumerHELOC
[/TD]
[TD="width: 80, bgcolor: transparent"]no prob
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="width: 79, bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent"]JH
[/TD]
[TD="width: 95, bgcolor: transparent"]20180205
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 111, bgcolor: transparent"]Certified Date
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]3
[/TD]
[TD="width: 94, bgcolor: transparent"]Johnson
[/TD]
[TD="width: 119, bgcolor: transparent"]ConsumerHELOC
[/TD]
[TD="width: 80, bgcolor: transparent"]sweet
[/TD]
[TD="bgcolor: transparent"]3
[/TD]
[TD="width: 79, bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent"]JH
[/TD]
[TD="width: 95, bgcolor: transparent"]20180212
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 111, bgcolor: transparent"]Certified Date
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]4
[/TD]
[TD="width: 94, bgcolor: transparent"]Murphy
[/TD]
[TD="width: 119, bgcolor: transparent"]ConsumerHELOC
[/TD]
[TD="width: 80, bgcolor: transparent"]jelly
[/TD]
[TD="bgcolor: transparent"]4
[/TD]
[TD="width: 79, bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent"]JH
[/TD]
[TD="width: 95, bgcolor: transparent"]20180213
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Channel
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]4
[/TD]
[TD="width: 94, bgcolor: transparent"]Murphy
[/TD]
[TD="width: 119, bgcolor: transparent"]ConsumerHELOC
[/TD]
[TD="width: 80, bgcolor: transparent"]jelly
[/TD]
[TD="bgcolor: transparent"]4
[/TD]
[TD="width: 79, bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent"]JH
[/TD]
[TD="width: 95, bgcolor: transparent"]20180213
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Action Taken
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]4
[/TD]
[TD="width: 94, bgcolor: transparent"]Murphy
[/TD]
[TD="width: 119, bgcolor: transparent"]ConsumerHELOC
[/TD]
[TD="width: 80, bgcolor: transparent"]jelly
[/TD]
[TD="bgcolor: transparent"]4
[/TD]
[TD="width: 79, bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent"]JH
[/TD]
[TD="width: 95, bgcolor: transparent"]20180213
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[SanFelippo]

This can be deleted, it has been solved