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 need to change a few hundred cells (one column) where the first letter may be a capital letter to a lowercase letter.
So:
GetAwardfromBid to getAwardFromBid
or
SmallLertter to smallLetter
I've done the following:
Sub BrownBH()
If Range("Brown!B4:B31") = X Then
Range("C4").Value = [#A]
Else
Range("C4").Value = NT
End If
End Sub
However, this doesn't even work.
When somebody enters an X in a specific cell on one worksheet, it's supposed to change the value to A of a specific cell in a different worksheet. Sounds simple enough...but...
Is there a way to get Excel to automatically change the first letter of single word entry to lowercase in each cell of the first row of my worksheet leaving the case the same for all other letters?
View 5 Replies View Relatedi want to fill down a column and instead of my formula changing from A6 to A7 i want it to change to B6.
View 9 Replies View RelatedI 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 am working on a spreadsheet for work, and have managed to do everything I need to so far but I need to colour specific letter strings, certain colours within a range of cells (each letter string will only appear once on each sheet)
The strings I will be looking for vary depending on data entered so I will need to cell reference them
The strings that need colouring are in cells with other strings that must stay black (They cannot be separated from other strings due to the nature of the grid)
I need some strings red, some green, and some blue.
These changes should also apply to the whole workbook not just one sheet.
Is there a way to do this with the VBA code.
i am downloading data from a website and one column of cells contains the date and the time. i only need the date and i am trying to delete the time from all cells in the column. i've tried formating but the time stays in the cell. i could go through the whole column and delete the date and time and enter only the date but that is cumbersome. i was wondering if there is a way to delete the time and keep the date in the cell?
View 9 Replies View RelatedI have a range of cells that I want to watch for a change in contents. If any cell in the range has a change in contents I want to launch a MessageBox that asks if they wish the new value to be entered automatically in all of the other cells in the range. Example:
Range = B8:M9
User changes F8 from "empty" to 38. MessageBox should launch asking if they want 38 to be entered into all of the cells in the range. Upon answering yes new contents of F8 should be inserted into the rest of the range.
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.
what i need is to affect the contents of cell c3.
if i select cell e15 i would like cell c3 to read 123
if i select cell e17 i would like cell c3 to read 234
i believe this could be done using an cell formula if statment but due to teh number of cells i wish to have affect c3 a vba based solution would be required.
i've built an if satement to do this under worksheet activate but not sure how to use the worksheet change way
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.
I am relatively new to VBA. I am creating an attendance calendar that tracks employees calling sick, late etc.. It is a point based system. What I am looking for is, a way to clear the point value that was manually entered in a specific cell (I3), if there is a Value manually entered in (CU3). Each column in my worksheet is for a specific date ie; I3 is the cell where I enter the points (1.00) for that employee by calling in sick on 3/1/2013, (Column "I" is for 3/1/2013). After 90 days, this point accumulated by the employee does not count against them, so I need that point entered in (I3) to either = 0 or the cell contents to be cleared if there is a value entered in cell (CU3) which is 91 days after, so my (A3) cell does not add that point acquired on 3/1/2013.
I need this to run in a range (I3:I450) so if any value is entered into (CU3:CU450) it has the same result and continue to for (J3:J450) so if any value is entered into (CV3:CV450) and so on..
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
For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).
When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.
=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")
Is there any way to make a cell's contents change automatically according to the present date?
View 9 Replies View RelatedIn sheet1 I have a simple database consisting of 5 columns of data
Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500
what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.
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!
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 have a formula in cell C2, =Summary!B2
I want the formula in cell C3 to be =Summary!C2
Then the formula in cell C4 to be =Summary!D2
etc etc.I need to do this for 43 rows so just want to drag the formula and the column number rather than the row number change.
i need a formula for a sign in sheet for the academy i work at. we have night and day classes of the same sessions, in the planner they are listed as d1, d2 d3 and n1 n2 n3 and so on for each.
so basically im trying to create a formula to quickly ender the d1, d2, d3 ect into the cells on eachpage for the students to sign in for each day it works out to be about 190 calls and 9 diferent classes to you can see why id like to speed this up. problem is i need the number to change not the D and excel want to use the cell allocation.
as you can see from the document i have not attatched, i have created a formula to add in the dates automatically, i would like to do the same with the session numbers or the D numbers, this is one of the shorter courses and has about 190 days
I just want to show a letter and change it to any letter and show it again in seconds. Can it be done with formula, maybe with RAND formula or any other formula.
for example when i press F9 the letter A change B, and then i press F9, it changed to C, and then when i press F9 and it changed to A again, and i press F9 and it changed to B, and so on...
(Is it possible too..with a little variation if i press F9 continually, it changed every 5 second?)
I have a user whose links in his spreadsheets are in UNC format. He changes them to drive letter mappings, but when he opens the workbook again, the UNC format returns. how to change the links so that they remain as F: etc.. rather than \servernamesharename The user has MS Excel 2000 and Windows Xp Professional
View 2 Replies View Relatedhow to allow both these codes to work on say sheet 1. I can get one to work just fine. So, any tips on how to get them both to work on the same sheet would be great.
Private Sub Worksheet_Activate()
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel. Range)
With Worksheets("Sheet1")
If Not Application.Intersect(Target, .Range("n1:n200")) Is Nothing Then
If Target = "S" Or Target = "s" Then Target = "Submitted"
If Target = "A" Or Target = "a" Then Target = "Approved"
If Target = "I" Or Target = "i" Then Target = "Investgating"
Else
End If
End With
End Sub
and this,.............
Looking for VBA that can change the font size of "•" char(0149) within any string in selected cells.
Also looking to delete the last "•" char(0149) within any string of selected cells.
What would the syntax for these two functions be?
I have attached a workbook stating my problem.
file1.xlsx‎
I work for a UK charity and have a list of funders in an Excel 2007 spreadsheet.
One of the columns refers to the date on which a new application for funding can be made to that particular funder.
In many cases new applications for funding can't be made for 1 or more years since the last application - sometimes as many as 5 years later. How to get a cell to refer to the date that it contains.
For example, say I have in cell A1 "The Acme Funding Organisation" and in cell B1 (i.e. the "Reapply when?" column) a date of 01/04/2013 (British date format, i.e. 1 April 2013) then what I want Excel to do is to look at the date in cell B1 and if that date has been reached to highlight the cell red. That way I'll know that the reapply date has been reached & that a new application can be made.
I am trying to create a sheet in XL 2010.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1
B1
C1
D1
E1
Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due
January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3
Ive also attached the worksheet
I have the the following spreadsheet: [URL] My formula in Columns A to E is the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE))
I would like to do the following in Column A: If the date in Column C is greater than today, then Column A must also say "Branch Not Open". I have tried the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),IF(C:C<=TODAY,"Branch Not Open, IF(AND(C:C=>TODAY,VLOOKUP(Query1_2[@Branch],Master!A:I,9,FALSE)))
But Excel does not like the formula at all.
I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.
View 1 Replies View Related
