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

[christeen]

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.

 

[Marcelo Branco]

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

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]

oops...

columns X-Size and Y-Size are not contiguous

Try this new version

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]

oops...

columns X-Size and Y-Size are not contiguous

Try this new version

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!

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?