Loop Count Math issue

[rjplante]

I have a spreadsheet that has a table with percentage values in it (see below). I want to sum up all the values starting with row 8 (first row with percent values in it), up to and including my row (the row of the cell I selected) for the rows that have a name in column D that matches the name in column D of my row. When I execute the macro listed below, It does not return the correct sum of the columns. The value of 0% shows up next to Bill Davis should read 25% and not 0% as I have cell L9 selected. If I select L11, I should get 125% and I just get 100%. How do I get the math to work correctly?

Also if change the Do to Do Until x=myRow, the sum is 0 for everything I select. I would have thought that the loop would run through the row I had selected. But it seems to only total things if I change it to my row plus 1. Even with that change I cannot figure out why I am getting 0% when it should read 25% for Bill. I am confused.

There are formulas in each of the cells that look like this:
=IF(OR($G9="",$H9=""),"",IF(AND($G9>L$6,$G9>=L$6+4),"",IF(OR($G9>=L$6,$H9>=L$6,$H9>=L$6+4),VLOOKUP($C9,data!$Q$2:$AC$13,HLOOKUP($F9,data!$Q$2:$AC$13,2,FALSE),FALSE),"")))

They are referencing a data table I have on another tab that has the percentage listed for each role and the task performed.

Thanks for any help with this one.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub 'If user selects more than 1 cell, then exit sub
If Target.Column < 9 Or Target.Column > 60 Then Exit Sub
If Target.Row < 8 Or Target.Row > 508 Then Exit Sub
If Target.Value = "" Then Exit Sub

Dim myCol As Long
Dim myRow As Long
Dim myRes As String
Dim ResCap As Long
Dim mySum As Integer
Dim y As Integer

Range("E7:E507").ClearContents

myRes = Range("D" & ActiveCell.Row).Value
myRow = ActiveCell.Row
myCol = ActiveCell.Column
mySum = 0

x = 8

Do Until x = myRow + 1
    If Range("D" & x).Value = myRes Then
        mySum = mySum + Cells(x, myCol).Value
    End If
    x = x + 1
Loop

ResCap = mySum

Range("E" & myRow).Value = ResCap

End Sub

 

[mumps]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Akuini]

Try changing this part:

Do Until x = myRow + 1
    If Range("D" & x).Value = myRes Then
        mySum = mySum + Cells(x, myCol).Value
    End If
    x = x + 1
Loop

to this:

For x = 8 To myRow
    If Range("D" & x).Value = myRes Then
        mySum = mySum + Cells(x, myCol).Value
    End If
Next

 

[rjplante]

I tried changing the code to what you have listed below and get the same result.

For x = 8 To myRow
    If Range("D" & x).Value = myRes Then
        mySum = mySum + Cells(x, myCol).Value
    End If
Next

 

[Akuini]

Try dim these 2 variable as Double:
Dim ResCap As Long
Dim mySum As Integer

 

[rjplante]

That worked. Once I changed the declarations to double, everything worked. Thanks a ton.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.