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
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
I assume you are talking about the Pass & Fail in column I of sheet 2.
QuoteThere 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.
Hi, yes that is correct. But I already have the numbers on the front sheet if that helps.
OK I see it now, try this
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
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
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?
It is not clear what the % column in the "KPI 14 - Notice management" table is to be a percentage of what out of what?
Yes, that is very confusing. Forget that part and I'll try to fix it.
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.
Try the attached, click the button to calculate all the percentages (except KPI14).
Code assigned to the button is
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
Note I changed the Formatting of all the percentage cells to Percentage with 0 decimal places.
That amazing.....thanks KJ.
Really appreciate your help.
Cheers, Cobber
You're welcome.
Don’t have an account yet? Register yourself now and be a part of our community!