how can I make my VBA code independent of column insertion or removal?

[mihaipruna]

Basically I have been working under the assumption that people would not need to insert or remove columns in random places.
So my code has statements such as:

Cells(sumrow, 5).FormulaR1C1 = "=Takedown!R" & startrow & "C" & 45 - 4
If Cells(9, 7).Value = takedown.Cells(startrow, 4).Value Then
Range("I:I,J:J,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,V:V,W:W").Select

Is there a fairly easy way to change this code and make it work after users have inserted or removed columns,wherever their sweet hearts desire?
Or do I have to rewrite every line for querying column contents to figure out the location of each column? (which will also involve some editing of the spreadsheet itself since some columns cannot be distinguished from others)
Don't you love "reasonable" requests?
Is there a way columns can be added to the right of the columns affected by code, but displayed in between those columns? I could make an add-delete column button that would do that, rather than leave the user with control over adding or inserting columns.

Thanks much!

 

[shyrath]

Now I don´t know the answer to the question.

But sometimes you want to be able to put a formula:

=IF("USERBRAIN"<"MYBRAIN";restrictuserfromusingcomputer;goahead)

 

[Jonmo1]

I can't give a direct answer specific to your situation.

But the best method is to have Headers in your columns.
Then you can use some sort of lookup to find the column you want and refer to that.

Like say you want to refer to the column that has the header of "Company Name"
You would use

MyCol = Application.Match("Company Name", Range("1:1"),0)

That will search row 1 for Company Name and return the column # where it is found.

Now wherever you need to refer to that column, you use that #
Cells(10,MyCol) = Row 10 in whatever column has the header "Company Name"


It will take some thought and alot of modification to your code, but it will be well worth it in the end.

Hope that helps..

 

[mihaipruna]

thanks for the input guys, I'm starting to develop some ideas, keep the good info coming

 

[Jonmo1]

Also using Named Ranges Helps...

Highlight a range, click Insert - Name - Define.
Give it a name and click Add

Now that Named range will automatically adjust when columns/rows are inserted/deleted.

and you can refer to that range in VBA like
=Range("NameOfRangeHere")

 

[mihaipruna]

Thanks Jon, I've thought of making named ranges for each column too...but how would I be able to fix items where I am actually inserting a formula via code, such as below:

itemstring = "R" & startf & "C" & 29 + 10 - 4
outdls.Add Item:=itemstring, key:=keystring
transfers.Cells(startf, 5).FormulaR1C1 = "=Takedown!" & outdls(i)

I guess that first solution,having each column have an independent hearder, would work better in this case, what do you think? The 29+10-4 comes from changes being made to the spreadsheet and me looking for quick fix. But now I have to be able to account for any possible column insertion or deletion.