I have a userform where the user will identify a record to delete. I need to search another worksheet (Month) for the event's name which is associated a date. Once it finds this event's name I need to clear the contents of that cell.
Here is the code I am using for the record deletion from the 'Data" worksheet. I need to also locate and clear the cell as stated above.
Basically i have date in col A, Name in col B and Status in col C.
Let say A2 & A3 are both 1 Jan 2014, B2 & B3 are both "exam 1", C2 is "success", c3 is "failed"
Is there any where to add helper column so in row A2 : C2 mark with "blank" and A3:C3 mark with "yes"
So then when i pivot it ..i can add the helper column as criteria and choose "yes".
I attached sample file here [URL] ..
Just click download on that site and you can save or open the data.
I have a row (will always be row 3) where each cell contains a day of the week, the days repeats for a year or so, making the row almost 400 cells.
Like this,
Mo - Tu - We - Th - Fr - Sa - Su - Mo - Tu - We - Th - Fr - Sa - Su - and so on...
Though, A3 doesn't have to be "Mo" because the days in this case can change (A3 can start with "Tu"), hence I think I need a macro.
So if this row contains a weekend, "Sa or "Su" I want all the cells in the column beneath that which contains a specific value to be cleared.
Example, if "Sa" or "Su" has 3 values in the columns under them, all the values that contain "X" or "Y" has to be cleared.
Like this:
Rows (1,2,3...,)
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su - .. and so on..
4 A --- B --- X --- Y --- X --- B --- Y
5 A --- B --- X --- Y --- X --- X --- X
6 A --- B --- X --- Y - --X --- Y --- C
After the macro it should be:
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su
4 A --- B --- X --- Y --- X --- B ---
5 A --- B --- X --- Y --- X --- ---
6 A --- B --- X --- Y - --X --- --- C
Notice the two examples in the excel file.
Excelforum.xlsx
Below is my initial code that can't get past the first line.
if right(cells(2,i),4) = "D_MA" then ..... ?
I'm trying to clear the contents of all data in each column that has the text conaining "D_MA" in that columns row 2. For instance, if cell F2 has 30D_MA, I want the macro to clear all contents in Column "F" -- but to do this for all columns that contain "D_MA" in row 2.
I have a report that in column BX has a large string of text (html information). Within this text there is a phrase called "| Hear = Education Website |" The phrase Education Website can be a variety of things though (Up to 9 choices). What is a formula I could use to just pull out the text after "Hear =" but before |""?
View 8 Replies View RelatedThe easiest way of explaining what I'm after is to say, I have letters of the alphabet, in their own cells, and I want to find them by way of a search. I don't mind how this is done, but it would be good if for example you entered A, C and E, any cells containing those letters changed, maybe became bold, or the cell filled with colour.
View 9 Replies View RelatedWhat is the easiest way to find the last occurrence of a value in a table using functions. I want to avoid VB if at all possible and note the row number and use it in an index function to report text adjacent to that last occurrence. I would normally use match but match only records the first match and not the last.
View 6 Replies View RelatedAny way to search for a value down column A, another value across roW 1, and tells what value lies in the cell at their meeting point. 4 example, I'm looking For "47" somewhere in column a, and also the number 53 somewhere in row 1, and I need to know what value is in the cell where those to meet.
View 1 Replies View RelatedI manage a list of web-based classes offered by my company - each class name, date, time, and attendance is recorded in the list. Periodically, we send out marketing emails for these classes to promote attendance, which is also recorded in the list.
Issue: I have a summary list of the class names (where each class is only listed once) and I'm trying to come up with a formula that will pull the class date for the last time that course was marketed.
I have attached an example of what I am trying to do - using colors instead of class names. The last date marketed for each color is already hard-keyed into the summary to show what should be there.
I have 2 cols of dates; col1 effective from, col2 effective to. In col c i have a list of corresponding rates. How can i look up the rate corresponding to a specific date. I've tried lookups and matching formulas but not working.
View 1 Replies View RelatedI have a table that has a number of new starters and corresponding appointments offered, what I originally required was to lookup the chronological date after the new start date.
However this has now been scuppered by my boss who has requested that not only lookup the date, but also add who the appointment is with, but if I do this the first array formula stops working and to tell the trust I'm not to sure how to do it anyway.
I've got a database that is sorted by date...
I need a formula that will look for 2 criteria and once it finds those 2 matching criteria, I need it to return the information on that line that's in column 6, let's say.
so in one column I have the date, the next column I have the sales persons name, 4 columns over I have their order number.
In cell a1 of worksheet 1, I have a drop down ready that has all my sales persons listed. In cell a2 I will manually enter the date that I need to reference.
I need the formula to then look for cell a1 and a2 in worksheet 2 where I have my spreadsheet with the info I mentioned above, match that criterium in worksheet 2, and return the info in column 6.
I need a code that will delete a row if it has a specific text in a specific column.
In this case in column W if there is anything that starts with '>TC' I need that entire row deleted that its in.
I am try to show a list of all rows that have a missing date in column "B" and then show the corrasponding name in the next column "C". I can find the first one on the sheet and how many have missing dates using:
View 6 Replies View RelatedSo I have a column that will have the same text in all of the cells contained within it except for one cell. Is there a formula that I can use that will automatically find and transpose the text of the one cell that is unique out of the column? I was thinking of an IF function, but the column could be up to 60 cells in length.
View 1 Replies View RelatedThis formula allows me to find the lowest value in column U where column N contains the text "NO".
{=MIN(IF($N$2:$N$10000="NO",$U$2:$U$10000))}
I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".
I have a list of numbers in column 'C' to lookup and if it is true, I need text "on schedule" to display in column 'D'. If it is false I need text "not on schedule" to diplay in column 'D'.
View 4 Replies View RelatedI have a data validation box and wish another cell to lookup the text in the validation box from a list a return a number next to it.
View 5 Replies View RelatedI want to use a formula, in another cell, to convert "Friday, 30 May 2014, 3:47:16 PM" to a value. I am using Excel 2003.
View 2 Replies View RelatedLooking for code that auto updates today's date in column C, when cells in either column a or b of the same row are edited or changed. I have never used VBA before, just browsing forums for something that might work. I think I'm close with this one, but maybe the offset is wrong - it populates the date in column b when a change.
View 2 Replies View Relatedi am trying to figure out a vba code to select the last cell in column C that contains "Cgy".
View 3 Replies View RelatedI need to find a word in a column, ie. "JAN" in col D (only first found is enough, not all instances) and then display message that "January has been updated", if not found, display message "this month has not been updated". The word "Jan" will be stored in a cell, so I need the code that could pick up the contents of that cell and lookup the col D (in another sheet, ie. "MthMaster") for that word.
I have a large excel file with the top seven rows such as you see in the attached file. In row five there is a bunch of info before the month and the year. What I want to do is get rid of that info besides the month and year. I than want to sort each column by the month and year so that all the columns are in sequential order.
demo.xlsx
i want a macro to search all of column A in multiple sheets for a date that has been put into an input box.
the matching dates have data corresponding to them in that row. i want the date and data from the row to then be copied into a new sheet.
I have a huge data set as given follow and want to Copy a the value of treatment from the cell having Treatment description at some other sheet through VBA
'UPDATE [XYZ 1213]
SET TreatmentDescription = '000'
WHERE ( ("Abc" < 00) );'
'UPDATE [XYZ 1111]
SET TreatmentDescription = '093'
WHERE ( ("wer" < 00) );'
Like in upper code I want the output sheet 2 as Cell A1 = '000' and A2 = '093' and so on
I have some specific procucts in coloum A in sheet (data), If that product was found in Sheets("allproducts"), copy all the rows and create a new sheet on the product name and save the excel.
Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error GoTo Err_Execute
'Start search in row 1
LSearchRow = 1
[Code] ....
Err_Execute:
MsgBox "An error occurred."
End Sub
I have some specific procucts in coloum A(a2, a3, a4, till a50) in sheet (data), If that product was found in Sheets("allproducts"), copy all the rows and create a new sheet on the product name and save the excel.
[Code] .....
I have a 14 column report with a dynamic range of rows. In Column A, there will only be one cell that contains the text "Added Sections:".
I need to highlight, 10 whole rows up to the 14th column, after the cell that contains "Added Sections:".
I have a table that contains various aspects of information about customer cases, and I want to replicate a user 'picking up' the case by a simple press of a button.
Users have access to only one Country, so I want to be able to search a particular column for the lowest value, but check that the Country for that row matches the user's access. If it doesn't, I then want to find the next lowest value in the column, and this is what's perplexing me??? As mentioned, I want to click a button to trigger this, and therefore want to use VBA code.
I want to delete any/all rows from a worksheet (named UK) which have the word "VAN" in column P. I have tried using a 'For Each... Next statement' in a macro, but always get a debug box "Run Time Error '91' Object variable or With block variable not set". What am I doing wrong? my code follows
Sheets("UK").Select
Dim cell As Range
For Each cell In Worksheets("UK").Range("P10:P200").Cells
Cells. Find(What:="VAN", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
Selection.EntireRow.Delete
Next