Both Round And Replace

[Guest]

Is there a way to both round and replace a column of numbers?

 

[Don Guillett]

Sub roundreplace()'for one
Range("c4:c5").Value = Application.Round(Range("c4:c5").Value, 2)
End Sub

Sub roundreplacemultiple()
For i = 3 To 5 'colums c:e
lr = Cells(Rows.Count, i).End(xlUp).Row
Range(Cells(4, i), Cells(lr, i)).Value = _
Application.Round(Range(Cells(4, i), _
Cells(lr, i)).Value, 2)
Next i
End Sub

 

[Guest]

Hmm. You should know although I've done some VBA in Access, I'm a real novice
to Excel. I couldn't get this to work. What I was did was:
Pasted the code into a module. Then from the worksheet I selected alt + F8
and selected the sub I wanted, chose Run. But the values remained the same.
Am I doing something wrong? THanks for your help.

 

[Don Guillett]

I assume you wanted to use the second macro. After installing in a module
you need to modify to suit YOUR specifics. As written it was to round &
value columns c, d, e from row 4 to the last row in each column.

If all else fails send me a workbook along with detailed instructions of
what you want with before/after examples.

 

[Guest]

yes, maybe its the worksheet itself. I checked the help from what you
posted, and added this and then it worked:
Sub RoundReplace()
For Each c In Worksheets("Allocation Parameters").Range("c2:c74")
c.Value = Application.Round((c.Value), 0)
Next c
End Sub

Is there a way I can prompt for the Range & Worksheet so I can make it a
generic macro? If you can point me to what I can search for it might already
be posted.
Thank you for your help!

 

[Don Guillett]

As I mentioned in my FIRST macro you can do the whole thing at once instead
of cell by cell

Sub roundreplace()'for one
worksheet("yours").Range("c2:c74").Value = _
Application.Round(worksheet("yours").Range("c4:c5").Value, 2)
End Sub
As to the prompt you could put in an input box asking for the sheet and the
range or put into a cell and use that reference in the macro. Specifics??