Sum 2 Ranges By Condition Macro
Oct 22, 2007
how to properly use the OR operator.
Currently, I have the following code in my program:
Cobbs = WorksheetFunction. SumIf(Sheet5. Range("H9:H500"), "C/F", Sheet5.Range("I9:I500")) _
+ WorksheetFunction.SumIf(Sheet5.Range("H9:H500"), "4/F", Sheet5.Range("I9:I500"))
But it seems to me that the program should work just fine if I use something like this shorter line of
Cobbs = WorksheetFunction.SumIf(Sheet5.Range("H9:H500"), "4/F" Or "C/F", Sheet5.Range("I9:I500"))
But it just ain't working for me. Can the OR operator not work in this situation? If so, what is the proper syntax?
View 3 Replies
ADVERTISEMENT
Feb 22, 2009
I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer.
I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form.
It looks something like this:
View 10 Replies
View Related
Jul 5, 2012
There are 5 sellers and their sales are captured in an individual sheet for each one. Also there's a general sheet where all the sales from all the sellers can be seen.
Now, each seller receives a commission of 5% for each sale he makes. However, when the sum of all the sales of all the sellers is greater than a predetermined goal (let's say 1000 USD), the commissions change to 7% instead of 5%.
IMPORTANT: The seller only receives 7% of commission from the sales made AFTER the goal was reached, the ones before are paid at 5%.
So I need to be able to split the sales in the end to know which were made before and after the goal was reached, so that the commissions are paid correctly.
View 4 Replies
View Related
Oct 23, 2007
I have a macro in a file that I want to only be able to run if a certain cell in the worksheet says something in particular. I.e. This macro can only be run on Day 7 of any week. There is a cell on the worksheet that says "Day 7" or whichever day it is. I want to get my macro to run if that cell does say Day 7 and to come up with an error message box if it doesn't.
View 2 Replies
View Related
Apr 27, 2014
I tried the belwo code to stop Macro if column "A" has the value "#NA" but i am getting error.
{if the Value "#NA" not in the column "A" then macro can continue to run}
[Code] ......
How to rectify the error.
View 10 Replies
View Related
Jan 25, 2014
Is it possible to trigger a macro on a condition like this?
If date more than 1 week arrange date in sequence order.
View 2 Replies
View Related
Jun 17, 2014
I need a code that will stop a Macro if any cell in column 3 equals #N/A All i have right now is:
If Selection.Columns(3).Columns = "#N/A*" Then
MsgBox "Incorrect Value"
Exit Sub
View 8 Replies
View Related
Aug 27, 2008
How do I run a macro atomaticly if the value in the cell is > 3. That is what I have so far:
If Worksheets("Variables").Range("B23").Value > 3 Then
I do not know what I should have before that?
View 9 Replies
View Related
Dec 5, 2009
I have recorded a simple macro that refreshes a set of random data in my sheet everytime I click on the button I made.
When I see a zero in one of the cells I know to stop refreshing. The thing is, I click on the button quite rapidly and sometimes miss the zero. I need to click on this button quite quickly so slowing down isnt an option!
What I need is for excel to stop that button operation when it sees a zero in one of the cells.
View 9 Replies
View Related
Feb 18, 2009
I have a spreadsheet that I need to cut and paste data from one tab to another but only from columns B:K (as the spreadsheet has formulas that can not be alterred after column K. It must also paste that data for the hidden columns.
The condition on the data to be pasted in based on the tabs along the bottom.
So for example, anything that says Corn Products needs to be cut from the "standard customers" tab and moved to the Corn Products tab, and so on for the additional tabs.
I then need to un-check (hide) Blanks from the filter on the Standard Customers tab.
If possible, I would like run the Subtotals function in this same macro; the conditions for that are as follows
For each change in Shipper, Subtotal Billing for <<month>> (Column S)
Page break inbetween each Change in Shipper, then I would also like each subtotal figure bolded.
Also, this is something I must do every month, and each customer may have more or less lines or no lines at all in any particular month.
View 14 Replies
View Related
Mar 4, 2009
Currenty i am designing accounting related excel design. In that i need one macro for entering the value in a table based on some condition. I explaine the concept behind this, could some one able to help mean please share the information
I have a xls worbook with two sheets.
in the first sheet contain the column for entering the values.
in the second sheet having one small table withdate and Numbers.
i need to enter the values in the first sheet S.NO, Date and Value. when i enter the value in the first sheet, the date and S.No is compared to the second sheet table Date and the S.No and the value should be placed in the appropriate cell.
View 9 Replies
View Related
Mar 26, 2009
How can i exit or close the macro after the condition,
View 3 Replies
View Related
May 28, 2009
What I need is a macro to delete a row or rows for a specific range of cells (eg. E20 to E58) when the cells in that range column E is = blank. The cells in column E is formated with a currency sign $. Deleting the row should shift up all rows below. I need to run this macro manually. Excel 2002 SP3.
View 9 Replies
View Related
Nov 5, 2012
How to create a macro that would highlight a row that has the following condition :
name
ref num
sam
123456
sam
123456
alice
342333
In this table, i need a macro that would highlight the row that has alice data due to it been having 1 ref number count under the ref num column. The macro should loop and end with msg " there are 'x' records having 1 ref number count"
View 1 Replies
View Related
Jan 6, 2009
I want to create a macro the will delete all the rows in a sheet if 1 of 2 conditions are met. The first condition is if a cell is blank or if a cell contains the word "negative"
View 9 Replies
View Related
Oct 6, 2006
Two ranges A1:A20 (input values) and B1:B20 (results).
Input values are always the digit 1 which starts the calculation and the result comes up in the cell to the right.
Example:
Input (1) in cell A1 gives a result value in cell B1.
Input (1) in cell A12 gives a result value in cell B12.
I need a macro “StartCalc” to do the following thing:
Go to cell A1.
Put in the value “1”
Check the result cell B1.
If “result value” > 20, then run macro “Test” (this macro is working)
Go to cell A2.
Put in the value “1”
Check the result cell B2.
If “result value” > 20, then run macro “Test” (this macro is working)
…….
Go to cell A20.
Put in the value “1”
Check the result cell B20.
If “result value” > 20, then run macro “Test” (this macro is working)
End of macro.
View 4 Replies
View Related
Mar 6, 2007
I have two reference points "start_calc and end_calc" and I'm trying to figure out what the coding is in the a macro to sum everything between these two ranges.
In case that's not clear, say reference point #1 is A1 and reference point #2 is A26. in A27 I want to the Sum of A1 to A26. But the next time the macro runs the data points migh be A1 to A27 and so on.
This is why I'm usind start_calc and end_calc references rather than specific cells.
View 9 Replies
View Related
Jun 21, 2014
I need the VBA code to automate my repeated task. I have the range of 100 rows and has the date field in a column and i need a macro to extract the rows for three previous days starting from today.
View 2 Replies
View Related
Jun 25, 2007
How do I have the marco delete rows if the O column = a certain cell.
For instance my O column has a bunch of colors
Cell N1 list one color (red) and I want the macro to delete the rows that contain the color from N1 in column "O".
View 9 Replies
View Related
Jul 10, 2008
I browsed the first few pages of searching for "row delete macro" and couldn't find anything that suited my needs very much, so here goes:
What I need to do is automatically delete rows where the entries in column B (date) and column E (string) are the same. It is a very large group of data, so filtering is not exacly an option here.
Here is the twist on it...
I have a column F that contains either "Accepted-Active" or "Accepted-Closed". If I delete a bunch of rows that contain the same column B and E entries, I want to save the row that contains "Accepted-Closed", otherwise I don't particularly care which of the rows is saved.
View 9 Replies
View Related
Jul 21, 2014
Is it possible to use the same macro when ranges have been changed? Attached is a sheet that I update frequently (rows will change day to day, not columns) and the goal is to have a dynamic macro that sums the rows highlighted in yellow and red. Is this possible?,
View 4 Replies
View Related
Feb 24, 2009
Macro for two named ranges. I have this code, which works fine...
View 4 Replies
View Related
Jun 29, 2012
I have a sheet containing employee data - 13,000 rows - one for each employee.
Column A contains their store number (there are about 1300 stores in total) and column H contains their name.
Is it possible to create a macro that, for each change in store number, will create a named range for the employees' names in that store automatically?
View 9 Replies
View Related
Aug 3, 2012
I have a table sorted on column A. I need a macro to go row by row on that list and stop when the NAME (column A) is changed, in this example 3 first rows "aa". then I need to define the range of these 3 rows (A2:D4) in the table and make some subroutine. After finishing with this range I need the macro to continue and find the next rows with the same name, define the Range and go to the subroutine (in my example A5:D5)
The next range will be A6:D7 and so on until last range A27:D27. How can it be done?
I tried some CASE, FOR NEXT, DO UNTIL loops to do this but get stuck.
DATA *ABCD1NAMEDATEALERTTEST
2aa10HB3aa20INR4aa41WBC5ww50ELE6zz30DIG7zz61HYD8dd21CRE9dd41PLT10dd60HB11dd71INR1
2rr10WBC13tt20ELE14tt41DIG15yy40HYD16uu51CRE17ii20PLT18ii31HB19ii51INR20ii70WBC21ii80
ELE22oo20DIG23ll11HYD24ll20CRE25ll30PLT26mm21HB27nn30INR
View 7 Replies
View Related
Jun 24, 2013
Code:
Sub appointment_nieuw()
With CreateObject("Outlook.Application").CreateItem(1)
.Subject = Range("C3")
.Start = DateValue(Range("A3")) + TimeValue(Range("B3"))
.Duration = 0
.Location = Range("D3")
Dim cell As Range
For Each cell In Range("F2:H2")
.body = .body & Space(2) & cell
Next
.Save
End With
End Sub
I have this macro that works great, but every time I have a different appointment. I have to go into VB editor and change the ranges for the subject, datevalue, timevalue, location and body.
Can I get a text box (or something better) to pop up and I enter the ranges for the 5 in it and have it change in the macro?
View 6 Replies
View Related
Mar 29, 2007
i have a workbook with about 32 sheets (number of days in the month, plus index page). each sheet has info on mutliple shows, i was wondering if there was a macro that i could use to go to Sheet 2 (first day of the month) selecet B10:B200 and name it date1, move to the next sheet name it date2, and so forth, through the end of the book.. then go back to sheet 2 select the next 4 columns rows 10 through 200 (in this case, C10:F200), name it Show1Date1 move to the next sheet and name the rance Show1Date2, then go back to sheet 2 and do it again ie G10:J200 named Show2Date1, next sheet is Show2Date2.... and so forth until we hit the end of the workbook at Show30Date(either 28, 30 or 31 obviously)....
View 9 Replies
View Related
Nov 3, 2006
I cannot seem to get the worksheet function sum to work with varables in this module. It is placing zeros where the summed data should be. The variable are showing proper start and end ranges for summation....
View 5 Replies
View Related
Mar 24, 2014
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
View 2 Replies
View Related
Aug 13, 2009
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies
View Related
Mar 26, 2013
In a particular sheet the data (with formulae) are present from rows E5:Q5000. I require a macro which automatically does the following:
- When user enters “submitted” in the row Column N, entire rows above that cell should be selected, and copy-pasted as values to remove all formulas. So if user enters “submitted”, at N31, then data set E5:Q31 should be selected and copy pasted special so as to retain only values.
-Finally the copy pasted data should be sorted as per the status of Column N.
View 4 Replies
View Related
