I have an Excel file with over 5000 rows all containing URLs
I only need the URLs containing a particular word in ther such as
if the URL contains ".edu" leave it, and if not, delete the data.
Is there a way to do this by VBA?
I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things
1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.
2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.
I Use the following Macro to import data from a .txt(As attached) into excel. This results in the also attached Excel sheet. I am almost happy with it, but I don't want the Return_number4501856 Totals row in there. As a matter of fact I don't want any break between the different return numbers. Does anyone know how to add this to the Macro?
View 2 Replies View RelatedDue to work, I need to creat the captioned file for our company.
View 2 Replies View RelatedI am trying to create a macro that sorts rows based on value of 2 columns, but every time that a value changes in either of them to leave an empty row between them. Is that possible? In simple words i want to separate my rows with the empty row while sorting them out alphabetically based on two columns.
Please check my screen shots. Test 1 is my spreadsheet before the macro. Test 2 is how i need it to be after the macro. The two columns that i want it sorted out is First GDS then Rate.
What i want to do is clear some data within a merged cell but leave the rest.
I have added a sample workbook. The data i want to clear is highlighted in Red so the 11111 will be replaced with ...... and the 09/12/09 will also be replaced with .......
I have a multi tabbed page and the page i want to sort (2) is looking up the 1st page and creating one column list from cell a1 to a280 and it includes a number of zero's from the look up pages because that is the correct result. I already have it sorting but I want it to sort and leave the zeros at the bottom (they are currently at the top) of the sheet so I can just print the info I need.
Can I create this in sort or do I need a macro and how can i write it as I am very green on VB.
what i want to do is use 2 sets of data ss is attached to help understand
i would like to compare hometeam v player
therefore any games that steven gerrard didnt play in ( in the example )
it would leave all the games on sheet 3 where he wasnt involved.
hometeam has all the games liverpool involved in
player has all the games steven gerrard involved in
cross reference the 2 to leave all the games he wasnt involved in on a new tab
must be possible just not sure if i need look up or a macro
I have used the snippet of code below many times to delete rows of filtered data. For example, if I want to delete all rows containing "Y" in column F, this code would filter to that criteria, offset it to save the column headings, resize it to avoid deleting the row below the range, then delete the rows. This works beautifully, until I ran into an instance where there were NO "Y"'s in column F. Then it deleted the column headings.
How would I modify the code below to leave the headings in row 1, but delete any other filtered data? (Also, if there's a better way to delete filtered rows but leave headings, I would be interested.)
I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this: =INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
I have 2 similar question.
I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this:
=INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
A1 has a date of 03/10/07
B2 has formula =A1 + 7
I want to copy that formula down the column B2 but if
B3 is empty I get 01/07/1900
How Do I make so that if there's no date entered in
A column I get blank cells but still retain formulas,
I've been running into this issue with other formulas but I couldn't find any solution searching this forum,
I have a lot of rows of data in this date and time format "2/29/2013 10:00:25 AM" and I would like to remove only the time and leave the date
View 2 Replies View RelatedWhen i use
Code:
=IF(ISBLANK(I31);"";I31-IF(ISNUMBER(J31);J31;0))
The cell gives me an error, i think because I31 has a formula that returns no value.
How can i stop the formula if cell I31 gives me no data??
I'm trying to create a worksheet to automatically make a football league table update once results are entered. I have entered a formula using the "IF" function which compares scores and then awards points. However before a game is played and the cells are left blank, the formula detects this as a "draw" and awards a point. how to prevent the formula operating until a result is entered?
View 7 Replies View RelatedI have a leave history report with data for leave taken for the 2014 year. The sheet contains the following headings:
Name; Leave Start Date; Leave End Date; Date Paid; Days Taken; Roundup; Offset
The roundup column rounds the days taken up, for example if someone took 0.4 days off it would round it to 1.The offset column just subtracts the Roundup value by 1 (=Roundup-1) I think we will need this for what I am trying to do.
I have created a new spreadsheet labelled gaant chart, this shows the name of the employee with the working dates for the year and has the following data:
Name
1/01/2014
2/01/2014
3/01/2014
6/01/2014
7/01/2014
8/01/2014
9/01/2014
10/01/2014
[code].....
What I would like for the macro to do is lookup Employee 1 from my gaant chart table and search the leave history data if it finds a relevant start date for the employee it marks that date with a 1, it then looks in the offset column and marks the number of offsets to the right. For example if employee 2 took leave from 1/1/14 to 3/1/14 the macro would mark a 1 on the 1/1/14, and search the offset column which would have 2, it will then mark a 1 in the 2 cells to the right of 1/1/14 also (these being 2/1/14 and 3/1/14)
I am wanting to have certain cells of data from one worksheet appear on another worksheet once the cells on the first sheet have data entered in them.
I find that I can do a simple formula to copy the cell but I want the cell to remain blank until data is added in the first sheet (it will be a mix of text and numerical entries).
I am making a spreadsheet for use by my customers. Is there a way to leave cells that have formulas' in blank until the cells that make up the formula have entries in?
View 5 Replies View Relatedneed to create a macro that inserts 3 rows below each existing row of data and simply copies and pastes that data into each of the empty rows before moving on to the next unique row and doing the same thing again.
This is what I have so far, but I can't seem to get the loop right.
Rows("2:2").Select
Do
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
activecell.Offset(-1, 0).Select
Range(activecell, activecell.Offset(0, 5)).Copy
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
Selection.Offset(1, 0).EntireRow.Select
Loop
Is there a way to do a left formula where you can have it pull the whole cell except the last character? Basically, I have a sentence that looks like this:
apples, oranges, pears,
And I want to drop that last comma. But the length of the each cell varies from row to row.
the below code adds a new row with data to the last blank row on the sheet how do I change it so that it leave the last row and inserts above it.
the last row is a sum of the columns ....
I have a 'Total' Cell (I23) that has the following Formula: =IF(SUM(D23:H23)>0,SUM(D23:H23),"")
But I would only like the formula to 'work' if another "Total" cell (I57) further down the sheet is Blank.Therefore if Cell I57 has a value, the Cell I23 is left Blank.
is it possible to have some sort of form that stays on top and is showing a total price as the user is working? It would have to move down the screen as the user moves down also. I dont have the workbook started, just needed to know if its possible and if so, of course, how to do it!
View 2 Replies View RelatedI'm looking to pull into Column B the last name only from the "full name" column (column C).
Right now, in Column B I have the formula =UPPER(MID(C3,FIND(" ",C3)+1,50)) to pull in the last name from the full name that is in column C. The problem is that when column C has a middle initial or a nickname, column B ends up having the middle initial and the last name. For example, if Column C has John "Buster" Jones, I get "Buster" Jones in column B but I only want Jones; or if Column C has Mary T. Smith, I get T. Smith in Column B but I only want SMITH
Is there a better approach so I only get the last name?
I'm want this formula, =IF($C30="Y",$A29+1,""), to leave cell A29 blank if cell C30 doesn't have a "Y" in it. If cell C30 does have a "Y" in it then I want to add one day to cell A29. Cell A30 should return 28-Feb, but in stead it returns 0-Jan.
************************************************************************>Microsoft Excel - 2007log.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA26B26A27B27A28B28A29B29A30B30C30A31B31C31A32B32C32A33B33C33=
ABCD2624-FebSat**2725-FebSun**2826-FebMon**2927-FebTue**300-JanWedY*310-JanThuN*320-JanFriN*330-JanSatN*Feb*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
this must be very simple but I dont see it.
= IF(A2 = 50;F2;A2)
this gives me circular reference. All I want is if the value is = 50 to change the cell value, otherwise to leave the old value intact, as it is.
If I write something like this:
= IF(A2 = 50;F2)
the cell is assigned FALSE, and the old value is overwritten.
How can I avoid setting to the FALSE value, without having the ciruclar reference error ?
I am writing a very simple Do While loop where VBA searches for the first nonempty cell in a column starting from cell C3. The error I get is "Overflow". I suspect that when all cells are empty it goes down the column till the last cell, which is around 65,000. How can I put a restriction on this loop? I need the search to be restricted to range C3:C95, because my data ends in row 95. In other words, the code essentially should do this: go from cell C3 to cell C95 and search for the first nonempy cell in this range. When you find the first nonempty cell, assign name "x" to this first nonempty cell.
View 6 Replies View RelatedOn the attached worksheet if the data in col F changes from FLT to AWF,col M will flag up "faw".I would like to leave a blank cell instead of false if the condition is not met. Col P is an example of the result im trying to achieve.
IF TEST.xlsx
How is it possible to leave a cell in a set color, if nothing is entered in the cell? And is it possible to merge it with it with existing formulas allready in the cell?
View 4 Replies View RelatedI have cells B5:AL22 I want to put 1, 0, or leave blank. I need the zero (0) to turn red with white letter, the 1 and blank cells to remain in the present format. I can get the 1 and zero (0), but I don't know how to get the blank cells to stay in regular format.
View 3 Replies View Related
