Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Autofill Question

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4

    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •