Is there a way to both round and replace a column of numbers?
I have a user who has multiple columns, that contained formulas,
so I inserted and copied them to get just the values.
Turns out, the values are decimals, and that he was rounding.
So now, I have to insert a column to round and then insert/copy/paste
special to copy just the values. I have to do this for each column, of which
there are 10.
Is there a better way to do this?
Thanks,

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Abbey Normal" wrote in message
...
Is there a way to both round and replace a column of numbers?
I have a user who has multiple columns, that contained formulas,
so I inserted and copied them to get just the values.
Turns out, the values are decimals, and that he was rounding.
So now, I have to insert a column to round and then insert/copy/paste
special to copy just the values. I have to do this for each column, of
which
there are 10.
Is there a better way to do this?
Thanks,

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" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Abbey Normal" wrote in message
...
Is there a way to both round and replace a column of numbers?
I have a user who has multiple columns, that contained formulas,
so I inserted and copied them to get just the values.
Turns out, the values are decimals, and that he was rounding.
So now, I have to insert a column to round and then insert/copy/paste
special to copy just the values. I have to do this for each column, of
which
there are 10.
Is there a better way to do this?
Thanks,

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Abbey Normal" wrote in message
...
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" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Abbey Normal" wrote in message
...
Is there a way to both round and replace a column of numbers?
I have a user who has multiple columns, that contained formulas,
so I inserted and copied them to get just the values.
Turns out, the values are decimals, and that he was rounding.
So now, I have to insert a column to round and then insert/copy/paste
special to copy just the values. I have to do this for each column, of
which
there are 10.
Is there a better way to do this?
Thanks,

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" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Abbey Normal" wrote in message
...
Is there a way to both round and replace a column of numbers?
I have a user who has multiple columns, that contained formulas,
so I inserted and copied them to get just the values.
Turns out, the values are decimals, and that he was rounding.
So now, I have to insert a column to round and then insert/copy/paste
special to copy just the values. I have to do this for each column, of
which
there are 10.
Is there a better way to do this?
Thanks,

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Abbey Normal" wrote in message
...
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" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Abbey Normal" wrote in message
...
Is there a way to both round and replace a column of numbers?
I have a user who has multiple columns, that contained formulas,
so I inserted and copied them to get just the values.
Turns out, the values are decimals, and that he was rounding.
So now, I have to insert a column to round and then insert/copy/paste
special to copy just the values. I have to do this for each column, of
which
there are 10.
Is there a better way to do this?
Thanks,