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

 

[Joe4]

Try using Named Ranges, which should adjust when cells within that range are inserted/deleted (Insert | Name | Define ...)

 

[frshca]

Ok, so if I used this technique, how would that alter my code?

= CountBlue("myrange") ... is that correct?

 

[Joe4]

Not quite. Putting brackets around something denotes a little value instead of a variable (or named range). So, I think you want something like this:

Range ("C8") = "= CountBlue(myRange)"

note no quotes around myRange

 

[frshca]

Ok that works great! I have a couple problems with using this method though. One of my macros goes through sheet 1 and looks at dates in a certain section and determines if they are 30 days overdue and writes down the name of the software and how late it is on sheet 2. The way this works is by using a function I created called "AGE," this function simply reads the target cell and outputs the following:

AGE('sheet1'!B7) = mySoftware is 36 days overdue.

So the macro is set up to use a counter, similar to the following:

i = 0
range("B" & 10 + i & ")" = "=AGE('sheet1'!B" & 7 + i & ")"
range("C" & 10 + i & ")" = "=AGE('sheet1'!C" & 7 + i & ")"
i = i +1

and it loops this until I reach a specified "i" value.

So column B can change if a new column is added or subtracted on sheet 1. How do I fix this? is it possible to use the idea I had in my first post where I just use the name in the column to find the column number or can I still use the named range of this? Thanks!

 

[Joe4]

You should be able to build and name your range dynamically by searching and using for specific column names, but there may be an easier way.

Is your data contigous? If so, than you may be able to use Current Region to capture your range dynamically every time. Starting from some cell, Current Region will select all rows from that point up to the first completely blank row (above and below), and all columns from that point up to the first completely blank column (to the left and right).

To see what it does, pick a cell in your data and type CTRL-SHIFT-*

In VBA code, it would look something like this:

Range("A1").CurrentRegion.Select

 

[frshca]

The data isn't exactly contiguous or it is connected to some data that I do not want to be included in the "CurrentRegion." If I went through and selected all the data as a Named Range, how could I change the code to allow this to work? is there a way I could take cell number 1 in the named range, run the age function on it, and then output in a specified cell on sheet 2, then continue this procedure until I have gone through all the cells in the named range?

i = 0
range("B" & 10 + i & ")" = "=AGE(myRange.cell(1," & 1 + i & "))"
range("C" & 10 + i & ")" = "=AGE(myRange.cell(2," & 1 + i & "))"
i = i +1

*this is assuming cell(1,1) means the cell in column 1, row 1 of myRange. And cell(2,1) means cell in column 2, row 1 of myRange.

So I would run this until i = number of data

 

[Joe4]

In your original post, you said:

I have made code that searches the column names and returns the column number to correspond to the name. How would I change this?

If you post this code, I'll see if I can help you edit it to build a dynamic range.

One question though. How do you determine which rows to include in your range?

 

[frshca]

Unfortunately, I cannot post code from the actual document. I can post some sort of code that is similar to the actual file:

Sub findColumn()
For Each cell In Range("A1:M1")
If cell = "Version" Then
VE = cell.Column
ElseIf cell = "Object" Then
OB = cell.Column
ElseIf cell = "Square 1" Then
S1 = cell.Column
ElseIf cell = "Square 2" Then
S2 = cell.Column
ElseIf cell = "Square 3" Then
S3 = cell.Column
ElseIf cell = "Square 4" Then
S4 = cell.Column
ElseIf cell = "Circle 1" Then
C1 = cell.Column
ElseIf cell = "Circle 1" Then
C1 = cell.Column
ElseIf cell = "Triangle 1" Then
T1 = cell.Column
ElseIf cell = "Triangle 2" Then
T2 = cell.Column
ElseIf cell = "Triangle 3" Then
T3 = cell.Column
ElseIf cell = "Triangle 4" Then
T4 = cell.Column
End If
Next cell
End Sub

.... I guess I can't attach files but I can describe the sheet. From the code, I'll give the values of OB,VE,S1, etc in order:

1, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 -- The only break is between 2 and 4 (Object and Square 1) -- all of these arbitrary names are along the top of the sheet. So these number correspond to the column they are in (A,B,D,etc.) So how would I substitute this into the previous code I gave you? (The one containing the "AGE" function)

* if these helps, below "Version" are numbers from 1.0 to 2.8 with a break in between the 1.9 and the 2.0, below Object are the names of the packages (Object 1 to Object 19), and below all other columns are dates, the dates are the parts I will run the "AGE" function on.

 

[Joe4]

OK, I think you have totally lost me in the details. I am afraid I can't see the forest for the trees.

Any chance we can simplify this example a little so we can try to get some working methodology. Let's look at dynamically creating one range. Can you explain, in English, how we determine the starting/ending points of that one range?