I am trying and failing to write an if statement in excel (not vba) to check the contents of cell H3069 to see if it equals 29/06/2011.
I have written the following
Code:
=if(DateValue(H3069)="29/06/2011","Y","N")
which returns #Value!
IF statement that looks at a cell to see if it contains a word.
ie IF A2 has " Total" in it then do this
I'm trying to create macro that will clear the contents of the cells in the 4 columns to the left of a cell that = 0 %, as well as the cell that = 0%. For example, if cell F13 = 0 % then the contents of B13:F13 would need to be cleared. The range in which data is being pasted into is B13:F27.
View 4 Replies View RelatedI have a 700 line spreadsheet and would like to delete the cell next to another cell if it contains a certain string. I.e if A1 to A3 equals "Test" then i would like to delete the contents of B1 to B3, But if A2 equals "Completed" then only B1 and B3 should be cleared.
View 4 Replies View RelatedI need to confirm that a date is entered correctly into a userform.
This code works for a command button, but not for a WorkSheet_Change event.
My textbox is the first in the userform, and needs to be checked immediately, as the labels in the rest of the userform all change to reflect the date that was entered.
What is the best way to do this? I tried a keycode = vbKeyReturn option, but cannot get it to work?
Code:
Private Sub DateCheck()
Dim DateString As String
Dim DateProper As Date
Dim EntryOK As Boolean
EntryOK = False
[code]....
I need to create a formula which will change the contents of the cell to one of 3 options based on the value of another cell
For example if I was using B2 as the cell to reference I need to say IF B2 = 0 then say "Closed", IF B2 = 1 then say "Open" and if its any other value then say "Error".
I'm having some trouble having all 3 if statements nested correctly.
I don't want to do this as a macro just as a formula.
I have a conditional IF statement, where I want the content of the cell to be blank if the result of the IF statement is false, ie I want the content of the cell to be as if there were no formula in the cell (this if so that the formula COUNTA(Sheet1!$B:$B) only counts the cells where the result is TRUE).
For example, IF(A1=1,1,"") where A1 1, the content of the cell will have something in it (a space), and won't be blank.
i am trying to automate altering a DAT file. Basically, each line of data in the DAT file contains an entity name, an account name, and a value. for some of the accounts, i need to change the entity name. for all other accounts, i can leave the entity name alone. if i copy the DAT file into Excel, and use text to columns, how can i create a macro to compare the accounts to a range of cells containing a list of the accounts i need to alter the entity name for, and then change the entity name if there is a match?
the account does not need to match perfectly, the accounts i input only needs to contain the reference account. the list of accounts and the entity name i need to change to are in cells on another sheet. i thought about doing a loop to run through each line of data, but i am not sure how to do that.
is there anyway I can do a conditional formatting of something like that to color a cell if the date in the cell is a friday?
Dates will appear in a column like this:
16-mar
17-mar
20-mar (this is a friday, it should be red)
21-mar
21-mar
If cell a1 equals todays date add cell b2s value to it and put the new date back into cell a1.
View 6 Replies View RelatedI'm looking for an if/then statement that will check if there is any kind of value in cell b before doing a calculation to cell J
with some basic code. I want to check the activecell, if it's blank then copy the contents from the cell above, if it's not blank, leave it & go down to the next cell & repeat until last row.
View 7 Replies View RelatedTrying to look at cell I2 to see if it's blank, has the current expiration date of 9/1/2014, or has an earlier expiration date.
I'm entering in K2 the following: IF(I2="","",IF(I2="9/1/2004","OWES","C")) At this point if the cell is blank it will show blank in K2 but if there is a date it always shows C.
I want to check the contents of all the cells in S Column to see if it equals "Process Recover/Rebill"
If True, then Change corresponding cell in C Column to "Manual Correction"
I have this VBA and it doesn't look like anything is processing. It appears that I must have something missing in the If line..message about missing.
(tot_new is the number of rows of data) I begin in the 3rd row.
For Counter = 3 To tot_new
If Range("S" & Counter & ").Value = "Process Recovery/Rebill" Then
Range("T" & Counter & ").Value = "Manual Correction"
End If
Next Counter
I am trying to set up a conditional formatting which will look at the cell contents and check if the value exists in a range.
The range to compare will be over multiple columns and multiple rows.
I have tried a range of "sumproduct" and "count if" and I am still not having any luck.
What I would like excel to do is to count what surgery/hospital (B)is referring for what product (F)
help.xlsx
I have the following columns that return cover period dates.
Start of Cover PeriodEnd of Cover Period01-Nov-0631-Jan-0701-Jul-0831-May-0901-Dec-0930-Jun-1001-Jan-1131-Dec-10
I however need a macro that will delete the cell contents if the "Start of Cover Period" (column AK) date is > than the "End of cover period" (column AL) date.
Thus it must compare ak2 with al2 and if ak2>al2 then clear both cells. if AK2
I have a user form that when a command button is clicked it enters the data from the from into coloums a,b,c,etc. I have code to do this but I want to add code to add a static date in coloum B based on if coloum A had data entered from the form. I need the date not to change to current date when the file is reopened. I am trying to elimate a date text box in the form. I have a link to the file http://www.box.net/shared/zdtsjv0qos
View 7 Replies View RelatedI need to check a range of cells (B4:B35) and see if any of the contents are less than a specified cell (M1) and then show a message, (the message part I can do). I have tried using For Each but I then get the message for every cell that is below the specified cell (which in theory could be all of them). I have also tried using an If Any statement but didnt work.
View 5 Replies View RelatedIs there any way to make a cell's contents change automatically according to the present date?
View 9 Replies View RelatedHow to check cell from a " DATE" column has todays date or not?
View 9 Replies View Related
I have a range B2:ZZ2000
Some of the cells contain a variety of dates (formatted as d-mmm) Other cells contain either "i", "n" or are blank - or contain a text string, eg "his name"
I'm looking macro code that will: Look at the range and only for any cell containing a date, replace it with "y".
I want to be able to test if a date entered in a cell (Format dd/mm/yyyy) equals a "Monday". If it doesn't, I want to display a warning message.
View 6 Replies View Relatedim trying to check if there is any contents in the row above and if there is insert a new row, if not leave it
*Row1 is the row i want to insert a new row above*
Row2 = (Row1 - 1)
Worksheets("TypesOfChanges").Activate
Worksheets("TypesOfChanges").Rows(Row2).Select
[Code].....
My excel worksheet is a Project Status Log with many rows and columns. My code goes through each row and sorts the projects Unique_ID by Launch Date. On the Launch & Revised (12 & 13) columns, I need to perform the following for each row (I reference Column 2 which is a Unique_ID).
Check if Revised value is populated
If yes then use Revised value else use Launch value
Check if value is a date else store Unique_ID in array Other
If the value is a date then check # of days from today to value
My problem is that if the value is not a date it does not store in array Other but may store to any of the alternate arrays I am utilizing. (I believe this is due to the string being converted to a date and then checked for date difference but don't know why this is happening.)
I have added more than the code I described in an effort to provide more understanding, so if you need more detail I can attempt to explain but right now my brain is hurting.
Do Until Sheets("Project Log").Cells(aRow, 2).Value = ""
On Error Resume Next
'Separate Top 10
If Sheets("Project Log").Cells(aRow, 3).Value = "Y" Then
a = a + 1
Redim Preserve arrT10(UBound(arrT10) + 1)
arrT10(a) = Sheets("Project Log").Cells(aRow, 2).Value
Else
I'm trying to find a formula to work out if i have 2 dates, sum data between those dates, but only if
So i kind of need a sumif/sumproduct in one?
What i need to do is this. Find enquiry made for the Hyatt between the 26/04/2014 & 20/06/2014
Hyatt
26/04/2014
20/06/2014
I have managed to do a sum product to find the enquiry between the date range, but can't get it to add on ONLY for Hyatt.
I have two columns, column Q and R. Column Q has Data Validation set, where you can only choose one of four options in any given cell between rows 2-250,000. When "Complete" is chosen, the cell turns green. Now when "Complete" is chosen in column Q, I need the cell next to it in Column R to auto-fill today's date. However, the date should not update to the next day. So if the cell autofills to today's date, which is 12/6/13, tomorrow, when the date changes to 12/7/13, the cell still reads 12/6/13.
View 4 Replies View RelatedI've got the following function that check if each cell has a date greater than TODAY(). If result is true, it'll display "NO GO". Otherwise, it'll display "GO".
I would want to improve on it such that if any of the 'B5:F5' cell is empty, it'll display "Incomplete" instead of "No Go".
See the attached example spreadsheet which will make this much easier to understand. I want to create a formula in the second worksheet that says "If cell A1 of sheet 1 = "a", Then i want to put the contents of cell B2 ("flour") into cell A1 of sheet 2, I want to put the contents of cell C2 ("egg") into cell B1 of sheet 2 and I want to put the contents of cell D2 ("sugar") into cell C1 of sheet 2. If not put 0.
View 3 Replies View RelatedI have a macro right now which makes a selection and then checks that selection to make sure it has less than 5,000 rows. If it has more, then a message box appears and we end the macro. However, in order for this macro to work properly for me, I need to change the if statement so that instead of checking for the amount of rows selected, I only need it to check if ROW 2 is selected. Is this possible? If not, can we have an IF statement to check the contents of ROW 2 (Row 2 is my header on the worksheet and will never change)?
Here is the code I currently have: [Code] ......