Autofill Question

duplinguy

Board Regular
Joined
Apr 25, 2002
Messages
95
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)
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

<pre>

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

</pre>
 
Upvote 0
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
 
Upvote 0
No offense here...
I'm a beginner and have taken alot of pleasure from learning on this site...
Tom
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top