Printable View
Quote:
Originally Posted by jejmiller
Code: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
Hey P45, I'm confused as to where to place your code.
It replaces all your full code in msg#20.
Awesome, that worked.Quote:
Originally Posted by p45cal
Now I have another sheet. I will try to do this on my own here.
The code is:
Code: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?
Code: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
more likely (but untested):Quote:
Originally Posted by jejmiller
Code: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.Quote:
Originally Posted by p45cal
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:
With this:Code:Selection.AutoFill Destination:=Range("F2:F33")
But no luck.Code:Range(Selection, Selection.End(xlDown)).Selec
Here is the full script. There's three places where auto-fill needs to be replaced.
Code: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.
Oh, look at me, I figured it out! For those curious,
I replaced:
Code:Selection.AutoFill Destination:=Range("F2:F33")
Range("F2:F33").Select
With this:
Code:LastRow = Range("A1").End(xlDown).Row ' Ctrl+Down from cell A1
Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
Range("F1").AutoFilter
For all of Macro4 try:Code: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