+ Reply to Thread
Results 1 to 13 of 13

Macro problem - auto fill to last active cell

Hybrid View

  1. 10-18-2012, 12:24 PM #1
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Question Macro problem - auto fill to last active cell

    Hello I am relatively new to your forum and was wondering if anyone out there could help me

    I have included my macro below and it is part of a much bigger macro, however at the moment due to the part of code:

    Selection.AutoFill Destination:=Range("A2:B1897")
    &
    Selection.AutoFill Destination:=Range("J2:J1897")

    It seems to not work correctly and if the input report I have is less than the 1897 lines it returns a #n/a.

    What I would like to do within this same macro is only do the steps to the last active cell in the sheet e.g. it would mean that any input report of any size would work and no errors returned.

    Can anyone help me out on this one

    Thanks

    CL
    Last edited by CraigieL; 10-18-2012 at 04:41 PM.
    Reply With QuoteRegister To Reply
  2. 10-18-2012, 12:24 PM #2
    JieJenn
    JieJenn is offline
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Macro help needed

    Can you post a sample file?
    Learn Data Analysis or Excel youtube.com/jiejenn
    Reply With QuoteRegister To Reply
  3. 10-18-2012, 12:28 PM #3
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro help needed

    Hi

    MARCO P1

    Sub Stage_1()
'
' Stage_1 Macro
'

