Variable column reference for a formula

[vjay883]

Hi,

I am trying to trim a column using a formula but the problem is that the column reference wont always be the same, It could be column B or column C etc. What I cant do is hard code the column references as can change, so what I specifically need to do is:

1. Find 'AS_DESCRIPTION' which will be the column used.
2. Go to the right of the data set to find the first empty cell to type the formula in and drag formula down to the bottom.
3. Then copy the trimmed formula and paste over the original data.
4. Delete formula used column
5. Repeat the same for a column named 'AS_DESCRIPTION_LD'

Any help is appreciated
V

[vjay883]

I can used a named range for the columns but the rows and columns will still be hardcoded when creating the range.

[p45cal]

You can do something like this to get the column number of a header in row 1 of the active sheet:

Set r = Rows(1).Find(What:="AS_DESCRIPTION", LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False, SearchFormat:=False) 
If Not r Is Nothing Then ColumnNumber = r.Column

You can wrap this in a function to make it a bit more flexible:

Function HeaderToColumnNo(HeaderText, TheSheet, HeaderRow, Optional ReportNotFound As Boolean = True)
HeaderToColumnNo = 0  'impossible column no.
Set r = TheSheet.Rows(HeaderRow).Find(What:=HeaderText, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)  'hard code but OK for now
If Not r Is Nothing Then
  HeaderToColumnNo = r.Column
Else
  If ReportNotFound Then MsgBox "Couldn't find '" & HeaderText & "' in row " & HeaderRow & " of the " & TheSheet.Name & " sheet"
End If
End Function

which you'd use in code like:

x = HeaderToColumnNo("AS_DESCRIPTION", Sheets("Sheet1"), 1, False) 'looks in first row of sheet1 for "AS_DESCRIPTION"
x = HeaderToColumnNo("AS_DESCRIPTION_LD", Sheets("Sheet5"), 2, True) 'looks in 2nd row of sheet5 for "AS_DESCRIPTION_LD"
'if you want the letter(s) of the column then something like:
Columnletter = Split(Cells(1, x).Address, "$")(1)

[vjay883]

Hi,


Sorry I haven't responded as been mega busy doing other project work. The development aspect of my role has come to a halt. If I do get time to try this code then I will post the results etc.

Many Thanks for your replies!
V