changing a 2 decimal place number into a whole number in a macro

[sharpeye]

So I have an issue sorting data and then using conditional formatting on that data.
he data that I start with has numbers in a 2dp format and my conditional format rules work on whole numbers. I dont need the numbers to be 2dp and none of the conditional formatting works until I manually enter eg.74% where the data was origionally 74.31%.
All of my data is sorted into correct cells using a simple macro to copy data thats spread over several rows into a single row and then apply my formatting rules, just wondered if I could enter somehing into my macro that would copy from the source cell as 2dp but paste in the destinaion cell as a whole number, then my conditional formaing would work as it should do.
Any ideas?

 

[Michael M]

Maybe you could post the macro you are currently using ??

 

[joeu2004]

First of all, 74% is not a "whole number". It is a percentage with 0 dp; maybe we can call it a "whole percentage". But it is a number with 2 dp. It is the same as 0.74.

Likewise, 74.31% is not a number with 2 dp. It is a number with 4 dp; also a percentage with 2 dp. It is the same as 0.7431.

So rounding a percentage (number) to 2 dp effectively rounds it to a "whole percentage".

The following might do something like what you want. I'm sure because I don't fully understand your requirements.

Select the range to be copied and modified. Then:

Dim v As Variant, r As Long, c As long
v = Selection
For r = 1 to UBound(v, 1): For c = 1 to Ubound(v, 2)
   v(r, c) = WorksheetFunction.Round(v(r, c), 2)
Next c, r
' if you want to replace Selection with the rounded numbers....
Selection = v

FYI, I use WorksheetFunction.Round (Excel ROUND) instead of VBA Round because the latter rounds differently. VBA Round uses so-called "banker's rounding", which rounds half up or down to an even value.

 

[sharpeye]

Thanks for your replies. Apologies, you are right as far as the number types go, Yes they are percentages which means as numbers they are indeed already at 2dp after I remove the extra 2dp I dont want.
The macro tha I use is simply copy from one cell and paste in another. When I recieve my data there are 29 cells that contain data and they are arranged across 7 rows.My macro basically rearranges those 29 cells of data into a single row and finally uses the format painter to copy my conditional formatting rules from a template row onto the row that has just gone through the macro.
What I hoping would be possible would be to insert a line or two into my macro so tha it will copy a cell that may contain 74.31% and paste 74% into the destination cell so that the formatting rules will then work.
This could also be relevant, if the cell I copy is 74.31% I would to paste 74%, if the cell I copy is 74.81%, I still only need to paste 74% so I dont need it to round up at all, this is where I got he whole number bit from, my bad

Thanks again

 

[mikerickson]

If DestinationRange is the range of cells with those data, perhaps

With DestinationRange
    .Parent.Parent.PrecisionAsDisplayed = True
    .NumberFormat = "0%"
    .Parent.Parent.PrecisionAsDisplayed = False
End With

 

[joeu2004]

With DestinationRange
    .Parent.Parent.PrecisionAsDisplayed = True
    .NumberFormat = "0%"
    .Parent.Parent.PrecisionAsDisplayed = False
End With

That is extremely ill-advised, IMHO. It is so risky that if we did it manually, Excel would display the warning "Data will permanently lose accuracy". But not just the data in DestinationRange as intended, but all constant data in the entire workbook (.Parent.Parent) that is formatted to display less precision. That can have a ripple effect on all dependent calculations.

(It also affects all calculated values in the entire workbook that are formatted to display less precision. But those changes are reversible when "Precision As Displayed" is disabled. Since the code above automatically disables PAD, the changes to calculated values will be invisible and temporary in this case.)

If you choose to experiment with setting PAD, be sure to make a backup copy of the Excel first, so that you can restore the original data if and when you discover some unintended consequences of setting PAD.

 

[joeu2004]

My macro basically rearranges those 29 cells of data into a single row and finally uses the format painter to copy my conditional formatting rules from a template row onto the row that has just gone through the macro. What I hoping would be possible would be to insert a line or two into my macro so tha it will copy a cell that may contain 74.31% and paste 74% into the destination cell

I provided one paradigm for doing that, namely to use WorksheetFunction.Round(...,2). The key is to round to 2 dp (percentage with no decimal places).

If you are unable to adapt that paradigm to your code, provide the entire code (or at least all relevant code fragments, including relevant Dim and ReDim statements) so that we can provide a turnkey solution. Be sure to use "code tags" around the VBA code ("#" icon on the applet toolbar).

if the cell I copy is 74.31% I would to paste 74%, if the cell I copy is 74.81%, I still only need to paste 74% so I dont need it to round up at all

If you are saying that you want to always round down, simply use WorksheetFunction.RoundDown(...,2) instead of WorksheetFunction.Round(...,2).

 

[sharpeye]

Thanks for your help and suggesions, I have found another way to acheive the results I need. When I run my macro to sort the cells into a single row and apply the condiional formating, my percentage cells, even though they contain percentages to 2dp I disply the results without the stray bits on the end. After each row of data is sorted, my macro then copies the entire row and I then manually paste that row into WPS spreadsheet as this will paste every cell as it appears with no conditional formatting commands, (thats quite cool.)
What I have decided would be easiest is to then copy all the rows back into excel as this will now also display the percentage cells in the format I need and then reapply the formatting commands, it works a treat and takes all of 30 seconds, no need to mess with my macro.
Once again, many thanks