Problem with adding and deleting columns, ruins code

[frshca]

I have a spreadsheet with 20 or so columns but that number can change at any time. I need my code to by dynamic so if a column is deleted (or added) my code will still work. Each column has a name in row 2 and I'm trying to use that in order to make everything dynamic. For example, my code counts the number of blue cells in colums D4:G68, but now if someone adds a column before column D, everything gets messed up and it will still count the cells in D4:G68 but I want to use E4:H64 now. Does that make sense? I have made code that searches the column names and returns the column number to correspond to the name. How would I change this?

Range ("C8") = "= CountBlue(E4:G68)"

*Note - the code doesn't look exactly like this, I believe it is actually in R1C1 format

 

[frshca]

Fair enough. It is easy for me to picture it because i have the chart, sorry for getting too far ahead. Lets say I want to count the blue cells for the "objects" in version 1.x (so all objects from version 1.0 to 1.9). Here is what the chart looks like (names are all arbitrary, don't worry about the names)

VERSION OBJECT SQUARE1 SQUARE2 SQUARE3
1.0 OB1 date date date
1.1 OB2 date date date


There is an example of what the chart looks like. Imagine the cells with dates are blue, green, yellow, or red. So I want to count the blue cells. I would say:


= CountBlue( "Square1.column & Version1.0.row" : "Square3.column & Version1.1.row" )

from the example, Square1.column = 3, Version1.0.row = 2 (I understand it can't actually be Version1.0.row), Square3.column = 5, and version1.1.row = 3.

However, I would want the columns to output a row letter instead so the function above would actually read:

= CountBlue(C2:E3)

Does that help any? --- after posting this thread, it reformatted the "example" sheet, but VERSION,OBJECT,etc are the top row. 1.1 and 1.0 should be under the version column, ob1 and ob2 under the OBJECT column, and the dates under the SQUARE columns

 

[Joe4]

What determines the color of these cells?
If it is determined by Conditional Formatting (where there is some formula/logic tied to each color), this could be a bit easier, as we may be able to use SUMPRODUCT to get your counts.

 

[Joe4]

Based on your example, I wanted to see if I could build the range that would include SQUARES 1 through 3 for all version beginning with "1.". Here is what I came up with (it is probably not the most efficient code, but it does work):

    Dim myStartCol As Integer
    Dim myEndCol As Integer
    Dim myStartRow As Long
    Dim myEndRow As Long
    
    Dim myLastDataRow As Long
    Dim i As Long
    Dim j As Long
    
    Dim myRange As Range
    
'   Find start column (where "SQUARE 1" exists)
    Range("A1:F1").Select
    Selection.Find(What:="SQUARE 1", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
    myStartCol = ActiveCell.Column
    
'   Find end column (where "SQUARE 3" exists)
    Range("A1:F1").Select
    Selection.Find(What:="SQUARE 3", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
    myEndCol = ActiveCell.Column
    
    
    myLastDataRow = Range("A65536").End(xlUp).Row
'   Find starting row (assuming looking in column "A", data starting on row 2)
    For i = 2 To myLastDataRow
        If Left(Cells(i, "A"), 2) = "1." Then
            myStartRow = i
            Exit For
        End If
    Next i
    
'   Find ending row (assuming looking in column "A", data starting on row 2)
    For j = myLastDataRow To 2 Step -1
        If Left(Cells(j, "A"), 2) = "1." Then
            myEndRow = j
            Exit For
        End If
    Next j

    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myEndRow, myEndCol))
    
'   Highlight range
    myRange.Select

'   Put COUNTBLUE formula in cell Z1
    Range("Z1").Formula = "=COUNTBLUE(" & myRange.Address & ")"

If you run this code, it will highlight the range it is building, so you can see if it is working properly. And it will put your COUNTBLUE formula in cell Z1.

 

[frshca]

Ok Joe, it looks real good and thanks so much for the help. However, I am getting a "subscript out of range" error in the paragraph that finds the start column. What does this mean and how do I fix it? If you haven't already noticed, I am brand new to vba and everything that I have learned has been from me just typing and seeing if it works. So if you wouldn't mind, could you briefly go over what the functions you used actually do? (For example, LookIn:=, LookAt:=, SearchOrder:=, SearchDirection:=, MatchCase:=, and the line that has:

For j = myLastDataRow To 2 Step -1

how does this part work?)

 

[Joe4]

The code is looking in the Range A1:F1 for the exact term "SQUARE 1". It looks to me like maybe it is not finding this value in that range. Can you confirm that the exact phrase "SQUARE 1" exists somewhere in range A1:F1 (and likewise, "SQUARE 3" also exists in that range)?

The line:

For j = myLastDataRow To 2 Step -1

is just a "backwards" loop.

By default, these kind of "FOR" loops increment by a value of 1 each time. However, in this instance, I want to start at the bottom and work my way up until I find the last row that start with "1.". So, it starts with the last row of data on the sheet (which we calculated and set equal to "myLastDataRow"), and work its way up to our first row of data (row 2). The "Step -1" tells it to decrement the counter by 1 each time instead of incrementing it by 1 (so we are counting down instead of counting up).

Note, that when we find what we are looking for, the "EXIT FOR" statement kicks us out of our loop. So it will probably never actually get all the way to row 2 (unless it can't find what we are looking for).

 

[frshca]

Hey Joe, it was my mistake and not yours. I mistyped "SearchOrder:=xlByRows" as "SearchOrder:=xlyByRows" so it works beautifully now. It works basically how I need it to work, I just have to make a few modifications to make it work with my sheet. Thank you so much.

 

[Joe4]

Hey Joe, it was my mistake and not yours. I mistyped "SearchOrder:=xlByRows" as "SearchOrder:=xlyByRows" so it works beautifully now. It works basically how I need it to work, I just have to make a few modifications to make it work with my sheet. Thank you so much.

Great! Glad you got it working.

If you manually typed all that out, and only had one typo, you are a much better typist that I am (or ever will be)! I would have just used Copy and Paste to copy the code to my workbook.

 

[frshca]

Yea I wish I could copy it but the computer the document is on, does not have an internet connection. I do have one other problem with the code. When I ran it, my data began on line 3 but the output said it started on line 4. What is the reason for this? Can I simply just subtract one from i?

 

[Joe4]

If your data begins on some line other than line 2, then you will want to change the two loops accordingly. Say it begins on line 3. Then you will adjust them like so:

    For i = 3 To myLastDataRow

    For j = myLastDataRow To 3 Step -1

 

[frshca]

OH! duh. ok thanks again!