Can anyone help? Please?

[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

[K Dales]

It would really help if we could see the code you have so far. You should be
able to incorporate some error checking yourself without having to use On
Error, but in order to see how to do this I would need to see how you are
currently doing the loop.

"DougP" wrote:

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

[Tom Ogilvy]

If you want to find the rows where Hospital is in Column H and Nurse is in
Column J, then you should use an autofilter.

If you want to summarize some numerical data (such as hours worked for
example) based on hospital and nurse, then you should use a pivot table.
If you just want to group the data, you should sort on hospital and then
nurse and use the subtotal command under the Data menu.

If you want to search the spreadsheet using some type of looping structure
to find the single instance (or even multiple instances) where Nancy is
listed in the same row with Hospital A, then you can use the code shown in
the VBA help for the FindNext command. This would show how to fine all the
instances of Hospital A in column H for example. for each find of column A,
you would simply check for Nancy in Column J of that row and stop when you
found her.

If none of these give you any ideas, then you need to post back with a much
more specific description of what you are trying to do.

--
Regards,
Tom Ogilvy


"DougP" <[email protected]> wrote in message
news:[email protected]...
> 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

"DougP" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>