Send your file to ME with an explanation.
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-----Original Message-----
From: Domain Admin
Sent: Monday, April 09, 2012 11:50 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
method?
That link fails. What you said below I think gives a range that
includes columns that have numbers I would not want rounded.
I can see how it would work I think to eliminate text columns for
example, but not numeric columns where rounding is wrong.
So still back to the best way to create a used range on each column?
On Mon, Apr 9, 2012 at 9:42 AM, dguillett1 <dguille...@gmail.com> wrote:
read this
http://support.microsoft.com/kb/194983f
simple change. Keep the special cells just in case or try without
For Each c In activesheet.usedrange
but blanks will now be 0 instead of blank
Sub roundvalues()
Dim c As Range
For Each c In activesheet.usedrange.SpecialCells(xlConstants, xlNumbers)
c.Value = Application.Round(c, 2)
‘OR vba round which may round down instead of rounding OFF
‘ c = Round(c, 2)
Next c
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-----Original Message----- From: Domain Admin
Sent: Monday, April 09, 2012 11:21 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
method?
All my values are numbers so can I eliminate the specialcells part?
To define the ranges can I use
set columnA = columns(worksheetfunction.match("columnA", .rows(1),0))
using the real name for each column and then use columnA as the
range?
But that would I think give me the entire column and I only need to
check the used range
The round I used is giving the correct round up at .005 so it is not
the VBA round I guess though I am not sure I see the distinction in
your example other
than the removal of application.
On Mon, Apr 9, 2012 at 7:21 AM, dguillett1 <dguille...@gmail.com> wrote:
Sub roundvalues()
Dim c As Range
For Each c In Range("a2:z22").SpecialCells(xlConstants, xlNumbers)
c.Value = Application.Round(c, 2)
‘OR vba round which may round down instead of rounding OFF
‘ c = Round(c, 2)
Next c
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: tangledweb
Sent: Sunday, April 08, 2012 5:42 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Could this loop be replaced by some range
method?
I am trying to get more used to using ranges instead of addressing
individual cells by indexes. The loop below is rounding off the entries
in
all the columns
where BarOpen for example is just the column index in the sheet. Is
there
a
way to do this more efficiently by using the used range portion of each
column?
In this case all the columns are the same length.
' Round off the raw data values
With Sheets(RawData)
For index = startrawdata To stoprawdata
Cells(index, BarOpen).Value =
WorksheetFunction.Round(.Cells(index, BarOpen).Value, 2)
Cells(index, BarHigh).Value =
WorksheetFunction.Round(.Cells(index, BarHigh).Value, 2)
Cells(index, BarLow).Value =
WorksheetFunction.Round(.Cells(index, BarLow).Value, 2)
Cells(index, BarClose).Value =
WorksheetFunction.Round(.Cells(index, BarClose).Value, 2)
Cells(index, StopCol).Value =
WorksheetFunction.Round(.Cells(index, StopCol).Value, 2)
Next index
End With
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
