Variable columns = how to insert formula

[shumonsaha]

I have to work on sheets with variable columns.

So for eg Col A Col B Col C Col D are fixed and then there are variable no of columns - Col E Col F and so on....

I have to insert columns after the last colum ie in Col F in this case.

However, the new column (lets call it Col G) will give a value with formula that connects Col E value with Col B (fixed)value. Again Col H (another new col) will also give a value with formula connecting Col H with Col B(fixed).

Easy to do in normal scenario but when I record macro in excel ( i cannot write VB), the variable columns make it difficult.

Help requested. Can clarify if required.

 

[texasalynn]

so you create a variable to the last column, like this

Dim LC as Long
LC = Range("IV1").End(xlToLeft).Column

this will find the last column with data in row one.

 

[shumonsaha]

thanks. as i explained i dont know coding at all. have to depend on the record macro option. is there any way I can use an excel formula for this and record in macro?

 

[texasalynn]

no what you will do is record your macro, then can put that into your macro and use it. So I would suggest you do the record macro, post back here with the code and how it need to use the last column and we will help you work it out.

BTW that is a great way to start learning VBA, and then you will be hooked.

 

[shumonsaha]

Sure Texa.. Would be fun to learn.... Here it goes.


Sub Variablecolumn()
'
' Variablecolumn Macro
'
'
Rows("3:5").Select
Selection.Delete Shift:=xlUp
Range("P1").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("P:Q").Select
Selection.Delete Shift:=xlToLeft
Columns("Q:T").Select
Selection.Delete Shift:=xlToLeft
Columns("R:U").Select
Selection.Delete Shift:=xlToLeft
Columns("S:U").Select
Selection.Delete Shift:=xlToLeft
Range("P1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("P1:R1").Select
Selection.Cut
Range("P2").Select
ActiveSheet.Paste
Selection.Copy
Range("S2").Select
ActiveSheet.Paste
Range("S1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Nor"
Range("S1:U1").Select
Selection.FillRight
Rows("1:1").Select
Range("D1").Activate
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("S2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,R[2]C)"
Range("S2:U2").Select
Selection.FillRight
Selection.Copy
Range("S4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("S2:U3").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("S5").Select
ActiveCell.FormulaR1C1 = "=RC[-3]*R5C9/10"
Range("S5:U5").Select
Selection.FillRight
Selection.Replace What:="$", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("S5:U5").Select
Selection.AutoFill Destination:=Range("S5:U50686")
Range("S5:U50686").Select
End Sub

 

[texasalynn]

ok so I will try to take a look at that, but trying to understand a little more now when that code. Where is the last column coming into play?

The first thing to learn about macro record and cleaning up your code is that where you see for example the Range(....).Select and then next line Selection.Copy, you can clean that up as Range(....).Copy (1 line the .... would be the range shown in the macro)

 

[shumonsaha]

Hi. To clarify further, the variable columns in this case are P, Q and R and the formulas connect to Col I to give values in S, T and U

So, if there is a new col S, the inserted columns V will also be inserted by macro...

am I getting to confusing?

 

[shumonsaha]

Hi Texa, One correction. The column P is fixed and common for all sheets.Its Col Q onwards which is variable.

I have to delete P since its not required.

However , then I have a selection of 6 columns -- QRSTU which is ia series and only R has to be kept and connected by formula to I

Again a series VWXYZ out of which only W has to be kept and connected by formula with I again.

Hope its clear.

 

[texasalynn]

I'm not getting a visual of what you are wanting. Maybe you could post your worksheet on one of the free sites. To share your file, upload it to box.net


I did do a little cleanup of your macro

Sub Variablecolumn()
'
' Variablecolumn Macro
Dim LC As Long
LC = Range("IV1").End(xlToLeft).Column
'
'
Rows("3:5").Delete Shift:=xlUp
Range("P1").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("P:Q").Delete Shift:=xlToLeft
Columns("Q:T").Delete Shift:=xlToLeft
Columns("R:U").Delete Shift:=xlToLeft
Columns("S:U").Delete Shift:=xlToLeft
Range("P1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("P1:R1").Cut
Range("P2").Select
ActiveSheet.Paste
Selection.Copy
Range("S2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("S1").FormulaR1C1 = "Nor"
Range("S1:U1").FillRight
Rows("1:1").Select
Range("D1").Activate
Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("S2").FormulaR1C1 = "=CONCATENATE(R[1]C,R[2]C)"
Range("S2:U2").FillRight
Range("S2:U2").Copy
Range("S4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("S2:U3").ClearContents
Range("S5").FormulaR1C1 = "=RC[-3]*R5C9/10"
Range("S5:U5").FillRight
Selection.Replace What:="$", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("S5:U5").AutoFill Destination:=Range("S5:U50686")

End Sub

 

[shumonsaha]

http://www.box.net/shared/mv0e8fh87p

uploaded the file... also added a new sheet of data to test the macro