Problem with adding and deleting columns, ruins code

frshca

New Member
Joined
Jun 4, 2008
Messages
27
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
Try using Named Ranges, which should adjust when cells within that range are inserted/deleted (Insert | Name | Define ...)
 

frshca

New Member
Joined
Jun 4, 2008
Messages
27
Ok, so if I used this technique, how would that alter my code?

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
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:

Code:
Range ("C8") = "= CountBlue(myRange)"
note no quotes around myRange
 

frshca

New Member
Joined
Jun 4, 2008
Messages
27
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
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

New Member
Joined
Jun 4, 2008
Messages
27
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
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

New Member
Joined
Jun 4, 2008
Messages
27
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
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?
 

Forum statistics

Threads
1,082,323
Messages
5,364,586
Members
400,808
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top