I am trying to make a macro that will query a column J4 for a date, if the date is found say "Wednesday, July 4, 2012" (J8) get the text from Column K8 "Independence Day" and insert the text in Cell (H1).
The Macro Prints sign in sheets with the date in H2, it only prints Monday through friday. Then skips the weekend and the next date will start the following Monday.
The Federal Holiday schedule is in column range J (Date) and K (Holiday)
I am trying to clean up and simplify my macros by compartmentalizing frequently repeated commands. I like to search for text in column headings and define the cell containing it as a range that I can later use .column or .row commands on, so my macros are a bit more flexible. The code I currently use for this is
Dim XColumn As Range ‘the cell containing the text “X” Cells.Select Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Set XColumn = ActiveCell
I would like to be able to create a Macro (possibly name FindSetCell()) that will accept arguments and achieve the results of the above code in other macros I think the command might look something like FindSetCell(“text to be searched for”,”name of newly Defined Range”) But I have not figured out how to code it
I have a long column of info as below. I want to extract (the name changes)- "SEAPORT TWN" etc and also "01.30" and place into separate columns say G and H at the next available cell.
** SWITCHLIST FOR TRAIN---Grain Spec -East DEPARTURE TIME from WESTTOWN is 01:00 TOWN STOP---SEAPRT TWN Arriving at 01:30
PICKUPS Terminal Shipping Burl North 460020 GRAINCAR empty Elevator Co. Ltd. Terminal Shipping Sante Fe 100396 GRAINCAR empty Elevator Co. Ltd. Train should leave this town with 2 car(s) TOWN STOP---PRARIE TWN Arriving at 02:00
SETOUTS Elevator Co. Ltd. Burl North 460020 GRAINCAR Grn w/Wht Lttrs -4 Bay Elevator Co. Ltd. Sante Fe 100396 GRAINCAR Maroon w/Wht Ltr -3 Bay 1/05/2007 8:57:52 a.m. ...............................
I'm writing some code where I need to know the column in which a specifc text string occurs. Because of the nature of the sheet, the string will only appear in one column, but that could be in almost any column on the worksheet.
For example say the text string is "Year 1", I need to find the column with this string, and then offset down to a predetermined row, or offset over to the appropriate column (something like below).
With Selection .Offset(0, x).Select If List.Selected(0) = True Then y = i - 1 With Selection .Value = Salary * Inflation1 ^ y End With Else
My userform has text that corresponds to my spreadsheet header. I would like to replace the text with a textbox so my headers are variable. code that will allow me to enter my text so when I click enter
In coloumn A have either Yes or No down to line 1000, In coloumn B I have email addresses down to line 1000. What I want to achieve is in cell C1 have all the email addresses concatenated together that have a Yes value in column A next to it.
on one sheet we have a summary of the main list, which includes totals of money recieved, totals of all the different sources (ie, where they heard about us from), the totals of the frequencies they pay (ie, how many donate monthly, quarterly...) ... etc. on the next sheet we have the "main" list of donors, their IDs, amounts, frequency, source ...
the totals on the first sheet are updated manually, but i want to change that as there are a great number of errors.
find a way to place a value of 1 (or a text "email sent: mm/dd/yy") in a specific cell on each row when an email is sent out through some VBA code I currently have. Then when the workbook is opened on another day, the code will look to see if that cell (target) is populated so that it doesn't trigger a second, third (repetitive) email every time the workbook is opened.
The spreadsheet contains rows of many clients, and growing every week. The code needs to be designed to search through each row, along a specific column (lets suppose column R beginning at row 7). When it finds the target rows blank, the code will turn each target cell red, as well as trigger a message box alerting that follow-up action is required.
Cell B4 is selected by user and then runs the macro. The macro then looks UP (A3, A2, A1 etc) cell by cell until it finds the first cell that has a valid date in it and if that date is same as the date that the macro is being run pastes "This Text" into the comments section (Not replacing everything in the comments but simply adding to it.)
2/20/2009 (empty cell) Dave Jonathan Steve
2/21/2009 James Sally Tyler (empty cell) John Betty
I need a macro or formula that can parse a column of data and if it finds a specific string of text ,then it changes another cell on that row to zero. It can have multiple text variables but all return the same value. For Example,
if artisan - matte - flat black artisan - matte brushed gold small - canvas - flat black
is found in a longer string of text
then it should change another cell on the same row to have a value of 0. Also i should mention that i need it to loop as it will be parsing through potentially thousands of rows.
I have a file that the user selects and when they enter a value (in this case, a job title) into the input box, my macro looks for the value in that file. If the value is there, a msgbox pops up that lets the user know that the value was found and it then goes to that cell, displaying in the next msgbox the cell address of where the value was found. I already have the code down for this part of the macro.
My problem is what happens if the file has the same value more than once. Ideally, I'd like to display a message box that returns the addresses of both cells with the same value and then prompts the user to select one of these values as the value they are looking for. I am not sure if a msgbox or a msgbox and then an input box are most appropriate for this situation. Once the user does this, the macro continues. The rest of my macro is built on the cell where this value is, so it is crucial that I make sure there is at least one value selected. Any help is much appreciated. I have included a sample worksheet of what this situation might look like.
Here is the code I presently have for this part of my macro.
Sub GetOpenFileName() strFind = InputBox("Please enter the job title you wish to search for:", "Search for job title in this file")
If strFind = vbNullString Then Exit Sub
With Sheets(strSheetsMainCompProfile) If WorksheetFunction. CountIf(Range(Cells(1, 1), Cells(100, 100)), "*" & strFind & "*") = 0 Then MsgBox strFind & " cannot be found on this sheet" Else
Looking to find 1 of 2 words in a cell in column B and return the word found in the same row in column E. This seemed easy but I am not having any luck.
the cells in column B have several words in them but I am looking for 2 specific words "PLAT" and "ORIG". If the word is not in the cell, it should show a blank cell in column E in the same row, otherwise one of the 2 words should be in that row in column E. A VBA loop would be ideal but a formula that can do it might work as well.
The code below works well also. However, I got 2 problems here. First, I manage to find the word " Sunday", then color the cell below that Sunday. However, I need Excel auto find out Sunday and color that particular columns for me, for example, Sunday found and color start from cell(L55) below that Sunday up to more cells(L59), Range("L55:L59").
Second, I only manage to find first Sunday, I wish the system keep find and keep color untill it found that rows blank, so I using this code.
'Encountered blank cell in row 2, terminate search If Len(Cells(2, LColumn)) = 0 Then MsgBox "No matching date was found." Exit Sub
I have a table that i use for a customer database. and the end user adds new customers to this table, what i would like is for a message box to pop up whenever cell B2 matches an entry in a column in the customers table. the table starts on row 25, and the column i would like to check for duplicates is column B. I would like the message box to give the user the message "A customer by this name already exists, Would you like to load this customers file?" If the answer is yes, then the row that the match was found on would be copied and pasted onto row 1. if the answer is no, then nothing else happens. I hope this makes sense, i am posting this sheet of my workbook for reference.
I have a file which has in excess of 12,000 rows of data in 5 columns (sample file attached with fake data). The five rows are:
"First Name" "Last Name" "Name" (uses CONCATENATE to combine column A & B) "Email" "Date Attended"
There will be duplications in the list as people will have attended more than once over the years. What I want to do is search through the email addresses (Column D with D1 being the header) and where there is a duplicate email address copy the cell to the right of the duplicate (F#) into the next available cell to the right of the first occurance and then delete the row with the duplicated email address.
I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.