To find the column with its column heading
Try this:
sccolumn = WorksheetFunction.Match("STOCK CODE", Rows("1:1"), 0)
lastrow = Cells(Rows.Count, sccolumn).End(xlUp).Row
Cells(1, sccolumn).EntireColumn.Insert
Cells(1, sccolumn).Value = "ITEMSIZE"
Cells(2, sccolumn).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])))))"
Cells(2, sccolumn).AutoFill Destination:=Range(Cells(2, sccolumn),
Cells(lastrow, sccolumn)), Type:=xlFillDefault
Regards,
Stefi
€˛pol€¯ ezt Ć*rta:
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
|