Issues With Formula For Dynamic Range

[BigNate]

Hello Everyone,

In a potion of my code below, I am having trouble with accounting for a dynamic range.

The red portion may not always end at row 9756. I want that to be dynamic, which I didn't have an issue with until the RC notation came into play.

My second issue is with the text in blue below. My autofill may not always go to row 20. For whatever reason, I can't seem to identify the last row and have it fill down. I got it to fill up from B4 to B1 though

How should I alter this so that the range for both the red text and blue text below will change in the event the number of rows changes?

Worksheets("Summary".Range("B4").Select
ActiveCell.FormulaR1C1 = _
"=AVERAGEIF(PickingReport!R6C10:R9756C10,Smmary!RC[-1],PickingReport!R6C12:R9756)"
Range("B4").Select
Selection.AutofillDestination:=Range("B4:B20")
Range("B4:B20").Select

Your help is greatly appreciated. Thank you for your time!

 

[BigNate]

Hello Everyone,

I'm still hoping to get help on this. I appreciate your time! Here is the entire subroutine in case that may help:

Sub SummaryStatistics()


Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = "Summary"


RowEnd = Worksheets("PickingReport").Range("A" & Rows.Count).End(xlUp).Row




Sheets("PickingReport").Select
Range("J6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Summary").Select
Range("A4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("A4" & ":A" & RowEnd).RemoveDuplicates Columns:=1, Header:=xlNo

Worksheets("Summary").Range("B3").Value = "Average Time Per Pick"
Worksheets("Summary").Range("C3").Value = "Median Time Per Pick"
Worksheets("Summary").Range("D3").Value = "Max Time Per Pick"
Worksheets("Summary").Range("E3").Value = "Min Time Per Pick"
Worksheets("Summary").Range("F3").Value = "Average Qty Per Pick"

Worksheets("Summary").Range("B3:F3").Columns.AutoFit

Range("B4").Select
Worksheets("Summary").Range("B4").FormulaR1C1 = _
"=AVERAGEIF(PickingReport!R6C10:R9756C10,Summary!RC[-1],PickingReport!R6C12:R9798C12)"
Range("B4").Select
Selection.AutoFill Destination:=Range("B4:B20")
Range("B4:B20").Select


End Sub

 

[Joe4]

You can find that last populated row in column B like this:

myLastRow=Cells(Rows.Count,"B").End(xlUp).Row

You can then incorporate that into your code, i.e.:

Range("B4:B" & myLastRow).Select

 

[BigNate]

You can then incorporate that into your code, i.e.:

Range("B4:B" & myLastRow).Select

[/QUOTE]

Joe4,

Thank you so much for your help. I am still struggling with this even though I have done something like this a few times now. I'm wondering if you could push me one step further perhaps?

I'm getting the following error at the second to bottom line:
"Run-time error '1004'
AutoFill method of range class failed"

Also I notice that after I get through this portion of code, all of column B is highlighted/selected.

Below is my revised code. My two questions at this point at (1) why isn't the autofill not working correctly and (2) how do I create a dynamic range for the red text in my first post? THANKS AGAIN!

Sub SummaryStatistics()




Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = "Summary"


RowEnd = Worksheets("PickingReport").Range("A" & Rows.Count).End(xlUp).Row
myLastRow = Cells(Rows.Count, "B").End(xlDown).Row


Sheets("PickingReport").Select
Range("J6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Summary").Select
Range("A4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("A4" & ":A" & RowEnd).RemoveDuplicates Columns:=1, Header:=xlNo

Worksheets("Summary").Range("B3").Value = "Average Time Per Pick"
Worksheets("Summary").Range("C3").Value = "Median Time Per Pick"
Worksheets("Summary").Range("D3").Value = "Max Time Per Pick"
Worksheets("Summary").Range("E3").Value = "Min Time Per Pick"
Worksheets("Summary").Range("F3").Value = "Average Qty Per Pick"

Worksheets("Summary").Range("B3:F3").Columns.AutoFit

Range("B4").Select
Worksheets("Summary").Range("B4").FormulaR1C1 = _
"=AVERAGEIF(PickingReport!R6C10:R9756C10,Summary!RC[-1],PickingReport!R6C12:R9798C12)"
Range("B4:B" & myLastRow).Select
Selection.AutoFill Destination:=Range("B4:B20")
Range("B4:B20").Select


End Sub

 

[Joe4]

Let's back up a step.
You are trying to Autofill column B, starting on row 4, right?
What column are you using to determine how far down you want to Autofill it? Column A?

Regarding your stuff in red, if you change from using ".FormulaR1C1" notation to just ".Formula" notation, you can use normal range references.

 

[BigNate]

Yes, you are correct. I am trying to autofill column B starting with row 4. I am using column A to figure out how far down in column B to autofill.

Regarding the code in red, I seem to have the syntax completely wrong and am not sure how to correctly do it with the ".Formula" notation. This is what I have so far. Please see below and most improtantly, thanks again for your help.

Sub FillAfterHelp()




Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = "Summary"


RowEnd = Worksheets("PickingReport").Range("A" & Rows.Count).End(xlUp).Row
myLastRow = Cells(Rows.Count, "B").End(xlDown).Row


Sheets("PickingReport").Select
Range("J6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Summary").Select
Range("A4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("A4" & ":A" & RowEnd).RemoveDuplicates Columns:=1, Header:=xlNo

Worksheets("Summary").Range("B3").Value = "Average Time Per Pick"
Worksheets("Summary").Range("C3").Value = "Median Time Per Pick"
Worksheets("Summary").Range("D3").Value = "Max Time Per Pick"
Worksheets("Summary").Range("E3").Value = "Min Time Per Pick"
Worksheets("Summary").Range("F3").Value = "Average Qty Per Pick"

Worksheets("Summary").Range("B3:F3").Columns.AutoFit

Range("B4").Select
Worksheets("Summary").Range("B4").Formula = _
"=AVERAGEIF(PickingReport!("J4:J" & RowEnd),Summary!("A4"),PickingReport!("L4:L" & RowEnd)" "expected: end of statement" error at this line
Range("B4:B" & myLastRow).Select
Selection.AutoFill Destination:=Range("B4:B20")
Range("B4:B20").Select

 

[Joe4]

That ".Formula" gets a bit messy due to the ranges using double-quotes too, so let's try changing it back to ".FormulaR1C1".

This may require some adjusting (as I don't pretend to know the structure of your sheets), but this should give you a good start:

Sub FillAfterHelp()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = "Summary"

RowEnd = Worksheets("PickingReport").Range("A" & Rows.Count).End(xlUp).Row

Sheets("PickingReport").Select
Range("J6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Summary").Select
Range("A4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("A4" & ":A" & RowEnd).RemoveDuplicates Columns:=1, Header:=xlNo

'Find last row in column A here
myLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Worksheets("Summary").Range("B3").Value = "Average Time Per Pick"
Worksheets("Summary").Range("C3").Value = "Median Time Per Pick"
Worksheets("Summary").Range("D3").Value = "Max Time Per Pick"
Worksheets("Summary").Range("E3").Value = "Min Time Per Pick"
Worksheets("Summary").Range("F3").Value = "Average Qty Per Pick"

Worksheets("Summary").Range("B3:F3").Columns.AutoFit

Range("B4").Select
Worksheets("Summary").Range("B4").FormulaR1C1 = _
    "=AVERAGEIF(PickingReport!R6C10:R" & (RowEnd - 6) & "C10,Summary!RC[-1],PickingReport!R6C12:R" & (RowEnd - 6) & "C12)"

Range("B4").AutoFill Destination:=Range("B4:B" & myLastRow)
Range("B4:B" & myLastRow).Select

End Sub

 

[BigNate]

Joe4,

You are the man! This worked.

Thanks,
Nate