VBA to find column by name and reference values from it in a formula

Question

I'm new to vba, and i only know what i've had to use (not much) and i've tried to frankenstein something together by copying all of your helpful suggestions in other threads, but i'm making a mess of it.

• I need to find a column by name (won't always be in the same place)
• I need to fill the first open column with a formula that references the column i just found (won't always be in the same place)

• If i can write a header name, that'd be swell.

  Sub Labels()
  Dim NextEmptyCol As Long
  Dim lastRow As Long
  
  
  SearchV = Range("A1:DD1").Find(What:="team_id", LookIn:=xlValues, LookAt:=xlWhole, _
  MatchCase:=False, SearchFormat:=False).Column
  
  lastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
  
  NextEmptyCol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column + 1
  Range(lastRow & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=b1+c1"
  
  End Sub
  

  I have no idea what i'm doing - this is cobbled from other forum threads, but i thought SearchV would find the column, lastRow would find how many cells needed to be filled, and NextEmptyCol would put them in the next column. (b1+c1 is a just a placeholder formula - i didn't know how to refer to the column i found in the first bit.

What a mess.

Answer 1

I believe this might be what you are looking for:

Sub subLabels()

Dim NextEmptyCol As Long
Dim lastRow As Long
Dim rngFound As Range

'Change the sheet name in the next line (if necessary) to the correct one
With Worksheets("Sheet1")
    Set rngFound = .Range("A1:DD1").Find(What:="team_id", LookIn:=xlValues, LookAt:=xlWhole, _
        MatchCase:=False, SearchFormat:=False)

    'Proceed only if "team_id" was found
    If Not rngFound Is Nothing Then
        lastRow = .Range("B" & .Rows.Count).End(xlUp).Row
        NextEmptyCol = .Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column + 1
        .Cells(lastRow, NextEmptyCol).Formula = "=B" & lastRow & "+C" & lastRow
    Else
        MsgBox "Column 'team_id' not found." & Chr(10) & "Aborting!"
    End If
End With

End Sub

Here are the changes:

1. I added the reference to a particular sheet instead ActiveSheet in case the macro runs while another sheet is active. Otherwise, that will mess up your file. You might have to change the sheet name there.
2. Exchanged the method Range for Cells in the line where you are setting the formula.
3. Include the possibility that the column "team_id" could not be found.
4. Made the formula modular. Otherwise, the formula in any row would be =B1+C1. Instead you probably want it to be =B25+C25 in row 25. If that's not the case then you can easily change it back.

_{Note: don't hesitate to mark any key-word in the code and then press F1 to learn more about it. For example: highlight the word cells and then press F1 to learn the difference between cells and range.}

Answer 2

If I got you right, you want to go for the column after the last used, and fill it with a formula which is =[column B] + [column with search term]. This should do it:

Sub DoFomula()

  Dim SearchV As Long
  Dim myRange As Range

  'column to write in / offset for headers
  Set myRange = Cells(1, Columns.Count).End(xlToLeft).Offset(1, 1)

  'height we need
  Set myRange = myRange.Resize(Range("B" & Rows.Count).End(xlUp).Row - 1, 1)

  'column we do look for
  SearchV = Application.Match("team_id", Rows(1), 0)

  'input formula
  myRange.FormulaR1C1 = "=RC2 + RC" & SearchV

  'your header
  myRange.Offset(-1).Resize(1).Value = "My New Header"

End Sub

Should be self explaining, but if you still have any questions, just ask :)

Answer 3

Your problem is that you're referencing a range with two rows: lastRow & Cells(Rows.Count, "A").End(xlUp).Row. I think you want Range(NextEmptyCol & lastRow).Formula = "=B1+C1"