Need help with row/column VBA loop to round down calculated cell amounts

[psulion01]

On my sheet I have a loop running to cycle through all necessary rows in a column, and then to loop back to the first row of the next column. The loop works fine when I run the code below with just the .Select line (i.e. I can see the cells being selected and looped correctly). Where I'm having trouble is getting a ROUNDDOWN calculation result populated in each cell of the loop. The calculation needs to be done using cells in column C of the active row and row 3 of the active column.

Allocation Sheet v2.xls
	A	B	C	D	E	F
1	OriginalOrderQuantity	15,910		ClientName	Smith	Jones
2	ExecutedOrderQuantity	0		OrderQty	4,312	2,661
3				%ofTotalOrder	27.1025%	16.7253%
4				SharestobeAllocated	0	0
5
6			Quantity	Price
7			300	25.89	81	50
8			300	25.895	81	50
9			500	25.9	135	83
10			800	25.91	216	133
11			100	25.92	27	16
12			200	25.93	54	33
13			500	25.95	135	83
14			400	25.97	108	66
Sheet1

Here's the snippet of VBA for the loop... (sorry i don't know how to post the code here)
For c = 5 To LastCol
For r = 7 To LastRow
Cells(r, c).Select
Selection.Value = Application.WorksheetFunction.RoundDown("C" & r * c & "3", 0)
Next r
Next c


So in essence I want E7 to have the result of ROUNDDOWN($C7*E$3,0) populated, with the same calc happening in E8, E9, etc and then eventually looping back to F7, F8, F9 etc. I just don't know how to tell the VBA RoundDown function to calculate on the looping row and column numbers. HELP!

Thanks
Mike

 

[alan.m]

try

    Selection.Value = Application.WorksheetFunction.rounddown(Cells(r, 3) * Cells(3, c), 2)

PS - I couldn't see the formula you used, so I have just rounded to 2 decimal places - the last number in the rounddown function.
HTH

 

[psulion01]

Ahh ok... i was on the right track in one of my attempts but must have had something wrong somewhere.

Question -- Would it be better to produce the formula in just one cell (E7) and then have the vba code fill the formula across to my last column and then fill all down to the last row? Seems more efficient than looping thru every cell. If so could you assist in getting the fill to LastRow and LastCol?

Thanks!

 

[alan.m]

I simply don't know if copy/paste will be more efficcient than looping through each cell. A couple of things will speed the code up though:
1 - don't select the cell if you don't need to
2 - reduce the number of lines if you can
so, here is a more efficient version

Sub subrounddown()
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = Cells(7, Columns.Count).End(xlToLeft).Column
    
    For c = 5 To LastCol
        For r = 7 To LastRow
            Cells(r, c).Value = Application.WorksheetFunction.rounddown(Cells(r, 3) * Cells(3, c), 2)
        Next r
    Next c
End Sub

Unless you are going to have a very large number of rows & columns, you won't notice any efficieny gains. You could then use Application.screenupdating = false to make things a tad faster. My preference would be for code that is easy to read, follow and debug, especially after having left it alone for a few months and then you return to it to make a change - the easier to read the better, I reckon.
eg

Sub subrounddown()
    Application.ScreenUpdating = False
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = Cells(7, Columns.Count).End(xlToLeft).Column
    
    For c = 5 To LastCol
        For r = 7 To LastRow
            Cells(r, c).Value = Application.WorksheetFunction.rounddown(Cells(r, 3) * Cells(3, c), 2)
        Next r
    Next c
    Application.ScreenUpdating = True
End Sub

HTH

 

[psulion01]

Very helpful... thanks!