Fluid VBA macro?

[samilynn]

I'm working on a macro with a lot of steps for a file that needs to be recreated on a regular basis.

Is it possible to have the macro modify the range in the formula each time, based on how many rows are populated in the file? (the spreadsheet doesn't always have 738 rows of data).

The formula gives the total quantity per product, without having to use Subtotal.

Thank you!
Samantha

Excel 2010 64 bit
	A	B	C	D	E	F
1	PROD	WHSE	QTY	COST	TOTAL QTY
2	AB5314	Whse: 1	5,000	$1.95	15,000	IF(COUNTIF($A$2:A2,A2)=1,SUMIF($A$2:$A$738,A2,$C$2:$C$738),"")
3	AB5314	Whse: 2	10,000	$1.97
4	SQ5577	Whse: 1	29	$317.43	41
5	SQ5577	Whse: 2	12	$317.43
QTY

Cell Formulas
Range	Formula
E2	=IF(COUNTIF($A$2:A2,A2)=1,SUMIF($A$2:$A$738,A2,$C$2:$C$738),"")
E3	=IF(COUNTIF($A$2:A3,A3)=1,SUMIF($A$2:$A$738,A3,$C$2:$C$738),"")
E4	=IF(COUNTIF($A$2:A4,A4)=1,SUMIF($A$2:$A$738,A4,$C$2:$C$738),"")
E5	=IF(COUNTIF($A$2:A5,A5)=1,SUMIF($A$2:$A$738,A5,$C$2:$C$738),"")

 

[Rick Rothstein]

I have been completely overlooking the .Value=.Value code line... all this time I thought the goal was to load up Column E with formulas. Since the goal is to place values in the cells, I think this code might be (noticeably?) faster...

[table="width: 500"]
[tr]
	[td]Sub samilynn2()
  Dim R As Long, I As Variant, Data As Variant, QtyRow As Variant
  Data = Range("A2", Cells(Rows.Count, "A").End(xlUp).Offset(, 2))
  With CreateObject("Scripting.Dictionary")
    For R = 1 To UBound(Data)
      If IsEmpty(.Item(Data(R, 1))) Then .Item(Data(R, 1)) = "/" & R + 1
      .Item(Data(R, 1)) = Val(.Item(Data(R, 1))) + Data(R, 3) & Mid(.Item(Data(R, 1)), InStrRev(.Item(Data(R, 1)), "/"))
    Next
    For Each I In .Items
      QtyRow = Split(I, "/")
      Cells(QtyRow(1), "E").Value = QtyRow(0)
    Next
  End With
End Sub[/td]
[/tr]
[/table]

@Fluff,

If you get the chance to test it, I would be curious how the above code fared speedwise in comparison to the other codes.

 

[Fluff]

That is a lot faster ~ quarter of a second.

 

[Rick Rothstein]

That is a lot faster ~ quarter of a second.

Yes! I knew it would be faster!!!!

Seriously, though, I thought the combination of an internal VB array coupled with the Dictionary object (that is, processing everything in memory) would have produced a better result than that.

 

[Fluff]

I think you may have misunderstood, reading it again it's slightly ambiguous.
It took ~.25 seconds, rather than being .25 seconds quicker.

 

[Rick Rothstein]

I think you may have misunderstood, reading it again it's slightly ambiguous.
It took ~.25 seconds, rather than being .25 seconds quicker.

And here I thought you were being facetious. Okay, so then I was right, executing almost entirely in memory was a lot faster. Hopefully the OP comes back to this thread to see this.

 

[Fluff]

And here I thought you were being facetious.

Wot, Me, Never