Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a variable to refer a cell
hi frns
I have a simple question, yet hard to answer for me :) ... assume that we have a code sth like sub countcells() dim n as variant dim counter as integer Worksheets("data").Select For Each n In ActiveSheet.Range("e:e") If n.Value < 0 Then counter = counter + 1 End If Next n End sub as u can see we are counting the cells that are not equal to zero on the column "e" here. now i gotta use that counter to refer to a cell. it sud be sth like ex: [a1:a+] here plus sud be the counter that i define .... what i wanna do is to copy the cells which have a value. But range may differ everyday. So i sud count them. i have searched for all kinds of references but cudn't find a solution... any help appreciated frnds ... thx u all.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a variable to refer a cell
No, you don't need to count them
Worksheets("data").Select set rng = Range(Cells(1,"E"),Cells(rows.count,"E").End(xlup) ) For Each n In ActiveSheet.Range("e:e") If n.Value < 0 Then cells(2,"A").EntireRow.copy Destination:= _ worksheets("NewSheet").Cells(rows.count, _ "A").End(xlup))(2) End If Next n End sub Hopefully you can adapt this to your problem. If not, post back. If you wanted to check a condition in a, it would be Dim rng as Range, n as Range Worksheets("data").Select set rng = Range(Cells(1,"E"),Cells(rows.count,"E").End(xlup) ) For Each n In rng If lcase(n.Offset(0,-4).Value) = "copy" Then cells(2,"A").EntireRow.copy Destination:= _ worksheets("NewSheet").Cells(rows.count, _ "A").End(xlup))(2) End If Next n End sub as an example. (to copy all rows where column A contained the single entry: Copy -- Regards, Tom Ogilvy "jeam" wrote in message oups.com... hi frns I have a simple question, yet hard to answer for me :) ... assume that we have a code sth like sub countcells() dim n as variant dim counter as integer Worksheets("data").Select For Each n In ActiveSheet.Range("e:e") If n.Value < 0 Then counter = counter + 1 End If Next n End sub as u can see we are counting the cells that are not equal to zero on the column "e" here. now i gotta use that counter to refer to a cell. it sud be sth like ex: [a1:a+] here plus sud be the counter that i define .... what i wanna do is to copy the cells which have a value. But range may differ everyday. So i sud count them. i have searched for all kinds of references but cudn't find a solution... any help appreciated frnds ... thx u all.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How make hyperlink refer to cell content rather than cell address. | Excel Discussion (Misc queries) | |||
variable to refer to "this worksheet"? | Excel Worksheet Functions | |||
refer cell using c | Excel Programming | |||
How to refer a Variable across Workbooks ? | Excel Programming | |||
Refer to Combo-box using a part + variable | Excel Programming |