Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
Retrieving Values from Cell with Macro
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 |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
Retrieving Values from Cell with Macro
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 |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
Retrieving Values from Cell with Macro
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 |
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
Retrieving Values from Cell with Macro
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 |
#5
Posted to microsoft.public.excel.setup
|
|||
|
|||
Retrieving Values from Cell with Macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error when running cut & paste macro | Excel Worksheet Functions | |||
insert date | Excel Worksheet Functions | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Row Expansion | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions |