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

SanFelippo

Board Regular
Joined
Apr 4, 2017
Messages
124
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"
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Certified Date
Certified Date Validation
Channel
Channel Validation
Application Number
Application Number Validation
Loan Purpose
Loan Purpose Validation
Last Name (Applicant)
Last Name (Applicant) Validation
Notes
Notes Validation
Action Taken
Action Taken Validation
Certified By
Certified By Validation
20180206
Y
ConsumerHELOC
Y
1
Y
1
N
Smith
Y
okay
Y
1
Y
JH
Y
20180205
Y
ConsumerHELOC
N
2
Y
4
Y
Joel
Y
no prob
Y
1
N
JH
Y
20180212
N
ConsumerHELOC
Y
3
Y
4
Y
Johnson
Y
sweet
Y
3
Y
JH
Y
20180213
N

ConsumerHELOC
N
4
Y
4
Y
Murphy
Y
jelly
Y
4
N
JH
Y

<tbody>
</tbody>

"Error Tabulation" Tab
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
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-Up
CCS Response
CCS Comments
Loan Purpose
1
Smith
ConsumerHELOC
okay
1
1
JH
20180206
Channel
2
Joel
ConsumerHELOC
no prob
1
4
JH
20180205
Action Taken
2
Joel
ConsumerHELOC
no prob
1
4
JH
20180205
Certified Date
3
Johnson
ConsumerHELOC
sweet
3
4
JH
20180212
Certified Date
4
Murphy
ConsumerHELOC
jelly
4
4
JH
20180213
Channel
4
Murphy
ConsumerHELOC
jelly
4
4
JH
20180213
Action Taken
4
Murphy
ConsumerHELOC
jelly
4
4
JH
20180213

<tbody>
</tbody>

 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top