Option Explicit
Sub testme()

Dim res As Variant
Dim wks As Worksheet
Dim LastRow As Long
Dim myFormula As String

Set wks = ActiveSheet

With wks

res = Application.Match("STOCK CODE", .Rows(1), 0)
If IsError(res) Then
MsgBox "Stock Code not found!"
Exit Sub
End If

LastRow = .Cells(.Rows.Count, res).End(xlUp).Row

.Columns(res).Insert

'add header in row 1
.Cells(1, res).Value = "Item size"

myFormula = "=LOOKUP(6.022*10^23,--MID(RC[1]," _
& "MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]" _
& "&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))"

.Range(.Cells(2, res), .Cells(LastRow, res)).FormulaR1C1 = myFormula

End With
End Sub

pol wrote:

Thanks very much working fine .Please help me for the following also

Also Please I have to give a heading 'ITEMSIZE' for that new column and have
to execute the following formula on that new column. I have to copy all
numeric values from the column which are on the right side of the in new
column which we created before ('ITEMSIZE).

The following foumula only worked out on the assumption of the STOCKCODE
WILL BE ALWAYS IN COLUMN B1.

ActiveCell.FormulaR1C1 = _

"=LOOKUP(6.022*10^23,--MID(RC[1],MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))"
Range("B2").Select

Dim Wks As Worksheet
Dim LastRow As Long

Set Wks = ActiveSheet

With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.Range("B2:B" & LastRow).FormulaR1C1 = .Range("B2").FormulaR1C1
End With

"Stefi" wrote:

Cells(1, WorksheetFunction.Match("STOCK CODE", Rows("1:1"),
0)).EntireColumn.Insert

Regards,
Stefi

€žpol€� ezt Ã*rta:

I have an excel sheet with column heading 'STOCK CODE' I want to insert a
column just left side of that column 'STOCK CODE' where it is. please let me
know to write a macros.

With thanks
Pol

--

Dave Peterson