VBA Calculate Coverage

M

mtjanousek

New Member
Joined
Jul 25, 2018
Messages
17
Hello,
I have another issue while finishing my excel workbook.

I am trying to compute coverage for a specific number. I finished my code but then I realized, that I am actually comparing the same rows in different sheets, while I need to compare matching rows and columns by a corresponding number.

i.e.: for number 205090 in sheet2 I need to compare values in rows with the same number in sheet3. The issue is, that the numbers are not in order while I did my macro as they would be.

The result of this is how many weeks the value in result sheet is able to cover.

This is how the results should look like. I am interested only in columns A (compared number), D (value) and E (where is the result of coverage - note: results in this column are just an example).
HJTd1Sc.png
Click to expand...
And here I have a sheet with the data I am comparing:
HJTeqB6.png
Click to expand...
Here I am interested in column A and columns G (delay) to AH (the rest I need to cover).

This is a macro I was using which is unfortunately not respecting the corresponding numbers.

Code: 
Sub WeeklyCoverage()

'   Define lr and r as integers; Double for decimals; String for the text
    Dim lr As Long
    Dim r As Long
    Dim wsr As Worksheet
    Dim wsc As Worksheet
    Set wsr = Worksheets("SH2_results")
    Set wsc = Worksheets("SH3_data")
   
'   Turn screen updating off (to speed up computation)
    Application.ScreenUpdating = False
        
'   Find the last row with data in column D
    lr = wsr.Cells(Rows.Count, "D").End(xlUp).Row
    
'   Loop through all rows in column D starting with row 2 and ending by the value of lr
    For r = 2 To lr
'       Compare the "COVERAGE" with the "DEALY"
'   Not Covered
        If wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value Then
            wsr.Cells(r, "E").Value = "NONE"
'   Covered SKLUZ only
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value Then
            wsr.Cells(r, "E").Value = "DELAY"
'   Covered till W0
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value Then
            wsr.Cells(r, "E").Value = "W00"
'   Covered till W1
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value Then
            wsr.Cells(r, "E").Value = "W01"
'   Covered till W2
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value Then
            wsr.Cells(r, "E").Value = "W02"
'   Covered till W3
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value Then
            wsr.Cells(r, "E").Value = "W03"
'   Covered till W4
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value Then
            wsr.Cells(r, "E").Value = "W04"
'   Covered till W5
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value Then
            wsr.Cells(r, "E").Value = "W05"
'   Covered till W6
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value Then
            wsr.Cells(r, "E").Value = "W06"
'   Covered till W7
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value Then
            wsr.Cells(r, "E").Value = "W07"
'   Covered till W8
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value Then
            wsr.Cells(r, "E").Value = "W08"
'   Covered till W9
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value Then
            wsr.Cells(r, "E").Value = "W09"
'   Covered till W10
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value Then
            wsr.Cells(r, "E").Value = "W10"
'   Covered till W11
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value Then
            wsr.Cells(r, "E").Value = "W11"
'   Covered till W12
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value Then
            wsr.Cells(r, "E").Value = "W12"
'   Covered till W13
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value Then
            wsr.Cells(r, "E").Value = "W13"
'   Covered till W14
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value Then
            wsr.Cells(r, "E").Value = "W14"
'   Covered till W15
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value Then
            wsr.Cells(r, "E").Value = "W15"
'   Covered till W16
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value Then
            wsr.Cells(r, "E").Value = "W16"
'   Covered till W17
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value Then
            wsr.Cells(r, "E").Value = "W17"
'   Covered till W18
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value Then
            wsr.Cells(r, "E").Value = "W18"
'   Covered till W19
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value Then
            wsr.Cells(r, "E").Value = "W19"
'   Covered till W20
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value + wsc.Cells(r, "AC").Value Then
            wsr.Cells(r, "E").Value = "W20"
'   Covered till W21
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value + wsc.Cells(r, "AC").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value + wsc.Cells(r, "AC").Value + wsc.Cells(r, "AD").Value Then
            wsr.Cells(r, "E").Value = "W21"
'   Covered till W22
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value + wsc.Cells(r, "AC").Value + wsc.Cells(r, "AD").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value + wsc.Cells(r, "AC").Value + wsc.Cells(r, "AD").Value + wsc.Cells(r, "AE").Value Then
            wsr.Cells(r, "E").Value = "W22"
