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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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

P
  • Question Question
How to find till which row the marco executed. It stopped at some random row without any error and unable to identitfy at what row it has been stopped
Replies
3
Views
191
FormR
FormR
A
  • Question Question
Save VBA to PDF
Replies
1
Views
311
MikeVol
MikeVol
U
  • Solved
Rewrite Code as a Sub to work on Active Sheet
Replies
2
Views
53
Skyybot
Skyybot
M
  • Question Question
Formulas with Inititals with Last Name
Replies
3
Views
116
Fluff
Fluff
Emka
  • Question Question
Web Scrap on Chrome via VBA
Replies
0
Views
155
Emka
Emka

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

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