A more elegant way of calculating start/end rows?

[cpjust]

I've got some QA testcases in a spreadsheet. The tests are separated into different sub-sections on each sheet (as well as different major sections on different worksheets). Column B contains the test numbers like this:

1
1.1
1.2
1.2.1
1.3

2
2.1
2.1.1
2.2
...

Column F holds the pass/fail ('P' or 'F') for each test.

Currently my code will detect a change in column F and update a summary at the beginning of each section of the number of pass/fail tests for each sub-section.
However, the start and end row numbers for each sub-section are hardcoded for now, which means if I insert or delete a test, I'll have to remember to update all the hardcoded row numbers in the macro too. Here is an example:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 6 Then
        ADDBUSER.First = 4
        ADDBUSER.Last = 125
        ALTBUSER.First = 127
        ALTBUSER.Last = 257
        DELBUSER.First = 298
        DELBUSER.Last = 265
        LSTBUSER.First = 287
        LSTBUSER.Last = 281
        ALTKN.First = 283
        ALTKN.Last = 337
        LTKN.First = 339
        LTKN.Last = 415
        DTKN.First = 417
        DTKN.Last = 484
        BLKTEST.First = 486
        BLKTEST.Last = 501
        RBALTBSE.First = 503
        RBALTBSE.Last = 629
        RBDEFBSE.First = 631
        RBDEFBSE.Last = 759
        RBDELBSE.First = 761
        RBDELBSE.Last = 797
        RBLISTBSE.First = 780
        RBLISTBSE.Last = 811

        'Pass the correct beginning and end row # for the test group.
        If Target.row >= ADDBUSER.First And Target.row <= ADDBUSER.Last Then
            Call PassFail(Sheets(Target.Parent.Name), ADDBUSER.First, ADDBUSER.Last)
            
        ElseIf Target.row >= ALTBUSER.First And Target.row <= ALTBUSER.Last Then
            Call PassFail(Sheets(Target.Parent.Name), ALTBUSER.First, ALTBUSER.Last)
            
        ElseIf Target.row >= DELBUSER.First And Target.row <= DELBUSER.Last Then
            Call PassFail(Sheets(Target.Parent.Name), DELBUSER.First, DELBUSER.Last)
            
        ElseIf Target.row >= LSTBUSER.First And Target.row <= LSTBUSER.Last Then
            Call PassFail(Sheets(Target.Parent.Name), LSTBUSER.First, LSTBUSER.Last)
            
        ElseIf Target.row >= ALTKN.First And Target.row <= ALTKN.Last Then
            Call PassFail(Sheets(Target.Parent.Name), ALTKN.First, ALTKN.Last)
            
        ElseIf Target.row >= LTKN.First And Target.row <= LTKN.Last Then
            Call PassFail(Sheets(Target.Parent.Name), LTKN.First, LTKN.Last)
            
        ElseIf Target.row >= DTKN.First And Target.row <= DTKN.Last Then
            Call PassFail(Sheets(Target.Parent.Name), DTKN.First, DTKN.Last)
            
        ElseIf Target.row >= BLKTEST.First And Target.row <= BLKTEST.Last Then
            Call PassFail(Sheets(Target.Parent.Name), BLKTEST.First, BLKTEST.Last)
            
        ElseIf Target.row >= RBALTBSE.First And Target.row <= RBALTBSE.Last Then
            Call PassFail(Sheets(Target.Parent.Name), RBALTBSE.First, RBALTBSE.Last)
            
        ElseIf Target.row >= RBDEFBSE.First And Target.row <= RBDEFBSE.Last Then
            Call PassFail(Sheets(Target.Parent.Name), RBDEFBSE.First, RBDEFBSE.Last)
            
        ElseIf Target.row >= RBDELBSE.First And Target.row <= RBDELBSE.Last Then
            Call PassFail(Sheets(Target.Parent.Name), RBDELBSE.First, RBDELBSE.Last)
            
        ElseIf Target.row >= RBLISTBSE.First And Target.row <= RBLISTBSE.Last Then
            Call PassFail(Sheets(Target.Parent.Name), RBLISTBSE.First, RBLISTBSE.Last)
            
        ElseIf Target.row >= RBALTBSE.First And Target.row <= RBALTBSE.Last Then
            Call PassFail(Sheets(Target.Parent.Name), RBALTBSE.First, RBALTBSE.Last)

        ElseIf Target.row > RBALTBSE.Last Then
            MsgBox ("Please update the macro.  I don't know how to calculate that line yet!")
        End If
    End If
End Sub

I'd like to find a way for the start & end row numbers to be dynamically passed to my PassFail() function. So far the only thing I've been able to think of is to look at Column B of the same row where the change happened, parse out the first number of the test number, then move through all rows in Column B to find out where the start and end rows are...

Before spending too much time coding & debugging that logic, I was wondering if anyone has any better solutions?

 

[xlbo]

Do you have gaps between your sets of data ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Could you explain how you define ADDBUSER?

Are these part of a collection?

If they were Named Ranges, you could loop thru the Names Collection and determine start and end row values.

Skip,

_[red][/red]

 

[arides]

well u could do a check against a blank cell. as xlbo implies, that would force you to have the data together and neverskip a row, which can be frustrating sometimes.

