How to match cells in two columns

[DougP]

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

 

[DougP]

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 are:
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