VBA percentage code required

cobber1418 · Jun 20th 2019

Hi everybody, I need a VBA macro percentage code for all the cells on the attached worksheet. There are 2 types I need, one has total, pass and fail, the others just pass and fail.

I will be very grateful, cheers. Cobber

**KjBox** · Jun 20th 2019

I assume you are talking about the Pass & Fail in column I of sheet 2.

Quote

There are 2 types I need, one has total, pass and fail, the others just pass and fail.

Not sure what you mean by that, please explain.

cobber1418 · Jun 20th 2019

Hi, yes that is correct. But I already have the numbers on the front sheet if that helps.

**KjBox** · Jun 20th 2019

OK I see it now, try this

Code

Sub percent_calculations()
    Dim x, i As Long
    
    With Sheet1.Cells(5, 4).Resize(36, 4)
        x = .Value
        For i = 1 To UBound(x, 1)
            x(i, 4) = vbNullString
            If x(i, 1) <> "" And x(i, 1) <> 0 Then
                If IsNumeric(x(i, 1)) Then
                    x(i, 4) = (x(i, 2) * 100) / x(i, 1)
                End If
            End If
        Next
        .Value = x
    End With
    
End Sub

Display More

cobber1418 · Jun 20th 2019

Thanks KJ, unfortunately I'm a rank beginner and I can't get it to work.

Is your code calculating the percentages required in column G, or just converting the number to a percentage format after the number is calculated?

Also, I assume it is not calculating all the other cells I require.

Cheers, cobber

**KjBox** · Jun 20th 2019

You had column G formatted as Number with zero decimal places so my code just puts a number in column G which represents the percentage of Passes out of Total.

Ahh you mean you need all the % columns on Sheet 1 calculating?

**KjBox** · Jun 20th 2019

It is not clear what the % column in the "KPI 14 - Notice management" table is to be a percentage of what out of what?

cobber1418 · Jun 20th 2019

Yes, that is very confusing. Forget that part and I'll try to fix it.

cobber1418 · Jun 20th 2019

I meant forget KPI 14 part.

And yes, I need all the % columns on Sheet 1 calculating?

I got this to work for J column and the other smaller calculations but it is a very long way to do it and it gives #DIV/0! when there is no data to calculate.

Code

Range("J5").Select
ActiveCell.FormulaR1C1 = "=(RC[-2]/(RC[-2]+RC[-1]))*100"

**KjBox** · Jun 20th 2019

Try the attached, click the button to calculate all the percentages (except KPI14).

Code assigned to the button is

Code

Sub percent_calculations()
    Dim x, i As Long, ii As Long
    
    With Sheet1
        Application.ScreenUpdating = 0
        With .Cells(5, 4).Resize(32, 4)
            x = .Value
            For i = 1 To UBound(x, 1)
                x(i, 4) = vbNullString
                If x(i, 1) <> "" And x(i, 1) <> 0 Then
                    If IsNumeric(x(i, 1)) Then
                        x(i, 4) = Format(x(i, 2) / x(i, 1), "0%")
                    End If
                End If
            Next
            .Value = x
        End With
        For ii = 8 To 55 Step 3
            With .Cells(5, ii).Resize(23, 3)
                x = .Value
                For i = 1 To UBound(x, 1)
                    x(i, 3) = vbNullString
                    If x(i, 1) <> "" And x(i, 1) <> 0 Then
                        If IsNumeric(x(i, 1)) Then
                            x(i, 3) = Format(x(i, 1) / (x(i, 1) + x(i, 2)), "0%")
                        End If
                    End If
                Next
                .Value = x
            End With
        Next
    End With
    
End Sub

Display More

**KjBox** · Jun 20th 2019

Note I changed the Formatting of all the percentage cells to Percentage with 0 decimal places.

cobber1418 · Jun 20th 2019

That amazing.....thanks KJ.

Really appreciate your help.

Cheers, Cobber

**KjBox** · Jun 20th 2019

You're welcome.

VBA percentage code required

Participate now!

Share