'   Covered till W23
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value + wsc.Cells(r, "AC").Value + wsc.Cells(r, "AD").Value + wsc.Cells(r, "AE").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value + wsc.Cells(r, "AC").Value + wsc.Cells(r, "AD").Value + wsc.Cells(r, "AE").Value + wsc.Cells(r, "AF").Value Then
            wsr.Cells(r, "E").Value = "W23"
'   Covered till W24
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value + wsc.Cells(r, "AC").Value + wsc.Cells(r, "AD").Value + wsc.Cells(r, "AE").Value + wsc.Cells(r, "AF").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value + wsc.Cells(r, "AC").Value + wsc.Cells(r, "AD").Value + wsc.Cells(r, "AE").Value + wsc.Cells(r, "AF").Value + wsc.Cells(r, "AG").Value Then
            wsr.Cells(r, "E").Value = "W24"
'   Covered till W25
        ElseIf wsr.Cells(r, "D").Value >= wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value + wsc.Cells(r, "AC").Value + wsc.Cells(r, "AD").Value + wsc.Cells(r, "AE").Value + wsc.Cells(r, "AF").Value + wsc.Cells(r, "AG").Value And _
        wsr.Cells(r, "D").Value < wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value + wsc.Cells(r, "AC").Value + wsc.Cells(r, "AD").Value + wsc.Cells(r, "AE").Value + wsc.Cells(r, "AF").Value + wsc.Cells(r, "AG").Value + wsc.Cells(r, "AH").Value Then
            wsr.Cells(r, "E").Value = "W25"
'   Covered till W26
        ElseIf wsr.Cells(r, "D").Value = wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value + wsc.Cells(r, "AC").Value + wsc.Cells(r, "AD").Value + wsc.Cells(r, "AE").Value + wsc.Cells(r, "AF").Value + wsc.Cells(r, "AG").Value + wsc.Cells(r, "AH").Value Then
            wsr.Cells(r, "E").Value = "W26"
'   Covered till W27
        ElseIf wsr.Cells(r, "D").Value > wsc.Cells(r, "G").Value + wsc.Cells(r, "H").Value + wsc.Cells(r, "I").Value + wsc.Cells(r, "J").Value + wsc.Cells(r, "K").Value + wsc.Cells(r, "L").Value + wsc.Cells(r, "M").Value + wsc.Cells(r, "N").Value + wsc.Cells(r, "O").Value + wsc.Cells(r, "P").Value + wsc.Cells(r, "Q").Value + wsc.Cells(r, "R").Value + wsc.Cells(r, "S").Value + wsc.Cells(r, "T").Value + wsc.Cells(r, "U").Value + wsc.Cells(r, "V").Value + wsc.Cells(r, "W").Value + wsc.Cells(r, "X").Value + wsc.Cells(r, "Y").Value + wsc.Cells(r, "Z").Value + wsc.Cells(r, "AA").Value + wsc.Cells(r, "AB").Value + wsc.Cells(r, "AC").Value + wsc.Cells(r, "AD").Value + wsc.Cells(r, "AE").Value + wsc.Cells(r, "AF").Value + wsc.Cells(r, "AG").Value + wsc.Cells(r, "AH").Value Then
            wsr.Cells(r, "E").Value = "W26+"
            
        End If
    Next r

'   Turn screen updating on
    Application.ScreenUpdating = True
                   
End Sub

Is it possible to just adjust my code to do the coverage with the corresponding number, or the code is completely broken?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
I have found a solution on the SO (https://stackoverflow.com/questions...coverage-in-two-sheets-for-each-matching-code):

Code: 
<code></code>
<code>Set FoundCell = wsr.Range("A:A").Find(wsc.Cells(r, "A").Value)
If Not (FoundCell Is Nothing) Then
   FoundRow = FoundCell.Row

'..........................
'.. Use FoundRow as bellow:
'..........................

If wsr.Cells(FoundRow, "D").Value < wsc.Cells(r, "G").Value Then
   wsr.Cells(FoundRow, "E").Value = "NONE"

'........................
'.. your provided code
'........................

End If</code>
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Word Doc into excel
Replies
1
Views
518
NdNoviceHlp
NdNoviceHlp
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
325
pure vito
P
A
  • Solved
Merging Multiple Cell Values into 1 Cell
Replies
7
Views
206
StephenCrump
S
gtd526
  • Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
136
gtd526
gtd526
R
  • Solved
Generate 1000 Iterations of a Calculated Value
Replies
4
Views
134
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,918
Messages
6,122,257
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top