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.... |
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.... |
All times are GMT +1. The time now is 10:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com