Matching names

[DougP]

Please help! I work with large Excel spreadsheets with tons of columns of
data involving hospital inpatient census. I have been trying to find a way
to write in VBA a way to match the names of hospitals in eg. column C with
their nurses in eg column G. I can autofilter the names, etc, but can't
figure out how to match up all the hospitals with name MEDCENTRAL with nurse
JANE. Is this an IF/THEN function? If so, I can't get the syntax right.
My main problem seems to be isolating only the visible part of column G
when I autofilter and select the nurse name. Is there a function that
selects only the visible column after autofiltering?
Thanks in advance. Hope you all can help.
doug

 

[Bob Phillips]

Yes, say that the range being filtered is B1:B900, then

Range("B1:B900").SpecialCells(xlCellTypeVisible).Copy

will get those visible cells

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[DougP]

Bob, thanks a ton.
This actually worked once I figured out where in the macro I had to place
it. The whole thing is probably more cumbersome than it needs to be, but
will try it out for real at work tomorrow on my census spreadsheet with 800
rows or so.
Any other ideas are welcome, esp if I can do it with an IF/THEN...
doug

 

[Tom Ogilvy]

I don't know why you wouldn't want to use Bob's method, but if you want to
use if then

Dim rng as Range, cell as Range

for each cell in Range("B2:B900")
if cell.EntireRow.Hidden = False then
if rng is nothing then
set rng = Cell
else
set rng = union(rng,cell)
end if
end if
Next
if not rng is nothing then
rng.Select
end if

 

[DougP]

Thanks.
What I was actually asking is if there is a way to use an IF/THEN to do eg.
if a cell in column C is hospital A, then nurse in column G is B thruout the
whole spreadsheet.
But maybe that's too easy. I'm a noob when it comes to excel and VBA.
doug