Using a variable to refer a cell

[jeam]

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....

 

[Tom Ogilvy]

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