?
Solved

Bulk Add - How?

Posted on 2012-08-29
8
Medium Priority
?
593 Views
Last Modified: 2012-08-30
Bulk Adding
Hello All,
Have a issue here. When say 3000 is added to cell to F2, Find the number of row that exhibit properties 2 and 3, and then divide the 3000 by that count. Then the adjusted number is added to each row
 The adjusted number is then added to all the lines that exhibits this set of properties:
1.      The line must have values for either B,C,D or E columns – if that line doesn’t have a number of all of these 4 columns, DONT’T add it to addon1 column of that row
2.      The number gets added to the line that has the same  date in column G as the one entered in the top of the page
Thank you
bulkStuff.xlsx
0
Comment
Question by:Rayne
  • 5
  • 3
8 Comments
 

Author Comment

by:Rayne
ID: 38347966
Thank you
0
 

Author Comment

by:Rayne
ID: 38347980
As you can see I am dealing with 20000 rows – so what could be the more efficient and quick VBA to get this to work?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38348109
Hi, Rayne.

I'm not sure I understand your requirements correctly, but here goes...
(1) The actions will be triggered by the user changing the value in H2. (Or do you want to manually run the macro? Is H2 updated manually or by a formula/code?)
(2) Count the number of rows whose date matches the date in H4 AND which have at least one value in columns B to E.
(3) Divide the value in H2 by the no. of rows calculated in (2).
(4) In each of the rows identified in (2) increase the value in column Q or R (depending on the value in H3) by the value calculated in (3). (Is "increase" correct or should it be "replace"?)

Thanks,
Brian.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:Rayne
ID: 38348220
Hello Brian,
Thank you for your reply. I have answered it below…

(1) The actions will be triggered by the user changing the value in H2. (Or manually run the macro? Is H2 updated manually or by a formula/code?) – This would a button “AddMe” next to H2 . So users after entering the number in H2, press that button and it kicks off the macro to do the desired..

(2) Count the number of rows whose date matches the date in H4 AND which have at least one value in columns B to E. (Yes)

(3) Divide the value in H2 by the no. of rows calculated in (2). (Yes)

(4) In each of the rows identified in (2) increase the value in column Q or R (depending on the value in H3) by the value calculated in (3). (Yes)

 (Is "increase" correct or should it be "replace"?) – Replace would be ideal..
0
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 38348335
Rayne,

Please see attached. Small warning, I've used Q5 and R5 as temporary work cells - let me know if that's a problem.

The code is
Option Explicit

Sub Bulk_Update()
Dim xLast_Row As Long
Dim xCol      As Long
Dim xRange    As Range

xLast_Row = [A1].SpecialCells(xlLastCell).Row
If xLast_Row < 7 Then
    MsgBox ("No Data rows. Run terminated.")
    Exit Sub
End If

If [H2] = "" Then
    MsgBox ("""HEapTotal"" is invalid. Run terminated.")
    Exit Sub
End If

If [H3] <> "add on1" And [H3] <> "add on2" Then
    MsgBox ("""EffectedColumn"" is invalid. Run terminated.")
    Exit Sub
End If

If [H4] = "" Then
    MsgBox ("""Date"" is invalid. Run terminated.")
    Exit Sub
End If

' Remeber the active cell...
Set xRange = ActiveCell

' Which column are we updating?
If [H3] = "add on1" Then xCol = 17 Else xCol = 18

' Flag rows for update...
Cells(7, xCol).Formula = "=IF(AND(F7=$H$4,OR(B7<>"""",C7<>"""",D7<>"""",E7<>"""")),1,"""")"
Cells(7, xCol).Copy Destination:=Range(Cells(7, xCol), Cells(xLast_Row, xCol))

' Calculate amount to update each row...
Cells(5, xCol).FormulaR1C1 = "=R2C8/SUM(R[2]C:R[" & xLast_Row & "]C)"

' Drop formulas in update column...
Range(Cells(7, xCol), Cells(xLast_Row, xCol)).Copy
Range(Cells(7, xCol), Cells(xLast_Row, xCol)).PasteSpecial Paste:=xlPasteValues

' Replace flag by update amount...
Range(Cells(7, xCol), Cells(xLast_Row, xCol)).Replace What:="1", Replacement:=Cells(5, xCol), LookAt:=xlWhole

' Clear work cell...
Cells(5, xCol).ClearContents

' Finished...
xRange.Activate
MsgBox ("Update complete.")

End Sub

Open in new window

Regards,
Brian.bulkStuff-V2.xlsm
0
 

Author Comment

by:Rayne
ID: 38348684
Awesome Brian,

This works GOLD, and I mean gold. super fast . I will let you know if any question but thanks for your effort to help me out :)
0
 

Author Comment

by:Rayne
ID: 38349024
Brian,

I don't know how to really thank you, you saved me so big time, all this time what I did was slow but users didn't really complain of the slowness because it worked. But I knew it could be more efficient. You did the job. This is speeded up to terrific proportions.  I wish I could give you 1000 points :)
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38349042
Thanks, Rayne. Glad to help!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
I came across an unsolved Outlook issue and here is my solution.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question