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" <dpeters9@nc.rr.com> wrote in message
news:r50ke.41246$vi2.1753536@twister.southeast.rr.com...
> 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
>
>

[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

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:Olfxp9tXFHA.2740@TK2MSFTNGP14.phx.gbl...
> 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" <dpeters9@nc.rr.com> wrote in message
> news:r50ke.41246$vi2.1753536@twister.southeast.rr.com...
>> 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
>>
>>
>
>

[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" <dpeters9@nc.rr.com> wrote in message
news:dP9ke.41858$vi2.1814175@twister.southeast.rr.com...
> 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
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:Olfxp9tXFHA.2740@TK2MSFTNGP14.phx.gbl...
> > 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" <dpeters9@nc.rr.com> wrote in message
> > news:r50ke.41246$vi2.1753536@twister.southeast.rr.com...
> >> 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
> >>
> >>
> >
> >
>
>

[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

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:eOqIARzXFHA.1384@TK2MSFTNGP09.phx.gbl...
>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" <dpeters9@nc.rr.com> wrote in message
> news:dP9ke.41858$vi2.1814175@twister.southeast.rr.com...
>> 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
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:Olfxp9tXFHA.2740@TK2MSFTNGP14.phx.gbl...
>> > 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" <dpeters9@nc.rr.com> wrote in message
>> > news:r50ke.41246$vi2.1753536@twister.southeast.rr.com...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>