Retrieving Values from Cell with Macro

Anift · External Since: Apr 01, 2006 Posts: 9

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

Bob Phillips · External Since: May 25, 2006 Posts: 1027

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
>

Anift · External Since: Apr 01, 2006 Posts: 9

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 · External Since: Jul 08, 2005 Posts: 16264

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

Anift · External Since: Apr 01, 2006 Posts: 9

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
>