Macro to find column, insert new column to right insert formula and copy formula down

R

robertguy

Board Regular
Joined
May 1, 2008
Messages
121
Hi.

can any please advise me of the macro code which will :-<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>

(a) find a column header ‘Date Details’ which can be located anywhere in row 1 between columns A to IV

(b) once located insert a new column to its right<o:p></o:p>
<o:p></o:p>
<o:p></o:p>(c) rename the new column header i.e. first cell in column to ‘Extract Date’<o:p></o:p>

(d) copy the formula ‘=MID(F2,3,10)’ in first cell under the ‘Extract Date’ header and then copy the formula down this new column to the last corresponding cell where there is no data ‘Date Details’. <o:p></o:p>
<o:p></o:p>
Any assistance in this matter would be greatly appreciated


Many thanks in advance

Rob

N.B. Excel version 2003
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Try

Code: 
Sub Insrt()
Dim Found As Range
Dim LR As Long
Set Found = Rows(1).Find(what:="Date Details", LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then Exit Sub
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
Found.Offset(, 1).EntireColumn.Insert
Cells(1, Found.Column + 1).Value = "Extract Date"
Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula = "=MID(F2,3,10)"
End Sub
 
Upvote 0
VoG,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Excellent !! It works exactly as required - you have save me many many frustrating hours doing this manually. :)<o:p></o:p>
<o:p></o:p>
Many thanks<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Rob<o:p></o:p>
 
Upvote 0
Vog,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
many thanks but I've made one massive over sight....whilst I wanted the formula '=MID(F2,3,10)' to be copied i.e.<o:p></o:p>
<o:p> </o:p>
copy the formula ‘=MID(F2,3,10)’ in first cell under the ‘Extract Date’ header and then copy the formula down this new column to the last corresponding cell where there is no data ‘Date Details’. <o:p></o:p>
<o:p> </o:p>
I did not take into consideration that the column 'Date Details' will not necessarily always be in column F (like I had it in my test workbook) as it could be any in the range of A : IV<o:p></o:p>
<o:p> </o:p>
Could you amend your code to work out what column the 'Date Details' header is in column which would them amend the formula to be copied into the 'Extract Date’ column e.g.<o:p></o:p>
<o:p> </o:p>
if 'Date Details' was in column 'G' the formula to be copied into column Extract Date’ would be '=MID(G2,3,10)'<o:p></o:p>
<o:p> </o:p>
if 'Date Details' was in column 'J' the formula to be copied into column Extract Date’ would be '=MID(J2,3,10)'<o:p></o:p>
<o:p> </o:p>
etc etc<o:p></o:p>
<o:p> </o:p>
many thanks<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Rob<o:p></o:p>
 
Upvote 0
Try

Rich (BB code): 
Sub Insrt()
Dim Found As Range
Dim LR As Long
Set Found = Rows(1).Find(what:="Date Details", LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then Exit Sub
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
Found.Offset(, 1).EntireColumn.Insert
Cells(1, Found.Column + 1).Value = "Extract Date"
Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).FormulaR1C1 = "=MID(RC[-1],3,10)"
End Sub
 
Upvote 0
VoG,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Simply Excellent !!

You Are A Life Saver!! :)<o:p></o:p>
<o:p></o:p>
Many many thanks<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Rob<o:p></o:p>
 
Upvote 0
Vog

can you advise me how I would adapt your code to be another formula such as A2+B2 as I tried to amend the last line in your code but it did not work

Code:
Sub Insrt()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Dim Found As Range<o:p></o:p>
Dim LR As Long<o:p></o:p>
Set Found = Rows(1).Find(what:="Date Details", LookIn:=xlValues, lookat:=xlWhole)<o:p></o:p>
If Found Is Nothing Then Exit Sub<o:p></o:p>
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row<o:p></o:p>
Found.Offset(, 1).EntireColumn.Insert<o:p></o:p>
Cells(1, Found.Column + 1).Value = "Extract Date"<o:p></o:p>
Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).FormulaR1C1 = "=MID(RC[-1],3,10)"<o:p></o:p>
End Sub
Code:

Many thanks

Rob
 
Upvote 0
Try like this

Code: 
Sub Insrt()
Dim Found As Range
Dim LR As Long
Set Found = Rows(1).Find(what:="Date Details", LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then Exit Sub
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
Found.Offset(, 1).EntireColumn.Insert
Cells(1, Found.Column + 1).Value = "Extract Date"
Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula = "=A2+B2"
End Sub
 
Upvote 0
I know this an old post but its very close to what am looking for. But i can't seem to get it to work. I want to find a column with the word NAME and insert a column and use this formula =IF(B2=B1,"yes","no") to the column with the word name.


for example if name is in column D i want to insert column E with this formula to use the column before it so in this case it would be D and to continue to the last row with data.

then i want column with NAME to sum at the bottom using this formula to the column that was inserted... =COUNTIF(H2:H100,"no")

if someone can help it would be great as i do this manually right now and am in still the process of trying to get this to work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
VBA Name New column & Use Column Header as Range
Replies
7
Views
517
Peter_SSs
Peter_SSs
K
  • Solved
Insert Row Include Formulas - Protected Sheet
Replies
3
Views
296
Kirrash
K
X
  • Question
Macro to fill an identifier down and SUMIF
Replies
4
Views
164
xbrianx
X
theYaniac
  • Solved
I would like to insert a blank row between each new unique identifier in column A.
Replies
3
Views
156
MARK858
MARK858
L
  • Solved
Referencing tables in formula
2
Replies
13
Views
360
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,659
Messages
6,120,786
Members
448,994
Latest member
rohitsomani

Share this page

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