Help!

Retrieving Values from Cell with Macro

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Setup RSS
Next:  Comboboxes  
Author Message
Anift
External


Since: Apr 01, 2006
Posts: 9



PostPosted: Mon Nov 20, 2006 5:26 am    Post subject: Retrieving Values from Cell with Macro
Archived from groups: microsoft>public>excel>setup (more info?)

Hi I want to write a macro code to retrieve the value in a Cell in Range( B1
: B20) if the corresponding Value in C1:C20 is " Open".
The Value should be displayed in a Range ( A1: A20) in a differnt worksheet
of the same file

For E.G B1 = Name1 C1 = Closed
B2 = Name2 C2 = Open
B3 = Name3 C3= Open
...... ......
...... ......

Now in Other sheet, I want to import all the values of Cell in range B1:20
if the corresponding value in Cell range C1:C20 is " Open"

Can anybody help with the code?, any help will be appreciated
Back to top
Bob Phillips
External


Since: May 25, 2006
Posts: 1027



PostPosted: Mon Nov 20, 2006 3:15 pm    Post subject: Re: Retrieving Values from Cell with Macro [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

For Each cell In Worksheets("Sheet1").Range("B1:B20")
If cell.Offset(0,1).Value = "Open" Then
i = i + 1
cell.copy Worksheets("Sheet2").Cells(i,"A").Value
End If
Next cell

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Anift" wrote in message

> Hi I want to write a macro code to retrieve the value in a Cell in
Range( B1
> : B20) if the corresponding Value in C1:C20 is " Open".
> The Value should be displayed in a Range ( A1: A20) in a differnt
worksheet
> of the same file
>
> For E.G B1 = Name1 C1 = Closed
> B2 = Name2 C2 = Open
> B3 = Name3 C3= Open
> ...... ......
> ...... ......
>
> Now in Other sheet, I want to import all the values of Cell in range B1:20
> if the corresponding value in Cell range C1:C20 is " Open"
>
> Can anybody help with the code?, any help will be appreciated
>
Back to top
Anift
External


Since: Apr 01, 2006
Posts: 9



PostPosted: Mon Nov 20, 2006 3:15 pm    Post subject: Re: Retrieving Values from Cell with Macro [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

HI Bob,
Thanks for the reply,
It throwing an error "object doesnt support property or method " on
cell.copy Worksheets("Sheet2").Cells(i,"A").Value

any suggestions?

"Bob Phillips" wrote:

> For Each cell In Worksheets("Sheet1").Range("B1:B20")
> If cell.Offset(0,1).Value = "Open" Then
> i = i + 1
> cell.copy Worksheets("Sheet2").Cells(i,"A").Value
> End If
> Next cell
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Anift" wrote in message
>
> > Hi I want to write a macro code to retrieve the value in a Cell in
> Range( B1
> > : B20) if the corresponding Value in C1:C20 is " Open".
> > The Value should be displayed in a Range ( A1: A20) in a differnt
> worksheet
> > of the same file
> >
> > For E.G B1 = Name1 C1 = Closed
> > B2 = Name2 C2 = Open
> > B3 = Name3 C3= Open
> > ...... ......
> > ...... ......
> >
> > Now in Other sheet, I want to import all the values of Cell in range B1:20
> > if the corresponding value in Cell range C1:C20 is " Open"
> >
> > Can anybody help with the code?, any help will be appreciated
> >
>
>
>
Back to top
Dave Peterson
External


Since: Jul 08, 2005
Posts: 16264



PostPosted: Mon Nov 20, 2006 3:15 pm    Post subject: Re: Retrieving Values from Cell with Macro [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Try dropping that .Value at the end of the "cell.copy ..." line.

Anift wrote:
>
> HI Bob,
> Thanks for the reply,
> It throwing an error "object doesnt support property or method " on
> cell.copy Worksheets("Sheet2").Cells(i,"A").Value
>
> any suggestions?
>
> "Bob Phillips" wrote:
>
> > For Each cell In Worksheets("Sheet1").Range("B1:B20")
> > If cell.Offset(0,1).Value = "Open" Then
> > i = i + 1
> > cell.copy Worksheets("Sheet2").Cells(i,"A").Value
> > End If
> > Next cell
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > "Anift" wrote in message
> >
> > > Hi I want to write a macro code to retrieve the value in a Cell in
> > Range( B1
> > > : B20) if the corresponding Value in C1:C20 is " Open".
> > > The Value should be displayed in a Range ( A1: A20) in a differnt
> > worksheet
> > > of the same file
> > >
> > > For E.G B1 = Name1 C1 = Closed
> > > B2 = Name2 C2 = Open
> > > B3 = Name3 C3= Open
> > > ...... ......
> > > ...... ......
> > >
> > > Now in Other sheet, I want to import all the values of Cell in range B1:20
> > > if the corresponding value in Cell range C1:C20 is " Open"
> > >
> > > Can anybody help with the code?, any help will be appreciated
> > >
> >
> >
> >

--

Dave Peterson
Back to top
Anift
External


Since: Apr 01, 2006
Posts: 9



PostPosted: Tue Nov 21, 2006 2:22 am    Post subject: RE: Retrieving Values from Cell with Macro [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Thanks Dave,
that worked, Thanks to Bob as well.

You guys Rock


"Anift" wrote:

> Hi I want to write a macro code to retrieve the value in a Cell in Range( B1
> : B20) if the corresponding Value in C1:C20 is " Open".
> The Value should be displayed in a Range ( A1: A20) in a differnt worksheet
> of the same file
>
> For E.G B1 = Name1 C1 = Closed
> B2 = Name2 C2 = Open
> B3 = Name3 C3= Open
> ...... ......
> ...... ......
>
> Now in Other sheet, I want to import all the values of Cell in range B1:20
> if the corresponding value in Cell range C1:C20 is " Open"
>
> Can anybody help with the code?, any help will be appreciated
>
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Setup All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum