Problem with If...Then...ElseIf Loop

[tfox]

[vba]
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

[/vba]
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.

[Bob Phillips]

Got a workbook that you can post?

[Frosty]

Impossible to give you concrete ideas without seeing at least dummy data.

Very very difficult to troubleshoot complex logic statements when you don't use option explicit.

You could definitely benefit from some modularization of your code, a la:

You may be able to fix further just by simplifying some of your structure.
[vba]
Option Explicit
Sub test1()
Dim no_of_row As Long
Dim cnt_Col As Long
Dim cnt_row As Long
Dim cnt_n1 As Double
Dim cnt_n2 As Double
Dim cnt_n3 As Double
Dim cnt_n4 As Double
Dim cnt_w1 As Double
Dim cnt_w2 As Double
Dim cnt_w3 As Double
Dim cnt_w4 As Double
Dim cnt_w1_ok As Double
Dim cnt_w2_ok As Double
Dim cnt_w3_ok As Double
Dim cnt_w4_ok As Double

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

HowToModularize cnt_row, cnt_Col, cnt_w1, cnt_n1, cnt_w1_ok

'***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

HowToModularize cnt_row, cnt_Col, cnt_w2, cnt_n2, cnt_w2_ok

'***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

HowToModularize cnt_row, cnt_Col, cnt_w3, cnt_n3, cnt_w3_ok

'***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

HowToModularize cnt_row, cnt_Col, cnt_w4, cnt_n4, cnt_w4_ok
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
Sub HowToModularize(cnt_row As Long, cnt_Col As Long, cnt_w As Double, cnt_n As Double, cnt_w_ok As Double)
cnt_w = cnt_w + 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_n = cnt_n + 1

ElseIf (Cells(cnt_row, 1 + (3 * cnt_Col)) = "") Or _
(Cells(cnt_row, 2 + (3 * cnt_Col)) = "") Then
cnt_n = cnt_n + 1

ElseIf Cells(cnt_row, 1 + (3 * cnt_Col)) >= _
Cells(cnt_row, 2 + (3 * cnt_Col)) Then
cnt_w_ok = cnt_w_ok + 1

End If
End Sub
[/vba]

[Frosty]

Or another, even easier to read alternative, using select case and variables instead of eye-bleeding elseif statements:
[vba]
Sub test1()
Dim no_of_row As Long
Dim cnt_Col As Long
Dim cnt_row As Long
Dim cnt_n1 As Double
Dim cnt_n2 As Double
Dim cnt_n3 As Double
Dim cnt_n4 As Double
Dim cnt_w1 As Double
Dim cnt_w2 As Double
Dim cnt_w3 As Double
Dim cnt_w4 As Double
Dim cnt_w1_ok As Double
Dim cnt_w2_ok As Double
Dim cnt_w3_ok As Double
Dim cnt_w4_ok As Double

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

Select Case Day(Cells(cnt_row, 1 + 3 * cnt_Col))
Case 1 To 7
HowToModularize cnt_row, cnt_Col, cnt_w1, cnt_n1, cnt_w1_ok
Case 8 To 14
HowToModularize cnt_row, cnt_Col, cnt_w2, cnt_n2, cnt_w2_ok
Case 15 To 21
HowToModularize cnt_row, cnt_Col, cnt_w3, cnt_n3, cnt_w3_ok
Case 22 To 31
HowToModularize cnt_row, cnt_Col, cnt_w4, cnt_n4, cnt_w4_ok

Case Else
'shouldn't get here
End Select

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
Sub HowToModularize(cnt_row As Long, cnt_Col As Long, cnt_w As Double, cnt_n As Double, cnt_w_ok As Double)
Dim varRet As Variant
Dim varRet2 As Variant

cnt_w = cnt_w + 1

varRet = Cells(cnt_row, 1 + (3 * cnt_Col))
varRet2 = Cells(cnt_row, 2 + (3 * cnt_Col))
If UCase(varRet) = "NA" Or UCase(varRet2) = "NA" Then
cnt_n = cnt_n + 1

ElseIf varRet = "" Or varRet2 = "" Then
cnt_n = cnt_n + 1

ElseIf varRet >= varRet2 Then
cnt_w_ok = cnt_w_ok + 1
End If
End Sub
[/vba]

[tfox]

Have to reach 5 post to post link

[tfox]

3 Have to reach 5 posts to post link

[tfox]

4 Have to reach 5 posts to post link

[tfox]

5 Have to reach 5 posts to post link. Mods & admins, plz don't blame me for doing this.

[tfox]

Got a workbook that you can post?

Here it is:


3 sets of data. Each with 10 rows and 2 columns. In this case, all 3 sets contain the same data, but this is not necessarily the case all the time.

The idea is to get the percentage of number in the second row that are smaller than or equal to the first row printed at the bottom. Eventually I will modify it to work with dates i.e. classify by week.

[tfox]

Impossible to give you concrete ideas without seeing at least dummy data.

Very very difficult to troubleshoot complex logic statements when you don't use option explicit.

You could definitely benefit from some modularization of your code, a la:

You may be able to fix further just by simplifying some of your structure.

I agree that my codes are very procedural. I could definitely benefit from object oriented-ness but I haven't learnt how to do it in VBA. Furthermore, I'm trying to get this to work as fast as possible. Any clues as to why it's not working?

[Frosty]

Having manually typed in your data from the image you posted (instead of an actual workbook)... I get an overflow using your code, and I get some meaningless percentages when I use my code.

So...percentage of what and what? I think you're mixing up columns and rows... but if you're going to post an image of a workbook (rather than the real thing), can you at least post what it should look like at the end result, with maybe a highlight on what numbers are doing what?

And since your code has some pretty complex elseif statements dealing with various scenarios, can you post some less dumb dummy data (i.e., deal with NA, empty cells, etc.). If you're asking the question, might as well get somewhat close.

My second code sample should be able to plug in and do exactly what your current code is doing, with the added benefit that it should be easier to troubleshoot what's going wrong.

Also: if this is some kind of school assignment, this isn't the place to be getting free info.

It's not OOP that you need (although that's good stuff too), just some procedural structure and the benefit of someone pointing out that instead of....
IF x >=1 OR x <8 then
End If

You can simplify a lot by using...
Select Case X
Case 1 To 7
End Select

That makes things a lot easier to read and, more importantly, troubleshoot.

Two suggestions:

1. Post an actual workbook with dummy data, and the cells manually filled in with something close to the end result you want. I am confused by your explanation above.

2. Listen to xld over me as he's much better with Excel than I am. However, I suspect this ends up being more of a general programming question (the subject line is what caught my eye) than something excel specific.

But need an example of an end result or someone smarter than me to decipher what you really want, rather than what you said.