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)

 

[Norie]

One thing you could be doing wrong is not posting all the code.

Without worksheet references for Range/Cells that code will refer to whatever VBA regards as the active sheet.

 

[Jonmo1]

works for me, it copyed the range AW2:AW3000

 

[cgmojoco]

Yes I was doing something wrong...apologies

I had to change my variable declarations to integer (they were string with previous implementation)

Thanks again!