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 RepliesThere 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.
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.
Is it possible to trigger a macro on a condition like this?
If date more than 1 week arrange date in sequence order.
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
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?
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.
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?
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.
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.
How can i exit or close the macro after the condition,
View 3 Replies View RelatedWhat 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 RelatedHow 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"
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 RelatedTwo 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.
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 RelatedHow 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".
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.
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.
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 RelatedIn 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.
I have accounts that I need to compare to see if they exist on my system the account that has a listed date, exist on my system then if I can fill the dates in the accounts the match then I will be able to delete the other accounts that don’t have a date see attach file for more understanding.
View 2 Replies View RelatedI'm working on a spreadsheet designed to track total overtime hours worked in a year; on the spreadsheeet is a column to keep track of each day's total OT, the week's total OT, as well as a column to track the year's total. There is a formula in the week total, but the year total is calculated via a macro (day of the week total, added to the existing year total, result updated, so, each day has it's own button and macro). We have a shift that works a different week than the shift that needs to track overtime, but still must be included in the list.
Therefore, I created a column to place the shift designator so there can be recognizable diffrerentiation. With quite a bit of help from this board, and others, I've created (or been kindly given) the following macro (this is just a part of it) to total the day's overtime and existing year overtime and input the result into the cell. I now need to have this executed only when the condition I specify (say, in cell D1) is met (that would be the shift, for example the text M1 or SST). Please note, the week totals are only for user reference - they do not come into play for calculations of year totals. The below macro actually takes the totals from a day of the week and adds it to the existing year total, placing the result in the year total column.
I want to be able to run a certain chunk of code in the macro if a certain condition is true, or if that condition is not true then I want to bypass that chunk of code. It doesn't matter if this is accomplished with an if... then... else statement, or some other way.
View 6 Replies View Relatedif there's a macro that can be run to check one worksheet. In this worksheet there is a column where the value should be "true". If this value is true, I'd like to copy the entire row of data into another worksheet.
There can be multiple rows of data where this condition is met.
Attach is a sample of my project. I have to loop through B9:B80 in sheet("Raw Data") and capture the value of each cell. EG:
* if Value captured is 120A --> check with sheet("SP14F") from the row which contains the same value.
* i have to check that the aperture, outer and inner values of Layer 120A and so on..of the Raw Data sheet is the same as that of in the SP14F sheet.
* if the values are not the same, change the font to red
i understand that this task requires looping and arrays.
Lets say we have data in Column A, B, C and D and no row left blank. In the column A no cell left blank however in B, C, D any one cell only contain a value in that row. If B10 has any value in it then C10 and D10 are left blank (not empty). I want to segregate the data in Column A based on the value in B, C or D. So this one column data ( that is Column A) will split into three column. this segregated data to be put in E, F and G.
Wherever Column B has any value that's greater than zero content from the column A from the same row should copy to the E, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the F, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the G.
I am trying to create a macro that will look in Column B for the word "RPLCASE." Whenever "RPLCASE" appears in Column B, the cells in that particular row will move over (Right) one space. I am simply trying to align the data from a file that I FTP'd from Net Term to Excel. You can see how the data should look in the 2nd example.
14-Feb-08JERRPLCASE671150536:116:56RPLCASE78308147:077:54RPLCASE89431228:228:57RPLCASE910592289:159:59RPLCASE10114732310:0010:48RPLCASE1112146811:4511:58RPLCASE12136563612:0612:56RPLCASE13144122113:0313:58RPLCASE14154642314:0114:23**********--------------TOTALS4632228LDLPKGPIK671361126:046:58PKGPIK781461247:007:56PKGPIK8963598:168:55PKGPIK9101591309:019:59PKGPIK101115113910:0010:54PKGPIK1112968711:3211:59PKGPIK121319916012:0012:59PKGPIK131412310313:0013:59PKGPIK141518716414:0014:59PKGPIK15161119715:0015:54PKGPIK16173316:3016:31**********--------------TOTALS13741178
I have a spreadsheet that calculates percentages and then outputs the results to a pie chart. There are 9 different percentages being graphed in cells A41 to A49. The chart looks weird if any of the percentages end up being 0, so I have the formula set to add 0.00001 to each calculation (so they show up as 0% and display on the chart as 0%, but truly are 0.00001). I would like it so that if any of these 9 percentages ends up being 0 (or really 0.00001) that the row automatically hides and thus won't display on the pie chart. How can I create a macro that automatically runs to accomplish this, and automatically updates as percentages are recalculated.
View 3 Replies View RelatedThe issue is the I want to put the condition here in my the code that only copies the desired cells if the sheet is named specifically such as "Jan", if not named as this the worksheet should not be used. The current code I am using was posted on this site in 2009: VBA to copy specified cells from all the files in a folder
My modified code is:
Option Explicit
Public strSourceFldr As String
Public EachFile As Object
Public objFSO As Object
Public objFolder As Object
Public objFile As Object
Public strSheetName As String
[Code] .........