ngann

New Member
Joined
Jan 18, 2018
Messages
18
I am trying to use a ctrl + shift + down arrow dynamic range but am unsure how to embed it in my current macro that has something along the lines of

Selection.autofill Destination: =Range(“J2:J330”)

please help
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to the board
Something like
Code:
   Dim UsdRws As Long
   
   UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Range("J2").AutoFill Range("J2:J" & UsdRws)
 

ngann

New Member
Joined
Jan 18, 2018
Messages
18
Hello and thank you!

Forgive my ignorance, but how would I write that into this formula?

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,21,FALSE)"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J5000")
Range("J2:J330").Select
Range("J2").Select
Selection.Copy
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
What cell are you putting that formula in?
 

ngann

New Member
Joined
Jan 18, 2018
Messages
18

ADVERTISEMENT

J2
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub chk()
   Dim UsdRws As Long
   
   UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Range("J2:J" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",21,FALSE)"
End Sub
 

ngann

New Member
Joined
Jan 18, 2018
Messages
18

ADVERTISEMENT

Sorry for all the questions. I am confident in my excel skills until it comes to macros and then I am just lost. Maybe it would help if I gave you the whole code haha.

The parts I am wanting to change are the ranges it says autofill on. These ranges vary from time to time and I would like for them to auto adjust as opposed to me changing the row count. I know it is decently lengthy code but I think only the autofill ranges would need to change.

Thanks so much for your help



Code:
Sub PricingMacro()
'
' PricingMacro Macro
'
' Keyboard Shortcut: Ctrl+h
'
    Sheets("PRISM History").Select
    Columns("C:C").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Sheets("S & E").Select
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,4,FALSE)"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C330")
    Range("C2:C330").Select
    Range("C2").Select
    Selection.Copy
    Range("J2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Sheets("PRISM History").Select
    Range("U1").Select
    Application.CutCopyMode = False
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ColorIndex = 36
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.FormulaR1C1 = "New Unit Price"
    Range("U1").Select
    Columns("U:U").ColumnWidth = 12.86
    ActiveWindow.ScrollColumn = 2
    Range("U2").Select
    ActiveCell.FormulaR1C1 = "=RC15/RC12"
    Range("U2").Select
    Selection.AutoFill Destination:=Range("U2:U789")
    Range("U2:U789").Select
    Sheets("S & E").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,21,FALSE)"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J5000")
    Range("J2:J330").Select
    Range("J2").Select
    Selection.Copy
    Range("M2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,12,FALSE)"
    Range("M3").Select
    ActiveWindow.SmallScroll Down:=-3
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M330")
    Range("M2:M330").Select
    Range("M2").Select
    Selection.Copy
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    Range("T2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,3,FALSE)"
    Range("T2").Select
    Selection.Copy
    Range("U2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,2,FALSE)"
    Range("U2").Select
    Selection.Copy
    Range("V2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R1048576,11,FALSE)"
    Range("V2").Select
    Selection.Copy
    Range("X2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,'Lead Time Report'!R[-1]:R[1048574],12,FALSE)"
    Range("T2:X2").Select
    Selection.AutoFill Destination:=Range("T2:X330")
    Range("T2:X330").Select
    Range("X2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,'Lead Time Report'!R1:R1048576,12,FALSE)"
    Range("X2").Select
    Selection.AutoFill Destination:=Range("X2:X330")
    Range("X2:X330").Select
    Range("X6").Select
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[3]>0,""HS"","""")"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G330")
    Range("G2:G330").Select
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC4="""","""",(IF((VLOOKUP(RC1,'PRISM History'!RC[-4]:R1524C19,18,FALSE))=RC[-1],""Match"", ""Check PO"")))"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E330")
    Range("E2:E330").Select
    Range("E2").Select
    Sheets("Summary").Select
    Range("B1").Select
    ActiveSheet.PivotTables("Pricing Summary").PivotCache.Refresh
    Range("B2").Select
    With ActiveSheet.PivotTables("Pricing Summary").PivotFields("Basis Code")
        .PivotItems("HS").Visible = True
    End With
    Sheets("Summary").Select
End Sub
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Firstly, when posting code please use code tags (the # icon in the reply window)
Secondly try
Code:
Sub PricingMacro()
' Keyboard Shortcut: Ctrl+h
    
   Dim UsdRws As Long
   
   Sheets("PRISM History").Select
   UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Columns("C:C").Select
   Selection.Cut
   Columns("A:A").Select
   Selection.Insert shift:=xlToRight
   Range("U1").Select
   Application.CutCopyMode = False
   With Selection.Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .COLORINDEX = 36
      .TintAndShade = 0
      .PatternTintAndShade = 0
   End With
   With Selection
      .HorizontalAlignment = xlLeft
      .VerticalAlignment = xlBottom
      .WrapText = False
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlContext
      .MergeCells = False
   End With
   ActiveCell.FormulaR1C1 = "New Unit Price"
   Range("U1").Select
   Columns("U:U").ColumnWidth = 12.86
   Range("U2:U" & UsdRws).FormulaR1C1 = "=RC15/RC12"

   Sheets("S & E").Select
   UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Range("C2:C" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",4,FALSE)"
   Range("J2:J" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",21,FALSE)"
   Range("M2:M" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",12,FALSE)"
   Range("T2:T" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",3,FALSE)"
   Range("U2:U" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",2,FALSE)"
   Range("V2:V" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws & ",11,FALSE)"
   Range("X2:X" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'Lead Time Report'!R1:R" & UsdRws & ",12,FALSE)"
   Range("G2:G" & UsdRws).FormulaR1C1 = "=IF(RC[3]>0,""HS"","""")"
   Range("E2:E" & UsdRws).FormulaR1C1 = "=IF(RC4="""","""",(IF((VLOOKUP(RC1,'PRISM History'!RC[-4]:R" & UsdRws & "C19,18,FALSE))=RC[-1],""Match"", ""Check PO"")))"
   
   Sheets("Summary").Select
   Range("B1").Select
   ActiveSheet.PivotTables("Pricing Summary").PivotCache.Refresh
   Range("B2").Select
   With ActiveSheet.PivotTables("Pricing Summary").PivotFields("Basis Code")
   .PivotItems("HS").Visible = True
   End With
   Sheets("Summary").Select
End Sub
 

ngann

New Member
Joined
Jan 18, 2018
Messages
18
That works great except in the vlookup formulas it is limiting the table size too narrow and not pulling the entire table. Is there a fix to this also can you explain what the R1:R is doing?

Thanks so much for your help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
My mistake try
Code:
Sub PricingMacro()
' Keyboard Shortcut: Ctrl+h
    
   Dim UsdRws As Long
   Dim UsdRws2 As Long
   
   Sheets("PRISM History").Select
   UsdRws2 = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Columns("C:C").Select
   Selection.Cut
   Columns("A:A").Select
   Selection.Insert shift:=xlToRight
   Range("U1").Select
   Application.CutCopyMode = False
   With Selection.Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .COLORINDEX = 36
      .TintAndShade = 0
      .PatternTintAndShade = 0
   End With
   With Selection
      .HorizontalAlignment = xlLeft
      .VerticalAlignment = xlBottom
      .WrapText = False
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlContext
      .MergeCells = False
   End With
   ActiveCell.FormulaR1C1 = "New Unit Price"
   Range("U1").Select
   Columns("U:U").ColumnWidth = 12.86
   Range("U2:U" & UsdRws).FormulaR1C1 = "=RC15/RC12"

   Sheets("S & E").Select
   UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Range("C2:C" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws2 & ",4,FALSE)"
   Range("J2:J" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws2 & ",21,FALSE)"
   Range("M2:M" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws2 & ",12,FALSE)"
   Range("T2:T" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws2 & ",3,FALSE)"
   Range("U2:U" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws2 & ",2,FALSE)"
   Range("V2:V" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'PRISM History'!R1:R" & UsdRws2 & ",11,FALSE)"
   Range("X2:X" & UsdRws).FormulaR1C1 = "=VLOOKUP(RC1,'Lead Time Report'!R1:R1048576,12,FALSE)"
   Range("G2:G" & UsdRws).FormulaR1C1 = "=IF(RC[3]>0,""HS"","""")"
   Range("E2:E" & UsdRws).FormulaR1C1 = "=IF(RC4="""","""",(IF((VLOOKUP(RC1,'PRISM History'!RC[-4]:R" & UsdRws2 & "C19,18,FALSE))=RC[-1],""Match"", ""Check PO"")))"
   
   Sheets("Summary").Select
   Range("B1").Select
   ActiveSheet.PivotTables("Pricing Summary").PivotCache.Refresh
   Range("B2").Select
   With ActiveSheet.PivotTables("Pricing Summary").PivotFields("Basis Code")
   .PivotItems("HS").Visible = True
   End With
   Sheets("Summary").Select
End Sub
R1:R" & UsdRws2 Is simply limiting the Vlookup to the rows of data rather than looking at the entire sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top