How to avoid using select in VBA for variable cell ranges?

Question

I have heard of the dislike for using .select in VBA for excel macros, but I am wondering how my particular goal can be achieved without its use? For example, say there is a cell(used as a header) with the value "Commodity". Beneath it, all cells need to have a VLookup function. However, on each and every iteration of the macro, the column will shift (as new columns are added) and new rows will be added (so that newly added rows will need to have the function added as well). How is it possible to consistently locate this Commodity column and find its lowest unfilled row? It is very simple to do using select:

Do Until ActiveCell.Value = "Commodity"
Activecell.offset(0,1).select
loop
Do Until ActiveCell.Value = ""
ActiveCell.offset(1,0).select
loop

Obviously, I would prefer to avoid using this type of syntax, but I do not know how to get around it. All answers I have seen regarding the avoidance of select appear to set, for example, rng = Cell(x,y) or something, but they are always known-location cells. I do not know how to do this without utilizing select to check cell values.

Answer 1

First find the column that your Sting is located, then count the rows beside it, set your range and enter the formula.

Sub FindColumn()
    Dim f As Range, c As Integer
    Dim LstRw As Long, rng As Range

    Set f = Rows(1).Find(what:="Commodity", lookat:=xlWhole)

    If Not f Is Nothing Then
        c = f.Column
    Else: MsgBox "Not Found"
          Exit sub

    End If

    LstRw = Cells(Rows.Count, c - 1).End(xlUp).Row
    Set rng = Range(Cells(2, c), Cells(LstRw, c))
    rng = "My Formula"

End Sub

Answer 2

Here are two iterate rows to based on the ActiveCell.

Sub Examples()

    Dim Target As Range
    Dim x As Long

    Set Target = ActiveCell

    Do Until Target.Value = "Commodity"
        Set Target = Target.Offset(0, 1)
    Loop

    Do Until ActiveCell.Offset(x, 1).Value = ""
        x = x + 1
    Loop

End Sub

Answer 3

Assuming the wanted header IS there, you can use this function:

Function FindLowestUnfilledCell(headerRow As Range, header As String) As Range
    With headerRow.Find(What:=header, lookat:=xlWhole, LookIn:=xlValues, MatchCase:=False) '<--| look for header in passed row
        Set FindLowestUnfilledCell = headerRow.Parent.Cells(headerRow.Parent.Rows.Count, .Column).End(xlUp)
    End With
End Function

to be used by your main sub as follows

Sub main()

    FindLowestUnfilledCell(Rows(1), "Commodity").Formula = "myformula"
End Sub

should the absence of the wanted header be handled, the same function gets a little longer like follows

Function FindLowestUnfilledCell(headerRow As Range, header As String) As Range
    Dim r As Range
    Set r = headerRow.Find(What:=header, lookat:=xlWhole, LookIn:=xlValues, MatchCase:=False) '<--| look for "Commodity" in row 1
    If Not r Is Nothing Then Set FindLowestUnfilledCell = headerRow.Parent.Cells(headerRow.Parent.Rows.Count, r.Column).End(xlUp)
End Function

and its exploitation would consequently take into account the possibility of not founding the wanted header:

Sub main()
    Dim lowestUnfilledRange As Range

    Set lowestUnfilledRange = FindLowestUnfilledCell(Rows(1), "Commodity")
    If Not lowestUnfilledRange Is Nothing Then lowestUnfilledRange.Formula = "myformula"
End Sub

Answer 4

I want to simplify the answer a bit. For example

Set r = ActiveCell
MsgBox r.Address    ' $A$1
Columns("A").Insert ' insert column before the first column
MsgBox r.Address    ' $B$1

so you can change your code to

Dim cell As Range   ' optional
Set cell = ActiveCell
While cell = "Commodity"
    Set cell = cell(, 2)    ' similar to Set cell = cell.Resize(1,1).Offset(, 1)
Wend
While cell = ""
    Set cell = cell(, 2)
Wend