To the last rowClosed

Question

Hello,

I need to know how to just paste my formula's on the right row, up to the amount of data on the left row, instead a fix number.

Could you please help? My code is below.


Sub Brij2()
'
' Brij2 Macro
' Macro recorded 21/08/2009 by vf63028
'

'
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=MONTH(TODAY())"
    Columns("J:J").Select
    Selection.NumberFormat = "0"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]<10,""M0""&RC[-1],""M""&RC[-1])"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("J:J").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("J2").Select
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    Range("B3").Select
    Selection.AutoFill Destination:=Range("B3:B10914")
    Range("B3:B10914").Select
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-2],'Paste Data'!C1:C14,2,0)"
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Columns("C:C").EntireColumn.AutoFit
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],5,9)"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C10914")
    Range("C2:C10914").Select
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D10914")
    Range("D2:D10914").Select
    Columns("C:D").Select
    With Selection.Interior
        .ColorIndex = 8
        .Pattern = xlSolid
    End With
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-4],'Paste Data'!C1:C14,4,0)"
    Selection.AutoFill Destination:=Range("E2:E10914")
    Range("E2:E10914").Select
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-5],'Paste Data'!C1:C14,7,0)"
    Selection.AutoFill Destination:=Range("F2:F10914")
    Range("F2:F10914").Select
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Columns("F:G").Select
    With Selection.Interior
        .ColorIndex = 40
        .Pattern = xlSolid
    End With
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=IF(C[-1]=""Cr"",""D"",""C"")"
    Selection.AutoFill Destination:=Range("G2:G10914")
    Range("G2:G10914").Select
    Columns("H:H").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    Columns("J:J").EntireColumn.AutoFit
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-7],'Paste Data'!C1:C14,5,0)"
    Selection.AutoFill Destination:=Range("H2:H10914")
    Range("H2:H10914").Select
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-4],'FX Rates'!C[-7]:C[-4],4,0)"
    ActiveCell.FormulaR1C1 = _
        "=IF(C[-4]=""GBP"",1,VLOOKUP(C[-4],'FX Rates'!C[-7]:C[-4],4,0))"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I10914")
    Range("I2:I10914").Select
    Range("H8").Select
    Selection.End(xlDown).Select
    Range("H10910").Select
    Selection.End(xlUp).Select
    Columns("H:H").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J10914")
    Range("J2:J10914").Select
    Range("J8").Select
    Selection.End(xlDown).Select
    Columns("J:J").EntireColumn.AutoFit
    Range("J10913").Select
    Selection.End(xlUp).Select
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=IF(C[-4]=""D"",222,223)"
    Selection.AutoFill Destination:=Range("K2:K10914")
    Range("K2:K10914").Select
    Columns("K:K").EntireColumn.AutoFit
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-11],'Paste Data'!C[-11]:C[2],14,0)"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L10914")
    Range("L2:L10914").Select
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M10914")
    Range("M2:M10914").Select
    Range("M2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("O2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-14],'Paste Data'!C[-14]:C[-1],10,0)"
    Columns("N:N").Select
    Selection.Delete Shift:=xlToLeft
    Range("N2").Select
    Selection.AutoFill Destination:=Range("N2:N10914")
    Range("N2:N10914").Select
    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight
    Selection.ColumnWidth = 3.29
    Selection.Interior.ColorIndex = xlNone
    Range("P2").Select
    Selection.AutoFill Destination:=Range("P2:P10914")
    Range("P2:P10914").Select
    Range("P2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("Q2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-16],'Paste Data'!C1:C14,3,0)"
    Selection.AutoFill Destination:=Range("Q2:Q10914")
    Range("Q2:Q10914").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C7").Select
    Application.CutCopyMode = False
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Range("C1").Select
    Selection.Copy
    Range("D1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=-6
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Row"
    Columns("H:J").Select
    Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
    Columns("K:K").Select
    Selection.NumberFormat = "0"
    Columns("L:L").Select
    Selection.NumberFormat = "d-mmm-yy"
    Range("F1").Select
    Selection.Copy
    Range("G1").Select
    ActiveSheet.Paste
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "month"
    Cells.Select
    Selection.Interior.ColorIndex = xlNone
    Cells.Select
    Selection.ColumnWidth = 18
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Columns("K:K").ColumnWidth = 2.29
End Sub

venkat1926 · Answer

if you can tell us what you want to do clearly newsgroup may be able to help you.

recording the macro by making the steps is fine for learning vba. but once macro is recorded you must edit it.  in vba you need not select every time  for e.g. 
instead of 
Range("J2").Select
ActiveCell.FormulaR1C1 = "=MONTH(TODAY())" 

you can have  just one line 
range("I2").formula="=month(today())"

better first prepare some sort of a flow diagram what you want to do on paper and then follow that in recording the macro and edit the macro to remove unnecessary line and generalise it. 

for e.g 
there are following code statements 

Columns("J:J").Select
Selection.Insert Shift:=xlToRight 

later 

Columns("J:J").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

why insert and again delete a column

To the last row

1 response

Similar discussions

Subscribe To Our Newsletter!