the only solution I found for blank cells was to add a "tolerance" for the number of blank cells found before the program decides to stop. so, instead of stoping on a blank cell, you increase a counter, and when that counter reaches a determined number (that being "the tolerance" i mentioned before), it will stop

one VERY importante thingy: if the cell is blank, the process must be skipped for that row/cell. or else u'll get obvious errors or at least make the app take longer to complete.

the basic structure would be:

counter =0
do 
    if cell = "" then(or any equivalent comparison, anything      that would make the cell invalid) 
            counter = counter +1
    else
            code
    endif

loop until counter = 50

i found i could put a 50 cells tolerance without feeling an impact on the application. the extra comparison did not made the application a lot heavier either.

hope that helps,


---------------
Dogbert

Can God write a code so complex he couldn't understand?

 

[djj55]

I have been known to go to the bottom right (can not remember the code for <CTRL><END> and move down one line, move end left, and then up to the first cell with data (end up).
djj

 

[cpjust]

Do you have gaps between your sets of data ?

Yes, there are a couple blank lines between the 1.x tests and 2.x tests. Although sometimes there will be a blank line inside a block of tests because I just have a comment there to add more tests in the future...

Could you explain how you define ADDBUSER?

Public Type TestRange
    First As Integer    'First row of tests.
    Last As Integer     'Last row of tests.
End Type

Dim ADDBUSER As TestRange

Actually, I just thought of another idea... Maybe I could add a comment to the first and last cells of each block that say something like "START testname" and "END testname", then move up to the first START comment from the changed row, and move down to the first END comment...?

 

[SkipVought]

dim lRow1 as long, lRow2 as long, lRow as long
with activesheet.usedrange
  lRow1 = .row
  lRow2 = .row + .rows.count - 1
end with
lRow = lRow1
do
   with cells(lrow, 1).currentregion
      PassFail(.Parent, .Row, .row + .rows.count - 1)
      lrow = cells(lrow+.rows.count-1, 1).end(xldown).row
   end with
Loop Until (lrow > lrow2)

Skip,

_[red][/red]

 

[Rofeu]

Hi,

I don't think you need to use code at all.

Enter the following formula in cell D2:

=SUM(IF(LEFT(B3:OFFSET(B3,COUNTIF(B:B,B2)+1,0),1)&F3:OFFSET(B3,COUNTIF(B:B,B2)+1,0)=B2&C2,1,0))

(if summary F and P are underneath eachother then for the first line COUNTIF(B:B,B2)+2 for the second COUNTIF(B:B,B2)+1)

provided that Column B in summary row lists the sub-section to summarize (change the length-criteria in the LEFT formula accordingly) and C the P or the F and there are no blank lines within a sub-section. Then enter it using Ctrl-Shift-Enter

If your summary is organized differently, please post how. The formula can be updated accordingly.

Cheers,

Roel

 

[cpjust]

My spreadsheet looks something like this:

      |      |      | %P |
1     | text | text | P  |
1.1   | text | text | F  |
1.2   | text | text |    |
1.2.1 | text | text |    |
1.3   | text | text | P  |
      |      |      |    |
      |      |      | %P |
2     | text | text | F  |
2.1   | text | text | P  |
2.2   | text | text |    |
  Insert test later |    |
2.2.1 | text | text | P  |
2.3   | text | text | F  |

%P is the summary of how many tests passed & failed.

 

[Rofeu]

Ok,

enter the following formula using ctrl-shoft-enter in place of %P (take care that Excel is in R1C1 reference style):

=(SUM(IF(LEFT(R[1]C2:R65536C2,LEN(R[1]C2))&R[1]C6:R65536C6=R[1]C2&"P",1,0)))/(SUM(IF(LEFT(R[1]C2:R[65535]C2,LEN(R[1]C2))=TEXT(R[1]C2,"@"),1,0)))

(assuming P/F in Col F and SubSection in Col B)

The only problem is the huge range to it uses. It takes quite a while to calculate. It can be reduced in size by using COUNT-like formulae and OFFSET. I'll have a look into later on, but I think this formula will do what you want.

Cheers,

Roel

 

[Rofeu]

Hello,

here's one that uses a much smaller range:

=(SUM(IF(LEFT(R[1]C2:OFFSET(R[1]C2,MATCH(MAXA(C2),C2,FALSE)-ROW()-1,0),LEN(R[1]C2))&R[1]C6:OFFSET(R[1]C2,MATCH(MAXA(C2),C2,FALSE)-ROW()-1,0)=R[1]C2&"P",1,0)))/(SUM(IF(LEFT(R[1]C2:OFFSET(R[1]C2,MATCH(MAXA(C2),C2,FALSE)-ROW()-1,0),LEN(R[1]C2))=TEXT(R[1]C2,"@"),1,0)))

Cheers,

Roel

 

[firefytr]

You could use Data Validation for this, using a custom formula checking the COUNTA of the range above (i.e. $A$1:$A10, assuming you're on row 11) agains the ROW().

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[cpjust]

It works fine with the macro for now, but when I get some time I`ll try out the other way you suggested.
Thanks.

 

[cpjust]

I tried all those formulas, but the first one doesn't seem to display anything but #VALUE, and the other two gave an error about the R[1]C2 part.

I think I'll just stick with a macro rather than a long cryptic formula.