excelvba

Copy a percentage value from a cell to another

In the given sheet, I have a sales target achieved %, and a corresponding bonus %.
enter image description here

I want to check the % in sheet1 column AI, and find the corresponding %.

For a2 = 3 To lastRowx
    If CInt(x.Sheets("Sheet1").Range("AI" & CStr(a2)).Value) <= 0.8 Then
        pc = 0.5
    ElseIf CInt(x.Sheets("Sheet1").Range("AI" & CStr(a2)).Value) >= 1.5 Then
        pc = 5
    Else
        Set n3 = x.Sheets("Payout Curve").Range("A4:A74").Find(What:=x.Sheets("Sheet1").Range("AI" & CStr(a2)).Value, LookAt:=xlWhole)
        MsgBox x.Sheets("Sheet1").Range("AI" & CStr(a2)).Value
        'x.Sheets("Sheet1").Range("AT" & CStr(a2)).Value = x.Sheets("Payout Curve").Range("B" & CStr(n3.Row)).Value
    End If
Next a2

It is taking the value as decimal, instead of a percentage.
84% should return 60% but it is detected as a 0.6.

Solution

  • Yes percentage values are in fact decimal values and hence excel treats them as such. % means nothing more than *(1/100). 80% is just another way to display 0.8. If you work with %values you'll always work with decimal values.

    If you have a (decimal) value in a cell and want the decimal value to be shown in the xx% format, you'll just have to change the Cells NumberFormat - The underlying value will always be a decimal, but the way it is displayed can be changed.

    You could even do this with some VBA code. Changing the NumberFormat property in order to display decimals as percent values can be done as shown below:

    Sub setFormatToPercent(cell As Range)
    cell.NumberFormat = "0%"
End Sub