CODEWITHSUNDEEP
vbarounding
SamL
SamL

Reputation: 11

VBA Rounding With Cell Values

I'm trying to round every populate cell in column AD:AD to 4 decimals and ends when the next cell is blank.

I thought something like this would work but it errors out on the cell.value.

Sub Round_4()
For Each cell In [AD:AD]
    If cell = "" Then Exit Sub
    cell.Value = WorksheetFunction.Round(cell.Value, 4)
Next cell

End Sub

Any suggestions?

Upvotes: 0

Views: 679

Answers (2)

jcrizk
jcrizk

Reputation: 615

You could do this:

Dim myCell As Range
Dim myRange As Range

Set myRange = Excel.Application.ThisWorkbook.Worksheets(worksheetNameGoesHereInDoubleQuotes).Range("AD:AD")

For Each myCell In myRange
    If Not IsEmpty(myCell) Then
        myCell.Value = Application.WorksheetFunction.Round(CDbl(myCell.Value), 4)
    'me being lazy with my range assignment
    ElseIf IsEmpty(myCell) Then
        Exit For
    End If
Next

Upvotes: 0

QHarr
QHarr

Reputation: 84465

You could work only down to the first empty cell with 

Range("AD1", Range("AD1").End(xlDown)).Value = Evaluate("round(" & Range("AD1", Range("AD1").End(xlDown)).Address & ",4)")

Note this is using Activesheet reference. You can wrap in a With statement giving the parent sheet.

Upvotes: 1

Related Questions