vba Working with columns by header name

cgmojoco

Well-known Member
Joined
Jan 15, 2005
Messages
696
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:
Rich (BB code):
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):
Rich (BB 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)
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
Code:
Dim co1 as integer: co1 = WorksheetFunction.Match("HeaderString",Sheets("Sheet1").Rows(2),0)

Then just use co1 to refer to the column.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Jan 15, 2005
Messages
696
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

Well-known Member
Joined
Jan 15, 2005
Messages
696

ADVERTISEMENT

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

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Jan 15, 2005
Messages
696

ADVERTISEMENT

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...
 

cgmojoco

Well-known Member
Joined
Jan 15, 2005
Messages
696
Thanks for that.
Wow a better question returns a better answer!
 

cgmojoco

Well-known Member
Joined
Jan 15, 2005
Messages
696
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,560
Messages
5,596,842
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top