Insert a new column next to a predefined column name with formula

christeen

New Member
Joined
Nov 25, 2011
Messages
3
Hi,

I'm new in macro. I've browsed through some threads on how to insert a column.
But most of the answers used column number(e.g: "J2"). How should i write to insert a column next to a column name "Y-size"?
ColF is the new inserted column with a formula. I want the formula to be autofilled too.

example:
original data

ColA ColB ColE ColF
Item X-size Y-size Area
Table001 10 20 10*20
Table002 25 10 25*10

Thanks in advanced.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
Hi and welcome to the board

Maybe something like
(assumes data in Sheet1 and headers in row 1)

Try it in a copy of your workbook

Code:
Sub aTest()
    Dim newCol As Long, lastRow As Long
    Dim rngFound As Range
 
    With Sheets("Sheet1") '<---Adjust the sheetname
        Set rngFound = .Range("1:1").Find(What:="Y-Size") '<--Assumes headers in Row 1
 
        If Not rngFound Is Nothing Then
            newCol = rngFound.Column + 1
           .Columns(newCol).Insert
           .Cells(1, newCol) = "Area"
           lastRow = .Cells(.Rows.Count, newCol - 1).End(xlUp).Row
           Range(.Cells(2, newCol), .Cells(lastRow, newCol)) _
            .FormulaR1C1 = "=RC[-2]*RC[-1]"
        End If
 
    End With
End Sub

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
oops...

columns X-Size and Y-Size are not contiguous

Try this new version

Code:
Sub bTest()
    Dim newCol As Long, lastRow As Long, colXSize As Long
    Dim rngFound1 As Range, rngFound2 As Range
    
    With Sheets("Sheet1") '<---Adjust the sheetname
    
        'Assumes headers in row 1
        Set rngFound1 = .Range("1:1").Find(What:="Y-Size")
        Set rngFound2 = .Range("1:1").Find(What:="X-Size")
    
        If Not rngFound1 Is Nothing And Not rngFound2 Is Nothing Then
            newCol = rngFound1.Column + 1
            colXSize = rngFound2.Column
           .Columns(newCol).Insert
           .Cells(1, newCol) = "Area"
           lastRow = .Cells(.Rows.Count, newCol - 1).End(xlUp).Row
           Range(.Cells(2, newCol), .Cells(lastRow, newCol)) _
            .FormulaR1C1 = "=RC[-1]*RC[" & colXSize - newCol & "]"
        End If
        
    End With
End Sub
 

christeen

New Member
Joined
Nov 25, 2011
Messages
3
oops...

columns X-Size and Y-Size are not contiguous

Try this new version

Code:
Sub bTest()
    Dim newCol As Long, lastRow As Long, colXSize As Long
    Dim rngFound1 As Range, rngFound2 As Range
    
    With Sheets("Sheet1") '<---Adjust the sheetname
    
        'Assumes headers in row 1
        Set rngFound1 = .Range("1:1").Find(What:="Y-Size")
        Set rngFound2 = .Range("1:1").Find(What:="X-Size")
    
        If Not rngFound1 Is Nothing And Not rngFound2 Is Nothing Then
            newCol = rngFound1.Column + 1
            colXSize = rngFound2.Column
           .Columns(newCol).Insert
           .Cells(1, newCol) = "Area"
           lastRow = .Cells(.Rows.Count, newCol - 1).End(xlUp).Row
           Range(.Cells(2, newCol), .Cells(lastRow, newCol)) _
            .FormulaR1C1 = "=RC[-1]*RC[" & colXSize - newCol & "]"
        End If
        
    End With
End Sub

Thanks Marcelo. It works!:LOL:

Erm, i hope you don't mind if i add another inquiry here.
What if die size info is stated as 10X20...i need to use "text to column" to separate die size in two different columns with die size x=10 and die size y=20.
Can you pls help?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,741
Members
414,171
Latest member
12Rev79

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
Top