rider
04-01-2013, 11:26 PM
Hi All... I am new to VBA. I am creating a macro to do some filtering and vlookup with 2 Excels. But I am stuck now at the last stage where,
1. I need to compare the cells in one row (the value of the cells are the result of another function MONTH, from the row 3)
2. And when there is a difference in the value of two cells, i need to insert a column in between those two cells (example: i need to insert a column after the December and before January)
3. And name the first cell (name December)
4. And insert a SUM function for the entire cells in that column to add the cells after the last insertion of last column.
I did try macro recording, but you know i am stuck at expanding it.
I am pasting the code i recorded below, for the reference.
Thanks in advance.
********
Sub Monthcompare()
'
' Monthcompare Macro
'
' Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("G2").Select
ActiveCell.FormulaR1C1 = "=MONTH(R[-1]C)"
Range("G1").Select
Selection.End(xlToRight).Select
Range("DA22").Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.FillRight
'Range("G2").Select
'Selection.AutoFill Destination:=Range("G2:DI2"), Type:=xlFillDefault
'Range("G2:DI2").Select
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("K1").Select
ActiveCell.FormulaR1C1 = "'October"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("K3").Select
Selection.AutoFill Destination:=Range("K3:K203")
Range("K3:K203").Select
Columns("P:P").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("P1").Select
ActiveCell.FormulaR1C1 = "'November"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("P3").Select
Selection.AutoFill Destination:=Range("P3:P203")
Range("P3:P203").Select
Columns("V:V").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("V1").Select
ActiveCell.FormulaR1C1 = "'January"
Range("V3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
Range("V3").Select
Selection.AutoFill Destination:=Range("V3:V203")
Range("V3:V203").Select
Windows("Test1.xlsx").Activate
End Sub
********
1. I need to compare the cells in one row (the value of the cells are the result of another function MONTH, from the row 3)
2. And when there is a difference in the value of two cells, i need to insert a column in between those two cells (example: i need to insert a column after the December and before January)
3. And name the first cell (name December)
4. And insert a SUM function for the entire cells in that column to add the cells after the last insertion of last column.
I did try macro recording, but you know i am stuck at expanding it.
I am pasting the code i recorded below, for the reference.
Thanks in advance.
********
Sub Monthcompare()
'
' Monthcompare Macro
'
' Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("G2").Select
ActiveCell.FormulaR1C1 = "=MONTH(R[-1]C)"
Range("G1").Select
Selection.End(xlToRight).Select
Range("DA22").Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.FillRight
'Range("G2").Select
'Selection.AutoFill Destination:=Range("G2:DI2"), Type:=xlFillDefault
'Range("G2:DI2").Select
Columns("K:K").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("K1").Select
ActiveCell.FormulaR1C1 = "'October"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("K3").Select
Selection.AutoFill Destination:=Range("K3:K203")
Range("K3:K203").Select
Columns("P:P").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("P1").Select
ActiveCell.FormulaR1C1 = "'November"
Range("P3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("P3").Select
Selection.AutoFill Destination:=Range("P3:P203")
Range("P3:P203").Select
Columns("V:V").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("V1").Select
ActiveCell.FormulaR1C1 = "'January"
Range("V3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
Range("V3").Select
Selection.AutoFill Destination:=Range("V3:V203")
Range("V3:V203").Select
Windows("Test1.xlsx").Activate
End Sub
********