Need help creating VBA or a tool for Excel project

VegaOne16

New Member
Joined
Sep 6, 2009
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm working on a project and wanted to see if someone could help me, either by using VBA or another way.

I'll have data in a worksheet name "Data" in Column C.

Lets say column C contains the following:
Row 1 = Bank of America
Row 2 = Company Deposit - John Doe Company
Row 3 = Denny's

I want excel to be able to do the following for me in Column A of the "Data" worksheet:
(#1) If cell in column C is blank, skip this row and go to the next row.
(#2) If cell in Column C contains (have it search through all search criteria on another worksheet named "Search Criteria), then put in this cell what is listed in column B of the "Search Criteria" worksheet.
(#3) If excel can't find any matching criteria on the "Search Criteria" worksheet, then enter the word "Manual" into the cell.

Note: I don't want it looking for an exact match. So if cell C said, "Bank of America", and I had "America" listed on the "Search Criteria" tab, I'd want it to pull over the data.

So lets say I'll have another worksheet called "Search Criteria" with the following criteria and results:

|Col A|--------------|Col B|
|America|------------|1000|
|Wal-Mart|-----------|2000|
|Company Deposit|---| 3000|

1) So I'll push a button, excel will go down each row in column A of the "data" worksheet...
2) if the cell in column C of that row is blank it'll just go to the next row...
3) if there is data in column C of that row, it will search column A on the "Search Criteria" worksheet and if it finds the criteria, it will take the result of column B of that worksheet and pop that into the cell of the "Data" worksheet...
4) if it doesn't find any matching data in the search criteria, I want it to put the word "Manual" into the cell on the "data" worksheet.

The end result will be on the "Data" worksheet:
|Col A|---|Col B|--|Col C|
|1000|----||-------- |Bank of America|
|3000|----||-------- |Company Deposit - John Doe Company|
|Manual|--||--------|Denny's|

Is something like this possible?

I'm using excel 2007.

Thank you for your help!
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

MikeWx

Active Member
Joined
Jan 16, 2010
Messages
436
Hi VegaOne16,

Welcome to the message board.

I have the search criteria in a separate sheet of the same workbook, and called the sheet Search Criteria.

Search Criteria


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 117px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>America</TD><TD style="TEXT-ALIGN: right">1000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Wal-Mart</TD><TD style="TEXT-ALIGN: right">2000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Company Deposit</TD><TD style="TEXT-ALIGN: right">3000</TD></TR></TBODY></TABLE>


This is what my Data sheet looks like:

Data


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 251px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">1000</TD><TD></TD><TD>Bank of America</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">3000</TD><TD></TD><TD>Company Deposit - John Doe Company</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>MANUAL</TD><TD></TD><TD>Denny's</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">2000</TD><TD></TD><TD>Africa Wal-Mart</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

My array formula (press Control Shift Enter after entering the forumula) in Cell A2 is:

Code:
=IF(C2="","",IFERROR(INDEX('Search Criteria'!$B$2:$B$4,SMALL(IF(ISERROR(SEARCH('Search Criteria'!$A$2:$A$4,C2)),"",ROW($A$1:$A$3)),1)),"MANUAL"))

Is this what you are trying to do?

Mike
 

VegaOne16

New Member
Joined
Sep 6, 2009
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hi Mike,

Thanks for your response.

Could you walk me through the keystrokes you made on how you got the numbers to auto populate in Col A of the data tab please.

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,461
Messages
5,596,276
Members
414,051
Latest member
tabecker

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
Top