tfox
07-05-2011, 03:25 AM
Sub test1()
no_of_row = 10 'Specify total number of rows here
For cnt_col = 0 To 2
cnt_n1 = 0
cnt_n2 = 0
cnt_n3 = 0
cnt_n4 = 0
cnt_w1 = 0
cnt_w2 = 0
cnt_w3 = 0
cnt_w4 = 0
cnt_w1_ok = 0
cnt_w2_ok = 0
cnt_w3_ok = 0
cnt_w4_ok = 0
For cnt_row = 1 To no_of_row
'***Start of Week 1***
If (Day(Cells(cnt_row, 1 + (3 * cnt_col))) <= 7) And (Day(Cells(cnt_row, 1 + (3 * cnt_col)) >= 1)) Then
cnt_w1 = cnt_w1 + 1
If (Cells(cnt_row, 1 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "na") Then
cnt_n1 = cnt_n1 + 1
ElseIf (Cells(cnt_row, 1 + (3 * cnt_col)) = "") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "") Then
cnt_n1 = cnt_n1 + 1
ElseIf Cells(cnt_row, 1 + (3 * cnt_col)) >= Cells(cnt_row, 2 + (3 * cnt_col)) Then
cnt_w1_ok = cnt_w1_ok + 1
End If
'***Start of Week 2***
ElseIf (Day(Cells(cnt_row, 1 + (3 * cnt_col)) <= 14)) And (Day(Cells(cnt_row, 1 + (3 * cnt_col)) >= 8)) Then
cnt_w2 = cnt_w2 + 1
If (Cells(cnt_row, 1 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "na") Then
cnt_n2 = cnt_n2 + 1
ElseIf (Cells(cnt_row, 1 + (3 * cnt_col)) = "") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "") Then
cnt_n2 = cnt_n2 + 1
ElseIf Cells(cnt_row, 1 + (3 * cnt_col)) >= Cells(cnt_row, 2 + (3 * cnt_col)) Then
cnt_w2_ok = cnt_w2_ok + 1
End If
'***Start of Week 3***
ElseIf (Day(Cells(cnt_row, 1 + (3 * cnt_col)) <= 21)) And (Day(Cells(cnt_row, 1 + (3 * cnt_col)) >= 15)) Then
cnt_w3 = cnt_w3 + 1
If (Cells(cnt_row, 1 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "na") Then
cnt_n3 = cnt_n3 + 1
ElseIf (Cells(cnt_row, 1 + (3 * cnt_col)) = "") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "") Then
cnt_n3 = cnt_n3 + 1
ElseIf Cells(cnt_row, 1 + (3 * cnt_col)) >= Cells(cnt_row, 2 + (3 * cnt_col)) Then
cnt_w3_ok = cnt_w3_ok + 1
End If
'***Start of Week 4***
ElseIf (Day(Cells(cnt_row, 1 + (3 * cnt_col)) <= 31)) And (Day(Cells(cnt_row, 1 + (3 * cnt_col)) >= 22)) Then
cnt_w4 = cnt_w4 + 1
If (Cells(cnt_row, 1 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "na") Then
cnt_n4 = cnt_n4 + 1
ElseIf (Cells(cnt_row, 1 + (3 * cnt_col)) = "") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "") Then
cnt_n4 = cnt_n4 + 1
ElseIf Cells(cnt_row, 1 + (3 * cnt_col)) >= Cells(cnt_row, 2 + (3 * cnt_col)) Then
cnt_w4_ok = cnt_w4_ok + 1
End If
End If
Next cnt_row
Cells(no_of_row + 5, 2 + (3 * cnt_col)) = (100 * cnt_w1_ok) / (cnt_w1 - cnt_n1) & "%"
Cells(no_of_row + 6, 2 + (3 * cnt_col)) = (100 * cnt_w2_ok) / (cnt_w2 - cnt_n2) & "%"
Cells(no_of_row + 7, 2 + (3 * cnt_col)) = (100 * cnt_w3_ok) / (cnt_w3 - cnt_n3) & "%"
Cells(no_of_row + 8, 2 + (3 * cnt_col)) = (100 * cnt_w4_ok) / (cnt_w4 - cnt_n4) & "%"
Next cnt_col
End Sub
Hi everyone,
Could you help me? I can't figure out what is wrong with my codes. I tried using this on some data on Excel.
What I am trying to do is to get percentages from data arranged into 2 columns and 10 rows (and this is done for 3 sets of data). I have no problem with that.
What I have problem with is the nested IF loops. The data runs through the first IF and ELSEIF while ignoring the other ELSEIFs.
no_of_row = 10 'Specify total number of rows here
For cnt_col = 0 To 2
cnt_n1 = 0
cnt_n2 = 0
cnt_n3 = 0
cnt_n4 = 0
cnt_w1 = 0
cnt_w2 = 0
cnt_w3 = 0
cnt_w4 = 0
cnt_w1_ok = 0
cnt_w2_ok = 0
cnt_w3_ok = 0
cnt_w4_ok = 0
For cnt_row = 1 To no_of_row
'***Start of Week 1***
If (Day(Cells(cnt_row, 1 + (3 * cnt_col))) <= 7) And (Day(Cells(cnt_row, 1 + (3 * cnt_col)) >= 1)) Then
cnt_w1 = cnt_w1 + 1
If (Cells(cnt_row, 1 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "na") Then
cnt_n1 = cnt_n1 + 1
ElseIf (Cells(cnt_row, 1 + (3 * cnt_col)) = "") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "") Then
cnt_n1 = cnt_n1 + 1
ElseIf Cells(cnt_row, 1 + (3 * cnt_col)) >= Cells(cnt_row, 2 + (3 * cnt_col)) Then
cnt_w1_ok = cnt_w1_ok + 1
End If
'***Start of Week 2***
ElseIf (Day(Cells(cnt_row, 1 + (3 * cnt_col)) <= 14)) And (Day(Cells(cnt_row, 1 + (3 * cnt_col)) >= 8)) Then
cnt_w2 = cnt_w2 + 1
If (Cells(cnt_row, 1 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "na") Then
cnt_n2 = cnt_n2 + 1
ElseIf (Cells(cnt_row, 1 + (3 * cnt_col)) = "") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "") Then
cnt_n2 = cnt_n2 + 1
ElseIf Cells(cnt_row, 1 + (3 * cnt_col)) >= Cells(cnt_row, 2 + (3 * cnt_col)) Then
cnt_w2_ok = cnt_w2_ok + 1
End If
'***Start of Week 3***
ElseIf (Day(Cells(cnt_row, 1 + (3 * cnt_col)) <= 21)) And (Day(Cells(cnt_row, 1 + (3 * cnt_col)) >= 15)) Then
cnt_w3 = cnt_w3 + 1
If (Cells(cnt_row, 1 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "na") Then
cnt_n3 = cnt_n3 + 1
ElseIf (Cells(cnt_row, 1 + (3 * cnt_col)) = "") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "") Then
cnt_n3 = cnt_n3 + 1
ElseIf Cells(cnt_row, 1 + (3 * cnt_col)) >= Cells(cnt_row, 2 + (3 * cnt_col)) Then
cnt_w3_ok = cnt_w3_ok + 1
End If
'***Start of Week 4***
ElseIf (Day(Cells(cnt_row, 1 + (3 * cnt_col)) <= 31)) And (Day(Cells(cnt_row, 1 + (3 * cnt_col)) >= 22)) Then
cnt_w4 = cnt_w4 + 1
If (Cells(cnt_row, 1 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 1 + (3 * cnt_col)) = "na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "NA") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "Na") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "na") Then
cnt_n4 = cnt_n4 + 1
ElseIf (Cells(cnt_row, 1 + (3 * cnt_col)) = "") Or (Cells(cnt_row, 2 + (3 * cnt_col)) = "") Then
cnt_n4 = cnt_n4 + 1
ElseIf Cells(cnt_row, 1 + (3 * cnt_col)) >= Cells(cnt_row, 2 + (3 * cnt_col)) Then
cnt_w4_ok = cnt_w4_ok + 1
End If
End If
Next cnt_row
Cells(no_of_row + 5, 2 + (3 * cnt_col)) = (100 * cnt_w1_ok) / (cnt_w1 - cnt_n1) & "%"
Cells(no_of_row + 6, 2 + (3 * cnt_col)) = (100 * cnt_w2_ok) / (cnt_w2 - cnt_n2) & "%"
Cells(no_of_row + 7, 2 + (3 * cnt_col)) = (100 * cnt_w3_ok) / (cnt_w3 - cnt_n3) & "%"
Cells(no_of_row + 8, 2 + (3 * cnt_col)) = (100 * cnt_w4_ok) / (cnt_w4 - cnt_n4) & "%"
Next cnt_col
End Sub
Hi everyone,
Could you help me? I can't figure out what is wrong with my codes. I tried using this on some data on Excel.
What I am trying to do is to get percentages from data arranged into 2 columns and 10 rows (and this is done for 3 sets of data). I have no problem with that.
What I have problem with is the nested IF loops. The data runs through the first IF and ELSEIF while ignoring the other ELSEIFs.