array and displaying count

[Ruth_C]

I have a worksheet with 3000 rows. I would like to set the initial value of
cells in columns C,d,e,f to 0.00. I am using the code below which works
fine. Is there a way of doing this in one line or using an array instead of
how I am doing it. I would also like to print on the screen the value of
count but I do not know how to return this and have it show on the screen for
the user.

Sub initializeCells()
Dim i, count As Integer
v = 0#
n = Cells(Rows.count, "C").End(xlUp).Row
For i = 13 To n
Cells(i, "C").Value = v
Cells(i, "D").Value = v
Cells(i, "E").Value = v
Cells(i, "F").Value = v
count = i

Next

End Sub

Ruth_C

 

[Mike H]

Hi,

Maybe this

Sub initializeCells()
v = 0#
n = Cells(Rows.count, "C").End(xlUp).Row
Range("C13:F" & n).Value = v
End Sub

Mike

 

[paul.robinson]

Hi
As well as Mikes reply :
1. your dim statement
Dim i, count As Integer

will make i a variant. You want
Dim i as integer, count As Integer

If you have a large indeterminate number of rows you might also be
better off with
Dim i as Long, count As Long

You might also want to steer clear of using count as a variable name
since it is also a method .

On showing the count, you could simply pick a cell like B1 and do
Range("B1").Value = n

or you could display a message box

Msgbox "Your Count is " & n, "Information on your row count"

regards
Paul

 

[Mike H]

Hi,

Sorry I missed the second bit of displaying the value of count (or i). In
your code it's a variable and it will change so fast all the user would end
up seeing is the final value of count which would be the same as the value of
n found at the beginning of the code.

To display that try

range("A1").value=n

Mike

 

[Ruth_C]

Thank you Mike, both parts work perfectly and the code looks much better.

 

[Mike H]

Glad I could help and thanks for the feedback

Thank you Mike, both parts work perfectly and the code looks much better.