Populating MS Excel columns based on wildcard search of column A 1

[BxWill]

Currently, I am reviewing for 5 conditions in column A and would like to populate Columns B through D - on a daily basis;

SearchForInColA---Column B ----Column C--------Column D
Abd Pain----------AbdomenPain--LowPriority-----Don't Call
Fever-------------Fever--------NormalPriority--Call
Cough-------------Cough--------LowPriority-----Don't Call
Headach-----------Headache-----NormalPriority--Don't Call
Sickle------------SickleCell---HighPriority----Call

Interested in using VBA in Excel 2007 to automate the process but contemplating if this can be performed more efficiently using a MS Access 2007 database.

The daily MS Excel file contains 3000+ records.

The list of conditions is expected to increase to approximately 20 within the next month.

Thanks in advance for any additional insight.

 

[idbr]

Hi BxWill,

How about using a template list?

Set up a sheet with your SearchForInColA---Column B ----Column C--------Column D values listed in sequence.

' *** air code ***

dim varfind
dim intRow as integer
dim i as integer

' Loop through he values on the data sheet. 

for i = 2 to sheets("MySheet").range("A1").currentregion.rows.count

' For each column A value, find this on the template list. 

set varfind = sheets("MyTemplateSheet").find what := sheets("MySheet").range("A"&i)

set intRow = varfind.row

sheets("MySheet").range("B" & i) = sheets("MyTemplateSheet").range("B" & intRow)

next i

Should do the job fairly simply.

Things to watch out for include handling not-found values.

Regards, Iain

 

[BxWill]

Still not able to automate the population of columns "B" through "D", as planned.

Did construct a "template list" similar to;

PartialName---FullName----Priority-----Call_NoCall
fev-----------Fever-------Normal-------Call
feve----------Fever-------Normal-------Call
fever---------Fever-------Normal-------Call
.
.
.

At this point, it appears that incorporating the filter function would be the way to go instead of a template list that would be time consuming to maintain considering that I would need to include all possible spelling combinations of a medical condition.

For example, the procedure that I am using currently is to filter column "A" to determine if it contains "fev" and then toggling over to column "B" and selecting "fever" from the validation list and then copy/paste down the column. Then, repeating this process for column "C"...

Any further thoughts?