PDA

View Full Version : Variable column reference for a formula



vjay883
09-17-2014, 02:44 AM
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
09-17-2014, 03:31 AM
I can used a named range for the columns but the rows and columns will still be hardcoded when creating the range.

p45cal
09-17-2014, 08:19 AM
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
09-29-2014, 02:03 AM
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