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

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
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
ABCDEF
1OriginalOrderQuantity15,910ClientNameSmithJones
2ExecutedOrderQuantity0OrderQty4,3122,661
3%ofTotalOrder27.1025%16.7253%
4SharestobeAllocated00
5
6QuantityPrice
730025.898150
830025.8958150
950025.913583
1080025.91216133
1110025.922716
1220025.935433
1350025.9513583
1440025.9710866
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

alan.m

Board Regular
Joined
Oct 22, 2010
Messages
120
try
Code:
    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

Board Regular
Joined
Sep 25, 2002
Messages
127
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

Board Regular
Joined
Oct 22, 2010
Messages
120
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
Code:
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,476
Messages
5,596,375
Members
414,063
Latest member
N_Bates

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