Retrieving Values from Cell with Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Anift - 20 Nov 2006 08:26 GMT

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

Reply to this Message

Bob Phillips - 20 Nov 2006 10:15 GMT

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)

> 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".
[quoted text clipped - 11 lines]
>
> Can anybody help with the code?, any help will be appreciated

Reply to this Message

Anift - 20 Nov 2006 13:41 GMT

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?

> For Each cell In Worksheets("Sheet1").Range("B1:B20")
> If cell.Offset(0,1).Value = "Open" Then
[quoted text clipped - 28 lines]
> >
> > Can anybody help with the code?, any help will be appreciated

Reply to this Message

Dave Peterson - 20 Nov 2006 15:13 GMT

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

> HI Bob,
> Thanks for the reply,
[quoted text clipped - 35 lines]
> > >
> > > Can anybody help with the code?, any help will be appreciated

Signature

Dave Peterson

Reply to this Message

Anift - 21 Nov 2006 05:22 GMT

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

You guys Rock

Reply to this Message