Click here to Skip to main content
15,877,967 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Workbook in Sheet1 Column I in Cell Value is 0.06% and more on how to convert percentage format to Number Format after convert number format cell value is 0.06.

But I got the result of below code as 0.00. I need output of result as 0.06.

What I have tried:

' Set the worksheet where you want to make the conversion
   Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name.
   ' Find the last row in column I
   lastRow = ws.Cells(ws.Rows.Count, "I").End(xlUp).Row

   ' Loop through each cell in column I
   For Each cell In ws.Range("I1:I" & lastRow)
       If cell.HasFormula = False Then ' Check if the cell
                                       ' doesn't contain a formula
           If cell.NumberFormat = "0.00%" Then ' Check if the cell is
                                               ' in percentage format
               cell.Value = Round(cell.Value / 100, 2) ' Convert percentage
                             ' to decimal and round to two decimal places
               cell.NumberFormat = "0.00" ' Set the number format
                                          ' to two decimal places
           End If
       End If
   Next cell
Updated 8-Sep-23 10:44am

Your cell contains 0.06.
Your conversion is this:
cell.Value = Round(cell.Value / 100, 2)
The result is 0.00 because 0.06 divided by 100 is 0.0006, and when you round that to 2 digits you get 0.00

If you want "6" in the cell, then multiply by 100 instead of dividing ...
Share this answer
The value 0.06 divided by 100 equals 0.0006. And that value rounded to two decimal places would display as 0.00.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900