'
    Sheets("Raw Data").Select
    Rows("1:1").Select
    Application.CutCopyMode = False
    With Selection.Font
        .Name = "Arial"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Department Indicator"
    Range("A4").Select
    Columns("A:A").EntireColumn.AutoFit
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Department"
    Range("B2").Select
    Columns("B:B").EntireColumn.AutoFit
    Range("A1:B1").Select
    Range("B1").Activate
    Selection.Font.Bold = True
    Range("A2").Select
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[2],(2))"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Dept,2,0)"
    Range("A2:B2").Select
    Selection.AutoFill Destination:=Range("A2:B1897")
    Range("A2:B1897").Select
    Columns("A:B").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    Range("F:F,H:H").Select
    Range("H1").Activate
    Selection.NumberFormat = "$#,##0.00"
    Range("I3").Select
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Removed"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Remove"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Removed,2,0)"
    Selection.AutoFill Destination:=Range("J2:J1897")
    Range("J2:J1897").Select
    Columns("J:J").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    Columns("M:N").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Todays Date"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "No.of days since Input"
    Range("M1:N1").Select
    Selection.Copy
    Range("P1").Select
    ActiveSheet.Paste
    Columns("P:P").EntireColumn.AutoFit
    Columns("Q:Q").EntireColumn.AutoFit
    Range("Q1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "No.of days since Invoice"
    Range("Q2").Select
    Columns("Q:Q").EntireColumn.AutoFit
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M1897")
    Range("M2:M1897").Select
    Range("M2").Select
    Selection.Copy
    Range("P2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("P2:P1897")
    Range("P2:P1897").Select
    Columns("M:M").Select
    Selection.Copy
    Last edited by arlu1201; 10-18-2012 at 01:23 PM.
    Reply With QuoteRegister To Reply
  4. 10-18-2012, 12:29 PM #4
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro help needed

    Sorry Could only get on this way at moment
    Reply With QuoteRegister To Reply
  5. 10-18-2012, 12:28 PM #5
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro help needed

    macro p2

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("P:P").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("N2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
    Range("N2").Select
    Selection.NumberFormat = "General"
    Selection.AutoFill Destination:=Range("N2:N1897")
    Range("N2:N1897").Select
    Range("N2").Select
    Selection.Copy
    Range("Q2").Select
    ActiveSheet.Paste
    Range("Q2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
    Range("Q2").Select
    Selection.AutoFill Destination:=Range("Q2:Q1897")
    Range("Q2:Q1897").Select
    Range("N:N,Q:Q").Select
    Range("Q1").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=29"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=11", Formula2:="=29"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=1", Formula2:="=10"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("S10").Select
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Cells.Select
    ActiveWorkbook.Worksheets("Raw Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Raw Data").Sort.SortFields.Add Key:=Range( _
        "J2:J1897"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Raw Data").Sort
        .SetRange Range("A1:AA1897")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 78
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 113
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 190
    ActiveWindow.ScrollRow = 202
    ActiveWindow.ScrollRow = 244
    ActiveWindow.ScrollRow = 290
    ActiveWindow.ScrollRow = 352
    ActiveWindow.ScrollRow = 421
    ActiveWindow.ScrollRow = 506
    ActiveWindow.ScrollRow = 603
    ActiveWindow.ScrollRow = 711
    ActiveWindow.ScrollRow = 873
    ActiveWindow.ScrollRow = 969
    ActiveWindow.ScrollRow = 1066
    ActiveWindow.ScrollRow = 1154
    ActiveWindow.ScrollRow = 1282
    ActiveWindow.ScrollRow = 1409
    ActiveWindow.ScrollRow = 1486
    ActiveWindow.ScrollRow = 1575
    ActiveWindow.ScrollRow = 1617
    ActiveWindow.ScrollRow = 1640
    ActiveWindow.ScrollRow = 1644
    ActiveWindow.ScrollRow = 1648
    ActiveWindow.ScrollRow = 1652
    ActiveWindow.ScrollRow = 1656
    ActiveWindow.ScrollRow = 1690
    ActiveWindow.ScrollRow = 1710
    ActiveWindow.ScrollRow = 1756
    ActiveWindow.ScrollRow = 1787
    ActiveWindow.ScrollRow = 1798
    ActiveWindow.ScrollRow = 1818
    ActiveWindow.ScrollRow = 1822
    ActiveWindow.ScrollRow = 1825
    ActiveWindow.ScrollRow = 1829
    ActiveWindow.ScrollRow = 1833
    ActiveWindow.ScrollRow = 1837
    ActiveWindow.ScrollRow = 1852
    ActiveWindow.ScrollRow = 1860
    ActiveWindow.ScrollRow = 1864
    ActiveWindow.ScrollRow = 1860
    ActiveWindow.ScrollRow = 1856
    ActiveWindow.ScrollRow = 1849
    ActiveWindow.ScrollRow = 1837
    ActiveWindow.ScrollRow = 1829
    ActiveWindow.ScrollRow = 1818
    ActiveWindow.ScrollRow = 1791
    ActiveWindow.ScrollRow = 1744
    ActiveWindow.ScrollRow = 1687
    ActiveWindow.ScrollRow = 1636
    ActiveWindow.ScrollRow = 1575
    ActiveWindow.ScrollRow = 1517
    ActiveWindow.ScrollRow = 1451
    ActiveWindow.ScrollRow = 1405
    ActiveWindow.ScrollRow = 1336
    ActiveWindow.ScrollRow = 1243
    ActiveWindow.ScrollRow = 1177
    ActiveWindow.ScrollRow = 1093
    ActiveWindow.ScrollRow = 1027
    ActiveWindow.ScrollRow = 961
    ActiveWindow.ScrollRow = 900
    ActiveWindow.ScrollRow = 826
    ActiveWindow.ScrollRow = 765
    ActiveWindow.ScrollRow = 699
    ActiveWindow.ScrollRow = 634
    ActiveWindow.ScrollRow = 568
    ActiveWindow.ScrollRow = 468
    ActiveWindow.ScrollRow = 406
    ActiveWindow.ScrollRow = 344
    ActiveWindow.ScrollRow = 294
    ActiveWindow.ScrollRow = 252
    ActiveWindow.ScrollRow = 202
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 128
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A1").Select
    Sheets("Raw Data").Select
    Sheets("Raw Data").Copy Before:=Sheets(2)
    Sheets("Raw Data (2)").Select
    Sheets("Raw Data (2)").Move Before:=Sheets(4)
    Sheets("Raw Data").Select
    Sheets("Raw Data").Name = "Invoices"
    Sheets("Raw Data (2)").Select
    Sheets("Raw Data (2)").Name = "Removed Invoices"
     
End Sub
    Last edited by arlu1201; 10-18-2012 at 01:23 PM.
    Reply With QuoteRegister To Reply
  6. 10-18-2012, 01:24 PM #6
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Macro help needed

    Welcome to the forum.

    I have added code tags to your posts. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

    Also - you do not need all these rows - 
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollRow = 5
    etc. You can delete them. They only make your code long and bulky to read and troubleshoot.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  7. 10-18-2012, 04:52 PM #7
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro problem - auto fill to last active cell

    Thank you - I will remove however how would the two problem codes allow for me to auto fill to last active cell- in the codes at the moment only allow a fill to 1897 however I need to allow a fill in both columns on any size of sheet so to the last active cell

    I hope that made sence
    Reply With QuoteRegister To Reply
  8. 10-19-2012, 09:32 AM #8
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Macro problem - auto fill to last active cell

    Your macro has a lot of extra statements which are not necessary. Can you list out exactly what you want the macro to do (step by step) and we can help you make your macro efficient as well as autofill till the last active cell.
    Reply With QuoteRegister To Reply
  9. 10-19-2012, 10:45 AM #9
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro problem - auto fill to last active cell

    Good Afternoon arlu1201

    The macro is going to be used to carry out many front end functions of a report, the autofill option is to allow for the copy and fill down of a formula and then copy and paste as values.

    It seems to work fine at the moment the only problem I have is that it only seems to fill to line 1897, and I ultimately need to fill on any length of base report, this is why I thought the autofill to last active cell would be best.

    Thanks
    Reply With QuoteRegister To Reply
  10. 10-19-2012, 11:01 AM #10
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Macro problem - auto fill to last active cell

    Ok. The macro may be working fine, i just wanted to help you make it more efficient because there are lots of unwanted rows of code which if deleted can make your macro run much faster.
    Reply With QuoteRegister To Reply
  11. 10-19-2012, 11:09 AM #11
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro problem - auto fill to last active cell

    Hi arlu1201

    Sorry I did not mean to come across in an adversarial way, I am at a novice stage of VBA, I appreciate the advice to remove these additional lines, Just wondering what I need to add to the following to ensure it fills to the last active cell .

    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Dept,2,0)"
    Range("A2:B2").Select
    Selection.AutoFill Destination:=Range("A2:B1897")
    Range("A2:B1897").Select
    Columns("A:B").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Where B1897 would be the autofill to last active cell in Column A - Sorry if I seem to be ungrateful at any point that is not the way I mean to portray myself
    Reply With QuoteRegister To Reply
  12. 10-19-2012, 11:13 AM #12
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Macro problem - auto fill to last active cell

    Hey sure no problem.

    When you are free, we can work together on bettering the code.

    Regarding the above snippet, it can be re-written as follows - 
    Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
    
Range("B2:B" & lrow).FormulaR1C1 = "=VLOOKUP(RC[-1],Dept,2,0)"
Range("B2:B" & lrow).Copy
Range("B2").PasteSpecial (xlPasteValues)
    Reply With QuoteRegister To Reply
  13. 10-22-2012, 04:43 AM #13
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro problem - auto fill to last active cell

    Hi arlu1201

    Thank you for this - I will input this to my code this week and let you know how it goes - thanks you for your help
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1