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

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

Sub insertCol()
For i = 1 To Columns.Count
If Cells(1, i).Value = "STOCK CODE" Then
Cells(1, i).EntireColumn.Insert
Exit Sub
End If
Next
End Sub
--
Gary''s Student - gsnu200806

Hi,

This will find 'Stock Code ' in row 1 and insert a column to the left

Range("IV1").End(xlToLeft).Find(What:="stock code", _
MatchCase:=False).EntireColumn.Insert

Mike

"pol" wrote:

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

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

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

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

Brilliants, thanks very much

"pol" wrote:

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

thanks very mcuh

"pol" wrote:

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