To find the column with its column heading
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 |
To find the column with its column heading
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 |
To find the column with its column heading
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 |
To find the column with its column heading
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 |
To find the column with its column heading
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 |
To find the column with its column heading
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 |
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 |
To find the column with its column heading
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 |
To find the column with its column heading
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 |
All times are GMT +1. The time now is 02:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com