UsedRange?

[Steve]

morning all.
I'm trying to modify an old macro that iterates through a UsedRange of cells.
My code is:

If ActiveCell <> "" Then
For i = 0 To UsedRange 'Step UsedRange

ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate

'my later procedures go here....

Next i

End If

So far, this only steps 1 cell, and stops. I need it to step through all
cells in a used range. However, when I use "Step UsedRange" it runs out to
the very end of the worksheet--- 1mRows.
When I check the actual used rows, it stops at row 287. I.e., I did ctrl +
End, and that's where it stopped.
My UsedRange will vary from worksheet to worksheet.
What am I doing wrong here?
Thank you, in advance.

 

[joel]

For each cell in UsedRange 'Step UsedRange
If Cell <> "" Then
'Use Cell as the object not activecell
'my later procedures go here....

end if

Next cell

 

[Aaron]

Hi Steve,

Check this out,
http://www.tek-tips.com/faqs.cfm?fid=2112

I use this for usedrange...I find it more reliable

Maybe you could alter the function to get it to return your last row and
lastcoumn or even return the last cell(lowest and most right cell) by
concatenating the lastrow and lastcolumn strings

And then your For loop could become something like,

For i=0 to LastRow
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
...rest of code
Next i

Hopefully this is something like what you were looking for

 

[Steve]

hi again Joel.
Ok, here's where I get stuck.

I tried it exactly as you've written it, and found that it rejects cell, as
not having been declared-- requires object.
So, I then declared cell as object.
It then rejects it as not having been defined, so I then do the following.
set cell = nothing
set cell = activecell
It then rejects UsedRange, stating it too requires an object.
So, I then define it as follows
Set UsedRange = ActiveSheet.UsedRange

At which point, the basics appear to work.
I then run the remainder, and it does not offset through each cell.
So, I put in
cell.offset(rowoffset:=1, columnoffset:=0).select
And use the balance of your Numplaces in the number format post
I get two undesired results.
1- instead of starting atthe cell that I have selected, it starts at A1, and
iterates through columns, within the UsedRange of the entire worksheet, one
row at a time.
2- it only sets the first selection's decimal place format, and leaves all
subsequent cells alone.

The full code as follows.

Sub DecimalCk()
Dim cell As Object
Set cell = Nothing
Set cell = ActiveCell

Set UsedRange = ActiveSheet.UsedRange

For Each cell In UsedRange

cell.Offset(1, 0).Select

If IsNumeric(cell) Then

NumPlaces = Len(cell.value) - InStr(cell.Text, ".")
cell.NumberFormat = "0." & String(NumPlaces, "0")


'---------------------------------------------
NumPlaces = Format(cell, NumPlaces)

'I placed this in here because it seemed to only change the number format
of the
'first selection, no subsequent selections. It didn't help.
'-------------------------------------------------------

End If
Next cell

End Sub