Autofill Question

[duplinguy]

I have a macro that finds a column called Product Type, inserts a blank column and then inserts a formula 1 row down. I would like to Autofill that formula down in my macro. Problem is, Product Type could be in different columns depending on which report I download into Excel. I have found the following code on this site but it only autofills from cell B2. Can it be modified to Autofill my formula depending on where the Product Type column is located?

Range("a2").Select
fillrange = Range(Selection, Selection.End(xlDown)).Offset(0, 1).Address
Set SourceRange = Worksheets("Sheet1").Range("b2")
SourceRange.AutoFill Destination:=Range(fillrange)

[Tom Schreiner]

Hi
This does what your macro does plus
it will fill down the fromula based
upon the rows of data in Column A
Edit to suit.
Tom

Sub FindColumn()

Dim c, LastRow

LastRow = Range("A1:A" & Range("A65536").End(xlUp).Row).Rows.Count

For Each c In Range("A1:IV1")

    If c = "Product Type" Then

        Columns(c.Column + 1).EntireColumn.Insert Shift:=xlToRight

        'replace with your formula

        Cells(2, c.Column + 1).Formula = _

        "=sum(" & Cells(2, c.Column).Address & " + 1)"

        Range(Cells(2, c.Column + 1), _

        Cells(LastRow, c.Column + 1)).FillDown

        Exit Sub

    End If

Next

End Sub

[duplinguy]

THANK YOU! That's exactly what I was looking for. You're the Man!!

[Brabantio]

Hi TsTom

I'm not trying to be critical, but you might find the following alternative interesting :-

Sub FindColumn()
Dim c%, lastRow#
lastRow = [A65536].End(xlUp).Row
c = [A1:IV1].Find("Product Type").Column + 1
Columns(c).Insert
Range(Cells(2, c), Cells(lastRow, c)).Formula = _
"=SUM(" & Cells(2, c - 1).Address(False, False) & " + 1)"
End Sub

[Tom Schreiner]

No offense here...
I'm a beginner and have taken alot of pleasure from learning on this site...
Tom