+ Reply to Thread
Results 1 to 2 of 2

Help with Macro: I need to fill columns D-N only up till the row number in column A

  1. #1
    Registered User
    Join Date
    02-17-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2013
    Posts
    3

    Help with Macro: I need to fill columns D-N only up till the row number in column A

    Hi,

    I have a control chart macro which is being used for patient doses of a medication. Right now my macro automatically fills columns D-N down to row 100. However, I would like it to stop at the last row of data entered in column A. Any assistance will be greatly appreciated. I have attached the code related to the formulae a

    J

    Sub Control_Chart_1()
    '
    ' Control_Chart_1 Macro
    '

    '
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Target"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "UCL"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "LCL"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Rule 1"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Rule 2"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Rule 1 AND 2"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("D2:F2").Select
    Selection.AutoFill Destination:=Range("D2: F100"), Type:=xlFillDefault
    Range("D2:F100").Select
    ActiveWindow.SmallScroll Down:=-141
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Category"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]-RC[-3]"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G100"), Type:=xlFillDefault
    Range("G2:G100").Select
    Range("H3").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(OR(RC[-1]>1, RC[-1]<-1, AND(R[-1]C[-1]<0,RC[-1]<0), AND(R[-1]C[-1]>0,RC[-1]>0)),1,0)"
    Range("H3").Select
    Selection.AutoFill Destination:=Range("H3:H100"), Type:=xlFillDefault
    Range("H3:H100").Select
    Range("I5").Select
    ActiveCell.FormulaR1C1 = "=IF(STDEV(R[-3]C[-7]:RC[-7])>1,1,0)"
    Range("I5").Select
    Selection.AutoFill Destination:=Range("I5:I100"), Type:=xlFillDefault
    Range("I5:I100").Select
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]*2+RC[-1]"
    Range("J3").Select
    Selection.AutoFill Destination:=Range("J3:J100"), Type:=xlFillDefault
    Range("J3:J100").Select
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Rule 3"
    Range("K9").Select
    ActiveCell.FormulaR1C1 = _
    "=5*IF(AVERAGE(R[-3]C[-9]:RC[-9])>2*AVERAGE(R[-7]C[-9]:R[-4]C[-9]),1,0)"
    Range("K9").Select
    Selection.AutoFill Destination:=Range("K9:K100"), Type:=xlFillDefault
    Range("K9:K100").Select
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Rules 1, 2, 3"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L100"), Type:=xlFillDefault
    Range("L2:L100").Select
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Technique Error Means"
    Range("M5").Select
    ActiveCell.FormulaR1C1 = "=IF(AVERAGE(R[-3]C[-10]:RC[-10])<-0.25,1,0)"
    Range("M5").Select
    Selection.AutoFill Destination:=Range("M5:M100"), Type:=xlFillDefault
    Range("M5:M100").Select
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Technique Error Rules"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=IF(OR(RC[-11]<-1,RC[-1]=1),1,0)"
    Range("N2").Select
    Selection.AutoFill Destination:=Range("N2:N100"), Type:=xlFillDefault
    Range("N2:N100").Select

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Help with Macro: I need to fill columns D-N only up till the row number in column A

    Yuck!

    First - please use CODE tags around any code you post.
    Second - you can get rid of more than half of those line - there's no need to do all that selecting.
    Third - you don't need to autofill, you can write the formulae directly to the specified range.
    Finally - It's easy to determine the last used row in column A, and use that in the range you write the formulae to.

    So, with all that, your code can turn into:
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] I need a macro to copy down till adjacent column ends in a blank cell
    By Paul Hayward in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-09-2014, 02:27 PM
  2. Replies: 10
    Last Post: 10-30-2013, 07:29 PM
  3. Copy entire selected column to next columns till last column
    By siroos12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2013, 05:20 AM
  4. [SOLVED] Compare columns, replace matching number with reference number and fill down random amount
    By datadigger in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-04-2012, 10:53 PM
  5. Delete every alternate columns till the last data in the row from column G
    By andywsw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-11-2012, 11:59 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1