If statement challenge

[sunshine076]

Is there a way to modify the if statements to help reduce the cluttered mess for the if statements. I am going to be adding more if statements to compensate for more data. Below is the program that I currently have:

Private Sub process(ByVal myRow As Integer, myCol As Integer)
Dim ce As String
Dim AA As Range
Dim d As Integer
Dim e As Integer
Dim BB As Range
d = 4
ce = "y"
Application.EnableEvents = False
ActiveSheet.Unprotect Password:=P
Do
Set AA = Range(Cells(8, d), Cells(1000, d))
Cells(4, d).Value = Application.WorksheetFunction.CountIf(AA, ce)
d = d + 1
Loop Until d = 12
Set AA = Nothing
If Cells(4, 4) > 0 Then Cells(5, 4).Value = Cells(3, 2).Value / Cells(4, 4).Value / 100
If Cells(4, 5) > 0 Then Cells(5, 5).Value = Cells(3, 2).Value / Cells(4, 5).Value / 100
If Cells(4, 6) > 0 Then Cells(5, 6).Value = Cells(3, 2).Value / Cells(4, 6).Value / 100
If Cells(4, 7) > 0 Then Cells(5, 7).Value = Cells(3, 2).Value / Cells(4, 7).Value / 100
If Cells(4, 8) > 0 Then Cells(5, 8).Value = Cells(3, 2).Value / Cells(4, 8).Value / 100
If Cells(4, 9) > 0 Then Cells(5, 9).Value = Cells(3, 2).Value / Cells(4, 9).Value / 100
If Cells(4, 10) > 0 Then Cells(5, 10).Value = Cells(3, 2).Value / Cells(4, 10).Value / 100
If Cells(4, 11) > 0 Then Cells(5, 11).Value = Cells(3, 2).Value / Cells(4, 11).Value / 100
ActiveSheet.Protect Password:=P
Application.EnableEvents = True
End Sub

 

[chergh]

Change the max value of "i" in the code below to what you need it to be.

Private Sub process(ByVal myRow As Integer, myCol As Integer)
Dim ce As String
Dim AA As Range
Dim d As Integer
Dim e As Integer
Dim BB As Range
d = 4
ce = "y"
Application.EnableEvents = False
ActiveSheet.Unprotect Password:=P
Do
Set AA = Range(Cells(8, d), Cells(1000, d))
Cells(4, d).Value = Application.WorksheetFunction.CountIf(AA, ce)
d = d + 1
Loop Until d = 12
Set AA = Nothing

for i = 4 to 11

If Cells(4, i) > 0 Then Cells(5, i).Value = Cells(3, 2).Value / Cells(4, i).Value / 100

next i

ActiveSheet.Protect Password:=P
Application.EnableEvents = True
End Sub

 

[Yarp]

chergh, nice edit, just saw it in time

 

[sunshine076]

thanks I will try it

 

[sunshine076]

none of the above counts are working any suggestions and it is duplicating cells in excel?

 

[chergh]

none of the above counts are working any suggestions?

Care to explain yourself more so it makes sense?

 

[sunshine076]

sorry. What I have going on in excel is the countif that will calculate the number of Y in a particular column and then in another cell it keeps track of the total number. The countif is no longer working therefore not allowing the countif and percentage to change with the new code. When I look at the excel sheet instead of seeing one row of data entry I am seeing two rows of the same data.

 

[chergh]

Theres nothing in your code posted above that is going to cause the issues you have just described.

 

[sunshine076]

Nevermind I figured out what I had done wrong. Thanks for your help

 

[sunshine076]

I think it might have been a glitch in my system causing the duplication.