Title: Visual Basic | IF, ELSE not working inside DO WHILE loop
Tags: vba;excel
Question: So, I am a little new to Visual Basic and was trying to create a macro on excel. The macro should ideally go through a column and separate the rows with value say, "WB" from the other values, say "EC" and compute based on that.
The problem is that the variables storing WB values are all zero and the variables storing the EC values in fact have the entire data computations.
Sub Button2_Click()
Dim i As Integer, TotalEffortEC As Double, TotalNCEC As Double, TotalSizeEC As Double
Dim TotalEffortWB As Double, TotalNCWB As Double, TotalSizeWB As Double
TotalEffortEC = 0
TotalNCEC = 0
TotalSizeEC = 0
TotalEffortWB = 0
TotalNCWB = 0
TotalSizeWB = 0
i = 3
Do While Worksheets("Valid Data").Cells(i, 6).Value <&gt; ""
If Cells(i, 7).Value = "WB" Then
TotalEffortWB = Worksheets("Valid Data").Cells(i, 23).Value + TotalEffortWB
TotalNCWB = Worksheets("Valid Data").Cells(i, 14).Value + TotalNCWB
TotalSizeWB = Worksheets("Valid Data").Cells(i, 13).Value + TotalSizeWB
Else
TotalEffortEC = Worksheets("Valid Data").Cells(i, 23).Value + TotalEffortEC
TotalNCEC = Worksheets("Valid Data").Cells(i, 14).Value + TotalNCEC
TotalSizeEC = Worksheets("Valid Data").Cells(i, 13).Value + TotalSizeEC
End If
i = i + 1
Loop
Worksheets("Summary").Range("A2").Value = (TotalEffortEC + Range("B10") + Range("C10")) / TotalNCEC
Worksheets("Summary").Range("B2").Value = (TotalEffortEC + Range("B10") + Range("C10")) / TotalSizeEC
Worksheets("Summary").Range("A3").Value = (TotalEffortWB + Range("B11") + Range("C11")) / TotalNCWB
Worksheets("Summary").Range("B3").Value = (TotalEffortWB + Range("B11") + Range("C11")) / TotalSizeWB
End Sub
The WB part of the code doesn't seem to run and it seems only the EC part is running irrespective of the validity of the condition. Any suggestions/help would be greatly appreciated.
Thank you.
Comment: Another note is, you should specify which sheet Cells(i, 7).value = "WB" is refering to. Guess it should be the Valid Data sheet in this case?

Comment: Are you sure column `7` contains any `WB`s? Check for spaces and lower/upper case.

Comment: Try running step-by-step (F8), and when you get to a "WB", highlight the `Cells(i, 7).Value` part and hover mouse over it. You can also add a watch by right clicking and selecting `Add Watch`. This is to see if it really says "WB". My guess is that it does not, because the `If` structure actually works in VBA.

Comment: Is it normal that you make your loop by testing column F and take result from column G ?

Here is the accepted answer: As noted by @Alex4336 you're surely referencing the wrong Worksheet object to which belong the Range and Cells collections to be accessed (parent Worksheet object), mixing qualified objects plus member access syntax (.Range and .Cells) and the use of "global" Range and Cells collections (bound to an active  Worksheet object).
This makes your code inconsistent and source of potential problems.
You can avoid repetitive qualifying expressions and gain readability, consistency and performances simply by introducing With statement (see MSDN) this way:
Sub Button2_Click()
Dim i As Integer, TotalEffortEC As Double, TotalNCEC As Double, TotalSizeEC As Double
Dim TotalEffortWB As Double, TotalNCWB As Double, TotalSizeWB As Double
TotalEffortEC = 0
TotalNCEC = 0
TotalSizeEC = 0
TotalEffortWB = 0
TotalNCWB = 0
TotalSizeWB = 0
i = 3
With Worksheets("Valid Data")
Do While .Cells(i, 6).Value <&gt; ""
If .Cells(i, 7).Value = "WB" Then
TotalEffortWB = .Cells(i, 23).Value + TotalEffortWB
TotalNCWB = .Cells(i, 14).Value + TotalNCWB
TotalSizeWB = .Cells(i, 13).Value + TotalSizeWB
Else
TotalEffortEC = .Cells(i, 23).Value + TotalEffortEC
TotalNCEC = .Cells(i, 14).Value + TotalNCEC
TotalSizeEC = .Cells(i, 13).Value + TotalSizeEC
End If
i = i + 1
Loop
End With
With Worksheets("Summary")
.Range("A2").Value = (TotalEffortEC + .Range("B10") + .Range("C10")) / TotalNCEC
.Range("B2").Value = (TotalEffortEC + .Range("B10") + .Range("C10")) / TotalSizeEC
.Range("A3").Value = (TotalEffortWB + .Range("B11") + .Range("C11")) / TotalNCWB
.Range("B3").Value = (TotalEffortWB + .Range("B11") + .Range("C11")) / TotalSizeWB
End With
End Sub
Comment for this answer: adn Alex4336 - Thanks a ton for your help. It works well now.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 4, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2015:00:12/11/15', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<vba><excel>', 'Title': 'Visual Basic | IF, ELSE not working inside DO WHILE loop', 'ViewCount': 1424, 'id': 34220571, 'user_AboutMe': None, 'user_AccountId': 7454504.0, 'user_DisplayName': 'Bhaskar Neti', 'user_Reputation': 13.0, 'user_Views': 9.0, 'user_id': 5668154.0}}