VBA column header vs first row text

vthomeschoolmom · #1 03-09-2012, 09:18 AM

I want to perform an action on rows within a column. I want to get the column header letter(s) of the column whose first row contains a known value.

For instance, if the first row of column N shows "Price", I want to retrieve the N for using in a range select. The columns that I will be working with are not fixed.

Can someone help the Excel object model noob with this? Muchos gracias.

macropod · #2 03-10-2012, 02:05 AM

You could try something along the lines of:

Code:

Sub Demo()
Dim LastCol As Long, i As Long
With ActiveSheet
  LastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
  For i = 1 To LastCol
    If .Cells(1, i).Value = "Price" Then Exit For
  Next
  MsgBox Split(.Cells(1, i).Address, "$")(1)
End With
End Sub

Of course, you don't actually need the column letter(s) to work with the column. Having found the column number, you can work with that. For example, if you want a value from the 10th row in the found column, you could use:
.Cells(10, i).Value

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Two column format without affecting neither the page numbers nor the header or footer	Jamal NUMAN	Word	4	02-21-2012 03:20 PM
Aligning column text in a table	dpeattie	Word	6	12-29-2010 04:59 PM
adding text on a separated column line	ahmet1985	Word	0	03-27-2010 07:10 AM
2-Column Text Slides and Aligning Bullets	KitKatJM	PowerPoint	1	03-09-2010 06:45 PM
Lost Column header	Policy Peddler	Outlook	1	06-05-2009 07:57 AM