Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 30 of 30

Thread: CTRL + Shift + Down in a Macro

  1. #21
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,952
    Quote Originally Posted by jejmiller View Post
    Column A
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Ctrl+Up ffrom bottom of sheet.
    'LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1. An alternative to line above if A1 is not empty.
    Range("H2").AutoFill Destination:=Range("H2:H" & LastRow)
    Range("I2:I" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-6],Blacklist!C[-8],1,FALSE)"
    Range("I1").AutoFilter
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  2. #22
    Hey P45, I'm confused as to where to place your code.

  3. #23
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,952
    It replaces all your full code in msg#20.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #24
    Quote Originally Posted by p45cal View Post
    It replaces all your full code in msg#20.
    Awesome, that worked.

  5. #25
    Now I have another sheet. I will try to do this on my own here.

    The code is:

    Range("F2").Select    Selection.AutoFill Destination:=Range("F2:F999")
        Range("F2:F999").Select
        Range("G2").Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],'Hotkey Ignore'!C[-6],1,FALSE)"
        Range("G2").Select
        Selection.AutoFill Destination:=Range("G2:G999")
        Range("G2:G999").Select

    Would my new code be this?

    LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],'Hotkey Ignore'!C[-6],1,FALSE)"
    Range("G2").AutoFill Destination:=Range("G2:G" & LastRow)
    Range("G1").AutoFilter

  6. #26
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,952
    Quote Originally Posted by jejmiller View Post
    Would my new code be this?

    LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1
    Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],'Hotkey Ignore'!C[-6],1,FALSE)"
    Range("G2").AutoFill Destination:=Range("G2:G" & LastRow)
    Range("G1").AutoFilter
    more likely (but untested):
    LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1
    Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
    Range("G2:G" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-5],'Hotkey Ignore'!C[-6],1,FALSE)"
    Range("G1").AutoFilter
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #27
    Quote Originally Posted by p45cal View Post
    more likely (but untested):
    LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1
    Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
    Range("G2:G" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-5],'Hotkey Ignore'!C[-6],1,FALSE)"
    Range("G1").AutoFilter
    This worked. Thank you.

  8. #28
    So, I not sure why I can't ever figure this out... I have a new script I'm trying to auto-fill rows to match the data in column A. Can someone explain what needs to be replaced and why?

    I've tried replacing this:

    Selection.AutoFill Destination:=Range("F2:F33")
    With this:

    Range(Selection, Selection.End(xlDown)).Selec
    But no luck.

    Here is the full script. There's three places where auto-fill needs to be replaced.

    Sub Macro4()'
    ' Macro4 Macro
    '
    
    
    '
        Sheets("AAP Data").Select
        Cells.Select
        Selection.Copy
        Sheets("Template").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A:A,B:B,C:C,E:E,G:G").Select
        Range("G1").Activate
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlToLeft
        Range("A1").Select
        Selection.AutoFilter
        ActiveWorkbook.Worksheets("Template").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Template").AutoFilter.Sort.SortFields.Add2 Key:= _
            Range("A1:A34"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Template").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Rows("2:2").Select
        Selection.Delete Shift:=xlUp
        ActiveWorkbook.Worksheets("Template").AutoFilter.Sort.SortFields.Clear
        Range("C1").Select
        ActiveCell.FormulaR1C1 = "Qty Purchased"
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "Qty Returned"
        Range("E1").Select
        ActiveCell.FormulaR1C1 = "Eligible"
        Range("F1").Select
        ActiveCell.FormulaR1C1 = "Hotkey"
        Range("C2").Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Purchases!C[-2]:C[-1],2,FALSE)"
        Range("D2").Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Returns!C[-3]:C[-2],2,FALSE)"
        Range("C2:D2").Select
        Selection.AutoFill Destination:=Range("C2:D33")
        Range("C2:D33").Select
        Cells.Select
        Selection.Copy
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("G7").Select
        Cells.Replace What:="#n/a", Replacement:="", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Range("E2").Select
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-2]-RC[-3]"
        Range("E2").Select
        Selection.AutoFill Destination:=Range("E2:E33")
        Range("E2:E33").Select
        Range("F2").Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Hotkey!C[-5],1,FALSE)"
        Range("F2").Select
        Selection.AutoFill Destination:=Range("F2:F33")
        Range("F2:F33").Select
        Cells.Select
        Cells.EntireColumn.AutoFit
        Range("F1").Select
        Selection.AutoFilter
        Selection.AutoFilter
    End Sub

    Thank you everyone for your help.

  9. #29
    Oh, look at me, I figured it out! For those curious,

    I replaced:

     Selection.AutoFill Destination:=Range("F2:F33")
        Range("F2:F33").Select

    With this:

    LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1
    Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
    Range("F1").AutoFilter

  10. #30
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,952
    For all of Macro4 try:
    Sub Macro4()    '
    Set rngSource = Sheets("AAP Data").UsedRange
    Set shtTemplate = Sheets("Template")
    With shtTemplate
      .Range("A1").Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
      .Range("A:C,E:E,G:G").Delete
      .Range("A1").AutoFilter
      With .AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=shtTemplate.Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
      End With
      .Rows("2:2").Delete
      .AutoFilter.Sort.SortFields.Clear
      .Range("C1:F1").Value = Array("Qty Purchased", "Qty Returned", "Eligible", "Hotkey")
      Set rngDataBody = .AutoFilter.Range
      Set rngDataBody = Intersect(rngDataBody, rngDataBody.Offset(1))
      With rngDataBody
        .Columns("C").FormulaR1C1 = "=VLOOKUP(RC[-2],Purchases!C[-2]:C[-1],2,FALSE)"
        .Columns("D").FormulaR1C1 = "=VLOOKUP(RC[-3],Returns!C[-3]:C[-2],2,FALSE)"
        .Columns("C:D").Value = .Columns("C:D").Value
        shtTemplate.UsedRange.Replace What:="#n/a", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        .Columns("E").FormulaR1C1 = "=RC[-1]+RC[-2]-RC[-3]"
        .Columns("F").FormulaR1C1 = "=VLOOKUP(RC[-5],Hotkey!C[-5],1,FALSE)"
      End With
      .Cells.EntireColumn.AutoFit
      .Range("F1").AutoFilter
      .Range("F1").AutoFilter
    End With
    End Sub
    Last edited by p45cal; 01-22-2020 at 09:08 AM.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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