Copy Date Rules With VBA Code
Feb 18, 2014I have a file which I want to copy the lines.
I have made a test file for clarity. See annex.
ADVERTISEMENT
Excel - Date Validation Based On Rules
Nov 29, 2011Validation on Excel
The User enters the date in cell D5 - I would like excel to validate the date based on the following rules:
1. Format "dd/mm/yyyy"
2. Prevents entering any weekend dates
3. When the user enters the date it can be the previous or current months date range
I have tried several ways in excel but I can't get anything to work. If an error occurs I want an alert message to appear.
Calculating Retirement Date Based On Retirement Rules?
Feb 8, 2013I need to calculate the retirement date of an employee based on the following 3 rules (in essence, the employee's retirement date is whichever rule the employee meets first)
At least 55 years old AND 20 years of service
OR
At least 60 years old AND 5 years of service
OR
At least 65 years old AND 1 year of service
I have Date of Birth in Column F and Hire Date in Column G.
I was able to hack something together when I had only two rules to deal with, but it was a very hard to follow IF statement. With three rules I'm having trouble.
Help With IF Rules
Jan 8, 2009I am creating a worksheet that I need to encorporate an If statement into. What I need to happen is when cell A1 has a number in it (1,2,3,4...) I need it to perform a function on cells B1 and C1. However there will always be information in cells B1 and C1, but I only need it to perform the function when the number in cell A1 is anything but 0.
View 8 Replies View RelatedCreating Rules For A Table?
Dec 9, 2013How can I create a rule for a table that finds a specified word and replaces it with another such that the rule would continue to apply to the table even when a new record (or row) is added. There has to be a simple way to do this without a macro.
View 3 Replies View RelatedVBA Not Following Consistent Rounding Rules?
Jun 13, 2014This Subroutine:
[Code] .........
Seems to behave differently each time it is called from my main Loop. As an example the first loop finds:
TotalTubes = 6
TubeRows = 4
TopRowTubeCount = 2
Where 6/4 = 1.5 and is rounded up to 2
The controlling variable in my code for Length is TopRowTubeCount and it is a Public Variable Dimensioned as an Integer
However on the next loop it finds:
TotalTubes = 210
TubeRows = 4
TopRowTubeCount = 52
Where 210/4 = 52.5 and is rounded down to 52?
I even used the round function for the TopRowTubeCount calculation and the erratic behavior continues.
InputBox Validation Rules
Jul 12, 2006I have 2 input boxes. Input Box 1 asks for user's Date of Birth. Input Box 2 asks for user's name: Pretty simple right! They will loop until following 2 conditions are met: If the user enters something in them OR user presses Cancel. If they are empty and Ok is pressed then keep on looping.
View 4 Replies View RelatedSome Cells Not Following Protection Rules
Aug 5, 2006I have set all cells in a worksheet to be protected. I then selected specific cells that are not protected. The problem is that cells A11:D29 except C13:C14 do not follow the protection rules in that I can edit them (but when I right click, I cannot format, just like they're protected). These cells are NOT part of the group that was selected to be unprotected. When I unprotect the sheet and check the protection setting for these oddball cells, it shows as protected. All other protected cells do not allow entry (which is what I want).
View 9 Replies View RelatedDisplay Conditional Formatting Rules?
Oct 9, 2013how to display the current set of conditional formatting? I don't want to go to the cells I want a listing of all the conditional formatting rules. I am sure I have seen this somewhere in the past but cannot locate it.
View 3 Replies View RelatedCombine 2 Conditional Formatting Rules?
Apr 12, 2014I have two conditional formatting rules that work independently, but I am looking to combine them into 1 rule, making (2) subject to (1) when (1) is true
(1) =$B3:$B6="y"
(2) =AND(COUNTIF(K$3:K$6,MIN(K$3:K$6))=1,K3=MIN(K$3:K$6))
Setting Rules For Multiple Of Number?
May 29, 2014I have started to use excel again because I need to create a spreadsheet for maintenance hours done on a vessel. The problem I have right now is, how to set a rule to allow only certain multiple of a particular number to appear. For example,
I have a initial cell value where I have to manually insert the total number of hours that have gone by in the vessel. Let us say that this value is 500 hours.
In another cell which is labeled, "Last Completed", it would have to read at how many hours this job was last completed.
I also have another cell which gives you the time interval before the job is performed again. This value is 250.
So if the current number of hours is 500, the last completed date should be 250. (assuming Maintenance was done at 0 hours and 250 hours).
How do I make it so that the "Last Completed" cell would read 250 until I hit 750 hours which at that point should read 500 Hours.
Calculating A Discount Based On Certain Rules
Feb 11, 2009I'm looking for assistance with working out the following formula. I live on an apartment complex in Marbella Spain and we pay Monthly Community Fees of say $100.
We want to calculate a penalty amount for late payers of say 30% of the monthly fee due for non payers? So for instance if the monthly amount due in Cell D8 = 100 and the actual amount received in cell E8 shows 0 or then increase any figure due for the following month in Cell F8 should increase by 30% ($ 130) in the following month. and so on
I use Excel ver 2003. Trust my query is reasonably clear?
Different CONCATENATE Rules Depending On Cell Value
Oct 28, 2009I'm trying to do a CONCATENATE function in Excel 2003. The first part of the function (this is working correctly) is a) concatenating 4 cells from a different worksheet (WB_NEW) into one cell and is b) putting a space in between each entry:
=CONCATENATE(WB_NEW!AH3," ",WB_NEW!AI3," ",WB_NEW!AJ3," ",WB_NEW!E3)
What I'm now trying to do is enhance this function to perform with 2 separate concatenation rules i.e. "if one of the cells specified above is blank, concatenate a different set of cells".
Automatic Fill Cells According Some Rules
May 15, 2013I would like to fill some cells with a customer id if this customer is the same as the cell bellow. As my example below.
CUSTOMER idCUSTOMER NAME TYPE
737346 Nikos 81
1154765 Kyriakos 81
1154780 Thanos 81
I would like to fill automatically the empty cell bellow Kyriakos the customer id 1154765 in new column. I want in column "new" to fill with customer id and when the customer id is empty to fill the same customer id as above customer id.
Like the example below
CUSTOMER idCUSTOMER NAME TYPEnew
737346 Nikos 81 737346
1154765 Kyriakos 81 1154765
1154765
1154780 Thanos 81 1154780
Using IF Function To Use Different Formulas Depending On Different Rules?
Nov 27, 2013I have got to this stage with a formula but cannot understand why it isn't working:
=IF(OR(A2=1,A2=2),(MIN(B2:B6)+50),(MAX(B2:B6)+50))
If A2 equals 1, I want it to add 50 on to the smallest (earliest) date in C2:C5.
BUT
If A2 equals 2, I want it to add 50 on to the largest (latest) date
The formula just adds 50 on the smallest (earliest) date, whether A2 equals 1 or 2.
What am I doing wrong?
Delete Rows Based On Rules
Aug 21, 2007I am trying to clear row with the following rules:
the last cell must not contain 0, if 0 delete entire row.
the first cell the the A row should be only three character long, if more delete entire row.
Now I managed to delete the 0 in the last cell and but cannot get the VBA to count the characters in the first row and if more than three, delete the entire row . I know to count the characters in a cell is done by Len(Rng.Value) , but getting the VBA right is alluding me .
the script is here,
Sub Del_rows_with_zero_in_column_of_activecell()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Const StartRow As Long = 1 'Row to Start looking at
Dim StopRow As Long
Dim Col As Long
Custom Rules For Sheet Naming
Apr 4, 2008I want something strange but i guess you can do just about anything with excel these days if you ask around.
I want names for the sheets that are contained on a specific excel file that i have to be automatically generated accordingly to the following:
sheetname = runxyy_s=zz%
Where x is the lowercase letter of the word formed on the name of the first sheet of the workbook
and yy and zz are values stored on specific cells on the sheet,lets say yy is A1 and zz is H4.
Permitted Permutation/Combinations With Some Rules
May 25, 2007I have found this very challenging, and have really confused my self on how to systematically approach this. I have the user enter the value in the black highlight boxes, some subtraction produces a base value, the user may also enter the tolerance of the working value (base value), effectivlly giving rise to a small range to generate the permutations from.
What I would like to do then is find all permutations of the inventory (letters in the sheet) with their corresponding values right above them to the small range determined via the user (working value and tolerance). There are also quantity limits for each item. There four basic rules that need to be adhered to:
1) Permutation can't have both S and PP, so one or the other
2) Sum of H and HN must be greater or equal to sum of S and PP
3) H or HN or both must be present, so their sum cannot be zero
4) If HN is less than or equal to 2, and H is greater than 2, then NA and NG must equal 0.
Copying Data To Cell With CF / Rules Will Change On Its Own
May 4, 2014I had previously posted a query on deconstructing 2 set of tables (with similar heading)(in sheet 1 & 2) & combine them side by side with similar heading (in sheet3) so as to use condition formatting to highlight the differences & visually i can compare them easily.
1 group is 2 tables to compare. I have about 30 groups. So after creating the 1st group, i intended to use it as a template, without having to create the conditional formatting again & again. But after copying, the conditional formatting rules change. I am not sure why.
I attached a sample of my template for reference.
Sheet 1 - Table 1
Sheet 2 - Table 2 & part of Table 1
Sheet 3 - combine both Tables with similar column header side by side
in Table 3, cell B3 rules are "=C5<>B5"
SO the problem is when i copy data from Sheet 2, Table 2's Column Header B (cell f9 to f421) to Sheet 3, Column Header B2 (cell c5 onwards) the rules change to "=C5<>XFC5"
Use Validation Rules To Only Allow One Input In Multiple Cells?
Oct 5, 2009Ive been having a little issue with this spread sheet. The problem ive got is I cant get the validation to work where it only allows one yes in a bunch of cells and gives an error message, heres an example of what im talking about.
View 3 Replies View RelatedPrevent Resetting Of Conditional Formatting Rules?
May 24, 2013I have a spreadsheet with many rows of data. I want to be able to see the last month or so of data (each day is one row), but I do not want to have to scroll to the bottom of the worksheet to see the recent data, so I want to reverse the data - I want Row 1 to be my column headers, and for every new day I want to insert a new row at Row 2 and add my data. However, when I do that, my conditional formatting gets all messed up. Once the new row is added, the formatting rule changes its range to only those rows which had data in them before I did the insert, each new row gets a separate but identical rule that applies only to itself, and the headers row gets its own rule.
That seems incredibly stupid to me. Is there a way I prevent the formatting rules from changing and just stay the way they are which applies to the entire column?
Extract Text From String (complex Rules)
Feb 19, 2012I have a column with data in the following (ideal) format:
texttextexttext/s2
texttextexttext/s2/c14
texttextexttext/s24/c2
and need a VBA solution to automatically split it across 3 columns, i.e
column1 column1 column1
texttextexttext s2
texttextexttext s2 c14
texttextexttext s24 c2
/s will always be present and will always be followed by one or more digits (no limit).
/c may or may not be present but if it is present, again it will always be followed by one or more digits (no limit)
I have been using TextToColumns with "/" as the delimiter and it works fine in most cases. However, the data frequently contains additional "/" characters which breaks everything. some examples of valid but problematic data are:
texttext3/12/2012text/s2/c2
text/textexttext/s2/c2
and even:
text/stext/ctext/s2/c2 (i.e. multiple occurrences of /s or /c)!!!
Applying Data Validation Business Rules
May 4, 2012I am in charge of validating 800+ excel reports coming from our vendors each month. I use excel data validation feature everytime I receive a new file. Data Validation feature is native to the excel workbook and cannot be saved to be used with other workbooks (at least; this is what I think).
What is the best way to write a generic validation script that I can run on any workbook. I am thinking somewhere along the lines of a test script that looks for specific data, data format, required data etc.
Here are some of the rules I have to validate against:
Cells in Column 'A' must contain data, reject if not populated.Cells in Column 'B' must contain a specific value (cat, dog, car), reject if other.Cells in Coulmn 'C' must contain (CA,AMM) only if cells in Column 'D' = 'Yes'Cells in Coulmn 'E' must be in proper casingAddress must use U.S. Postal Service standardsZip Code must be 5digits+4Exclude formatting charecters like '( )' from phone numberMinumum age must be less than or equal max ageMaximum age must be greater than Min ageCheck for populated data in cells, else reject file.Date must be in 'YYYMMDD' formatTime must be in HH:MM:SS' or 'MM:SS' formatID must contain 7 integersThis is a sample that represent 70% of files recived.
3 Color Conditional Formatting Formulas Rules?
Jun 23, 2012How do I create 3 color conditional formatting formulas for the following rules?:
if today is 15 days or greater before the day in the cell then green
if today is between 14 and 7 days before the day in the cell then yellow
if today is within 6 days to zero days before the day in the cell then results
anything is clear
x8:x21
5/10/2012
5/14/2012
5/21/2012
6/11/2012
6/12/2012
6/12/2012
6/12/2012
6/20/2012
6/22/2012
6/23/2012
7/4/2012
7/5/2012
7/8/2012
7/12/2012
Maximum Number Of Conditional Format Rules
Jan 14, 2010I need to add more conditional formatting to my spreadsheet. But it seems to belimiting me to 3. Is there a way of increasing it?
Script To Add Conditional Formatting Rules To Column Of Cells
Dec 2, 2013I'm having to successfully add three conditional formatting rules, and associated cell interior colour formats if any of the rules are met.
Each time I run my macro, I want to refresh every cell, within a defined range within a Col P, with these rules.
So far, I can add the three rules to each cell, but when I try and add the format colour - for when a rule is met - I keep getting a 'Subscript out of range error'.
(As an aside, is my For / Next loop approach the best one to take to add the formatting to each cell, or can it be written much simpler?)
Here's my script:
VB:
Dim Col_P As Range
Dim Cell_in_ColP As Long
'Set up range of cells to add conditional formatting rules to
Set Col_P = Worksheets("tRIIO Pack Notices").Cells(2, 16).Resize(Lrow - 1)
[Code] ......
Automatic Formatting Of Cells Based On Rules / Criteria
Jul 22, 2014I have a long set of data that I extract a "table" out of based on index matching controlled by 4 different criteria. This part is working as intended. However I need to format the cells with the corresponding "fetched data" based on another criteria. Makes sense? Have a look at a spreadsheet i made representing my problem. It has comments for the problems.
excel tableformatting problem.xlsx
Randomly Select Cells Based A Couple Rules
Nov 6, 2007I have a sheet that has 13 players listed in rows down column A. I have 6 columns each representing a quarter in a game. I was wondering what the best way would be to randomly select 5 players in each column and to distribute each cell selection as equally as possible over the 6 quarters.
So what I am doing is having excel designate equal amounts of playing time for each player each game.
Is this possible.
Can I base this off of previous games?
Date Doesn't Appear Automatically When Running Date Code
Aug 21, 2009Private Sub txttodaysdate_change()
txttodaysdate = Format(Now, "mmm/d/yy")
End Sub
when i use this code i wnat the date to automatically appear in the text box but it doesn't I have type something into the textbox then the current date appears,.
VBA Code To Validate Date When User Enters A Date
Jun 13, 2014I am looking for VBAS code to validate a date when a user enters a date. The date format must be in format dd/mm/yyyy for eg 16/05/2014 , 13/06/2014 etc
If date not in this format msgbox to advise user date format invalid