OK, I give up. I have tried all sorts of different statements, eg.
for-each, if-then, etc but just can't quite seem to get there.
My problem is this: I work with large spreadsheets, with hospitals in one
column, and nurses in another column. I want to match a nurse with a
hospital, eg. if a cell in column H is Hospital A, then nurse in the cell in
column J is Nancy. I can select, do an if-then and isolate the visible
cells in column H, but I need a way to write the macro so that I catch each
hospital and nurse, but some days a hospital may not be there, so I need a
way to move to the next hospital without it causing an error and stopping
the sub. If I use "on error resume next" I end up with an endless loop, or
a whole column of Nancy's. That's really my main problem.
Any ideas?
I would be most grateful for any solutions you can give me. Mucho thanks in
advance.
DougP

I assume you have observations with hospital name, nurse name and other
information. What do you want to know when you evaluate the data? You
haven't really stated that.

"DougP" wrote in message
om...
OK, I give up. I have tried all sorts of different statements, eg.
for-each, if-then, etc but just can't quite seem to get there.
My problem is this: I work with large spreadsheets, with hospitals in
one
column, and nurses in another column. I want to match a nurse with a
hospital, eg. if a cell in column H is Hospital A, then nurse in the cell
in
column J is Nancy. I can select, do an if-then and isolate the visible
cells in column H, but I need a way to write the macro so that I catch
each
hospital and nurse, but some days a hospital may not be there, so I need a
way to move to the next hospital without it causing an error and stopping
the sub. If I use "on error resume next" I end up with an endless loop,
or
a whole column of Nancy's. That's really my main problem.
Any ideas?
I would be most grateful for any solutions you can give me. Mucho thanks
in
advance.
DougP

Have you considered VLOOKUP() or an INDEX()/MATCH() combination?
Here's how to use a VLOOKUP():

1. Create a backup of your data so you can recover from a disaster.
2. Insert a new tab called "Reference" in your spreadsheet, and create
a table like this one, in the range A1:B6 :
Hospital Nurse
Hospital A Ann
Hospital B Brianna
Hospital C Claire
Hospital D Denise
Hospital E Elizabeth
3. In your main sheet, column J, where you need a nurse's name, enter
this formula:
=VLOOKUP(A2,Reference!$A$2:$B$6,2,0)

If you go this route, you'll need to expand the range in your formula
to accommodate the rows in your Reference table. This should do it for
you. Have a good weekend!

Thank you all so much for the replies. A couple of you sent some really
good suggestions, but I can see I was not specific enough.
What I am trying to do is write a macro that will search thru the
spreadsheet and assign "Nancy" in column J for each instance of Hospital A
in column H.

Here is an example of what I've written so far, repeated for each different
hospital:

Selection.AutoFilter field:=8, Criteria1:="AVENTURA HOSPITAL AND MEDICAL
CENTER"
Range("J:J").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Offset(1,
0).SpecialCells(xlCellTypeVisible).Select
ActiveCell.FormulaR1C1 = "SMITH,JACQUELINE A."
Selection.FillDown
Selection.AutoFilter field:=8

This actually works, but if "AVENTURA etc" is not on the census that day,
the whole thing stops at line 3.
If I use "On Error Resume Next", I get a constant looping of line 2.
So, the questions a
1) Is there a more efficient way to automate matching nurses to
hospitals (there are probably around 30 hospitals and 8 nurses)?
2) How can I get the sub to move on to the next hospital, if the first
hospital is not present in the spreadsheet that day?

Again, thanks for all the help, and hope you can help me further on this.
It really takes a fair amount of time to manually go thru a spreadsheet of
700-800 rows and match these up manually.
DougP


"DougP" wrote in message
om...
OK, I give up. I have tried all sorts of different statements, eg.
for-each, if-then, etc but just can't quite seem to get there.
My problem is this: I work with large spreadsheets, with hospitals in
one
column, and nurses in another column. I want to match a nurse with a
hospital, eg. if a cell in column H is Hospital A, then nurse in the cell
in
column J is Nancy. I can select, do an if-then and isolate the visible
cells in column H, but I need a way to write the macro so that I catch
each
hospital and nurse, but some days a hospital may not be there, so I need a
way to move to the next hospital without it causing an error and stopping
the sub. If I use "on error resume next" I end up with an endless loop,
or
a whole column of Nancy's. That's really my main problem.
Any ideas?
I would be most grateful for any solutions you can give me. Mucho thanks
in
advance.
DougP

Will this work for you? You'll have to un-hid, un-filter, etc.

sub Hosp_Name()
range ("j1").select 'or enter your preferred starting point

do until activecell.value = "" 'this code will run until a blank cell
is found
' if you don't like the blank cell method, try
' do until activecell.value = "stop"
' which will run until it finds the word "stop" that you have to enter

if range("h" & selection.row).value = "Aventura" then
activecell.value = "Nancy"
activecell.offset(1,0).select
loop
end sub

This is a simplified version, which will work one-at-a-time, but it
doesn't work for you because you have 30 hospitals and 8 nurses. You
could expand this code to include an array, and then for each row loop
through the array to match the names, (this is chunky and ugly) use
this code and 30 IF statements.

Easily enough done, but is code easier than the VLOOKUP solution?