vba Working with columns by header name

[cgmojoco]

Hi there-

Help...I am looking for a better solution working with columns in VBA.

Currently, I refer to columns by their letter or # in my VBA code.

For example:

Columns("AW:AW").FormulaR1C1 = "=+RC[-8]"

I have a scenario where I have to work with a dynamic data source that scrambles columns around (column is not always in the same position on the spreadsheet). Currenct...Also, there may be missing columns or new columns that need to be recognized.

What I would like to do is refer to my columns by header name.

For example (PSUEDO CODE):

If column ("MTD BUD") or ("MTD REV") do not exist Then
Goto ErrorHandler
Else
Columns("MTD REV").FormulaR1C1 = "=+RC[MTD BUD]"
End if

Would the best way to go about something like this be to create an array with column header names that I can run things against? How would this look?

(column headers are always in row 2)

 

[DonkeyOte]

personally I would set variables (as integer) for columns and use WorksheetFunction.Match to determine the position of a column, (if using XL07 you might want to use Long rather than Integer)

ie

Dim co1 as integer: co1 = WorksheetFunction.Match("HeaderString",Sheets("Sheet1").Rows(2),0)

Then just use co1 to refer to the column.

 

[Jonmo1]

you could use Match or Find to find the column with the header name...
Something like this maybe..

X = Application.Match("MTD BUD",Range("1:1"),0)
If IsError(X) Then Goto ErrorHandler
Columns(X).FormulaR1c1 = "=RC" & X

 

[cgmojoco]

Great thank you very helfpul.

That helps with moving columns issue!

New/Missing columns:

If I have a list of column headers that I expect to see.

How would I go about comparing that list to the actual headers and returning new/missing columns?

Would I use an array for that?

Thanks in advance

 

[cgmojoco]

How can I make this return the column letter rather than #?

colBUDGET = Application.Match("BUDGET", Range("1:1"), 0)

 

[Jonmo1]

How can I make this return the column letter rather than #?

Why ??

It's not needed, and it creates extra work to convert the # to the Letter..

You can use the Cells Function to use column #
Cells(Row#,Col#)

Cells(10,1) is the same as Range("A10")

 

[cgmojoco]

Thanks---yes, I know. I am just not aware of how to pass the column # into the below formatted VBA.

Range(strC & strR & ":" & endC & LR).Copy

strC is the letter of the starting column
endC is the letter of the last column

Perhaps I need to rewrite this so it uses the column# rather than letter...

 

[Jonmo1]

Range(Cells(strR,strC),Cells(LR,endC)).Copy

 

[cgmojoco]

Thanks for that.
Wow a better question returns a better answer!

 

[cgmojoco]

I must be doing something wrong?:

Range(Cells(strR, strC), Cells(LR, endC)).Copy

strR is 2
strC is 49
LR is 3000
endC is 49

But it doesn't copy