Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert column
Hello,
I have a worksheet which records special prices for certain customers, all other customers using a fixed price on the main worksheet within the workbook. If a customer now needs to be included in those with the special price, then their details have to be included in the Special Prices sheet. The way that I do this at present is to inseert a column, add in some references to ensure that they are included in the various lookup tables and then input their specific information. I now want someone else to be able to do this for me, so need to automate the insertion of the columns and setting up of the references for the lookups. I have been able to do this via a macro, however, there is one part that I am having a problem with and that is the insertion of the column. If I record the macro, then it lets me insert in (say) column "P", however, next time I will want this to be column "Q". Is there a way of getting Excel to either prompt for a column reference (in a similar way to a parameter query in Access), or is there an easy way to find the last used column and then move back one before inserting? Regards Colin Foster |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert column
Colin,
This will give you the last used column in Row 1 Dim lastColumn As Long lastColumn = Cells(1, 100).End(xlToLeft).Column MsgBox lastColumn Neil "Colin Foster" wrote in message ... Hello, I have a worksheet which records special prices for certain customers, all other customers using a fixed price on the main worksheet within the workbook. If a customer now needs to be included in those with the special price, then their details have to be included in the Special Prices sheet. The way that I do this at present is to inseert a column, add in some references to ensure that they are included in the various lookup tables and then input their specific information. I now want someone else to be able to do this for me, so need to automate the insertion of the columns and setting up of the references for the lookups. I have been able to do this via a macro, however, there is one part that I am having a problem with and that is the insertion of the column. If I record the macro, then it lets me insert in (say) column "P", however, next time I will want this to be column "Q". Is there a way of getting Excel to either prompt for a column reference (in a similar way to a parameter query in Access), or is there an easy way to find the last used column and then move back one before inserting? Regards Colin Foster |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert column
Hi,
here it goes: Dim i As Integer Dim t As Range Set t = Application.InputBox("Click the last column", "Some Title Here", Type:=8) ' 1) i = t.Column Columns(i).EntireColumn.Insert i = Application.InputBox("Column number?", "Some Title Here", Type:=1) ' 2) Columns(i).EntireColumn.Insert i = Range("IV1").End(xlToLeft).Column ' 3) finds the last column without asking the user Columns(i).EntireColumn.Insert i = Cells(1, 256).End(xlToLeft).Column ' 4) same here Columns(i).EntireColumn.Insert regards Tomek "Colin Foster" wrote in message ... Hello, I have a worksheet which records special prices for certain customers, all other customers using a fixed price on the main worksheet within the workbook. If a customer now needs to be included in those with the special price, then their details have to be included in the Special Prices sheet. The way that I do this at present is to inseert a column, add in some references to ensure that they are included in the various lookup tables and then input their specific information. I now want someone else to be able to do this for me, so need to automate the insertion of the columns and setting up of the references for the lookups. I have been able to do this via a macro, however, there is one part that I am having a problem with and that is the insertion of the column. If I record the macro, then it lets me insert in (say) column "P", however, next time I will want this to be column "Q". Is there a way of getting Excel to either prompt for a column reference (in a similar way to a parameter query in Access), or is there an easy way to find the last used column and then move back one before inserting? Regards Colin Foster |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert column
some autoformatting spoiled the layout.... it should look like this:
Dim i As Integer Dim t As Range ' 1) Set t = Application.InputBox("Click the last column", "Some Title Here", _ Type:=8) i = t.Column Columns(i).EntireColumn.Insert ' 2) i = Application.InputBox("Column number?", "Some Title Here", Type:=1) Columns(i).EntireColumn.Insert ' 3) finds the last column without asking the user i = Range("IV1").End(xlToLeft).Column Columns(i).EntireColumn.Insert ' 4) same here i = Cells(1, 256).End(xlToLeft).Column Columns(i).EntireColumn.Insert "Tomek" wrote in message ... Hi, here it goes: Dim i As Integer Dim t As Range Set t = Application.InputBox("Click the last column", "Some Title Here", Type:=8) ' 1) i = t.Column Columns(i).EntireColumn.Insert i = Application.InputBox("Column number?", "Some Title Here", Type:=1) ' 2) Columns(i).EntireColumn.Insert i = Range("IV1").End(xlToLeft).Column ' 3) finds the last column without asking the user Columns(i).EntireColumn.Insert i = Cells(1, 256).End(xlToLeft).Column ' 4) same here Columns(i).EntireColumn.Insert regards Tomek |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert column
Thanks to both Neil & Tomek...not had chance to ry out either of your
suggestions, yet, but will do soon. Thanks for your input Regards Colin "Colin Foster" wrote in message ... Hello, I have a worksheet which records special prices for certain customers, all other customers using a fixed price on the main worksheet within the workbook. If a customer now needs to be included in those with the special price, then their details have to be included in the Special Prices sheet. The way that I do this at present is to inseert a column, add in some references to ensure that they are included in the various lookup tables and then input their specific information. I now want someone else to be able to do this for me, so need to automate the insertion of the columns and setting up of the references for the lookups. I have been able to do this via a macro, however, there is one part that I am having a problem with and that is the insertion of the column. If I record the macro, then it lets me insert in (say) column "P", however, next time I will want this to be column "Q". Is there a way of getting Excel to either prompt for a column reference (in a similar way to a parameter query in Access), or is there an easy way to find the last used column and then move back one before inserting? Regards Colin Foster |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Column with macro | Excel Discussion (Misc queries) | |||
Macro- Column Insert | Excel Discussion (Misc queries) | |||
Macro- Column Insert | Excel Discussion (Misc queries) | |||
Macro- Column Insert | Excel Discussion (Misc queries) | |||
Trying to insert a column using variable in a Macro | Excel Discussion (Misc queries) |