I have Excel 2013. I would like to use the code below but I need it to let me select the range instead of specifying it in the code, then pop up a form into which I can fill in what I want it to search and what I need it replaced with.
[Code] ......
This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.
I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.
Perhaps an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA.
I would like to be able to replace all cell values in a range of 20c by 20r (i.e. 400 cells). In all cases the condition would be the same (find all cells with a value greater than than zero), but then replace with different values.
e.g. Cells with value >0 in range CX119:DQ138 replace with "NT", then cells with value of >0 in range DR119:EK138 replace with "NU"
I thought you could do it with find and replace by just selecting that range of cells but can't see how to set the conditional >0 bit.
In Excel 2013, how do I translate all characters of selected cells to Unicode?
There is a formula to translate the first character into Unicode [=UNICODE(text)] and that formula would have worked fine for me .Only if it could translate all the characters to Unicode not just the first one.
In Excel 2013, how do I translate all characters of selected cells to Unicode? There is a formula to translate the first character into Unicode [=UNICODE(text)] and that formula would have worked fine for me .Only if it could translate all the characters to Unicode not just the first one.
View 10 Replies View RelatedI merged about 15 adresslists from media contacts to one excel list. Each list had a name i.e. music, health, theater, etc. and the same logic in colums. I added a few columns and have 1 large list now.
As some journalists write about music & health & theater, architecture, etc. they are listed up to 10 times in the new list now. But the "genres" from the original list i.e. music, health, theater, etc. are in different columns. Some of the lines have empty fields (i.e. no address or mail)
All I want to do is have one line with all the information of all 10 lines in it, merged, dupes removed:
company - firstname - lastname - Adress - Mail, etc. : genre: music - health - theater:
example.xlsx
I atteched an example of the full list and the result i want
I just installed Office 2013 Pro. In my Office 2010 Additional Controls toolbox, the control toolbox has a Date and Time Picker(DTPicker1) . I cannot find it in the Additional Controls toolbox in Excel 2013. I certainly hope they did not choose to not make it available in Excel 2013 - this is an extremely important control for my Excel spreadsheets as it allows inputting a date range on a userform with DTPicker1 and DTPicker2 and generating reports using VBA in conjunction with Autofiltering code on a Generate Report button.
View 8 Replies View RelatedSo there's a macro "RoundedRectangle4_click" that's assigned to a box on a worksheet. It's a simple macro, I assume. it just takes the user to the 'Main Sheet' tab. I know the name b/c I rightclicked on the box to get the Macro Assigned.
I click on Macros (F8), it's not listed there, but"RoundedRectangle5_click" is, so I click on it, edit it to match the one I'm searching. Nope, nothing. CREATE button is highlighted.
I've gone to VBA (AltF11) to try to find it among the 25+ worksheets, forms & modules - haven't found it yet.
I have the below code which was working at work using Excel 2010. We have upgraded to Office 2013 and it no longer works
Error: Compile Error. Cant find Project or Library
It does highlight this : If Right(varFile, 4) = ".xls" Then
Code:
Sub PropertyName_SAVEAS()
Sheets("Menu").Select
Range("I27").Select
Dim varFile As Variant
Dim strFileNameDefault As String
On Error Resume Next
[Code] .......
I know the formula =large but my problem is the formula for the equivalent value for it. Note: In my xcel file attached you can see there are multiple items with the same total numbers and also blanks
View 3 Replies View RelatedI've got a piece of code I'm stuck on. Basically, I have a range of data and most cells are empty. However, I want to replace all the nonblank cells with an X. I can't quite figure out how to have the code replace something non-specific...
View 3 Replies View RelatedI have a big spreadsheet with IT / Finance data. Below is the formula, I currently have. I am summing column Y in Tab Savings Data Detail, where column A does not equal Parent, and The Value in Column AK on the Savings data details tab matches the value in current tab for cell B26 and where column G on the savings detail tab equals Transition to IT. I am adding that value to the second sumifs statement essentially the values in column X on the savings detail tab with the same criteria.
Now I need to add to each of these formulas. I need to sum only the values where the date value in column W on the data details tab is greater than or equal to 01/01/2013 and less than or equal to 12/31/2013
find the easiest way?
=SUMIFS('savings data details'!$Y:$Y,'savings data details'!$A:$A,"Parent",'savings data details'!$AK:$AK,$B26,'savings data details'!$G:$G,"Transition to IT")+
SUMIFS('savings data details'!$X:$X,'savings data details'!$A:$A,"Parent",'savings data details'!$AK:$AK,$B26,'savings data details'!$G:$G,"Transition to IT")
I've got a column of client company names and because it is based from accounts, the companies names appear many times. I just need to populate a new column with unique names, so I have found a formula to do this:
=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))
However when I do this, I CtrlShiftEnter and drag to copy that down, and it's just a copy of the original list - client names are appearing on my new list multiple times. I don't see how this doesn't work... I'm using 2013.
Is it possible to perform average function on subsequent group of rows and make a new set of rows ?
For example: As below, in column 1 the average of values of first 3 rows (i.e, 1, 2 & 3) is 2. similarly average of values in next 3 rows (i.e, 4,5 & 6) is 5 and so on....
Is this possible to get a new set of rows by averaging values of rows from a particular column. without applying average formula in each row of column 2. i m using MS Excel 13.
Column 1 ______ Column 2
1 _____________ 2
2 ______________ 5
3 ______________ 8
4 _____________ 11
5
6
7
8
9
10
11
12
Is there a way to find specific values and change their colors all at once in xls?
For example, if it looked like:
1 4 5 3 7
4 2 6 3 1
5 9 2 6 4
And if I tell it to change every 4 to red, it'd be like:
1 4 5 3 7
4 2 6 3 1
5 9 2 6 4
Or changing the cells' color would be ok, too.
Here is an example of what I am trying to do in E2013.
The orange Bin is my input. It want that to return the column associated with the # as show in orange output column. I think this is an index command? I cannot seem to make it work.
Also - is there an easier way to insert sheets other than a screen grab and photos?
Using Excel 2013.
I have two columns of numbers. For a row, if the right number is greater than the left number, then color it green, if it is less then color it red.
e.g.
A1 = 100 B1 = 101, then B1 gets colored green
A2 = 100 B2 = 99, then B2 gets colored red
etc.
I have clicked on conditional formatting with A2 selected, then chose A1 for values greater than and chose green. I then copied the formula for all rows. I then did the same for values less than and chose red.
It is not coloring the cells correctly. On some rows when B
I am using Excel 2013
I need to print to a printer that is not the default printer, a range of Cells e.g. B4:L28 on Sheet Print Out.
The code is to be added to a VBA routine that already exists that collects & arranges the data on the Print Out sheet from other sheets in the workbook. This routine is assigned to a button on another sheet.
I have some nominal data that I'd like to get into a pivot table (Excel 2013). For simplicity let's say it's a one-question survey with 6 respondents:
Q1: Dogs are better than Cats
Strongly Agree
Agree
Neither Agree or Disagree
Disagree
Strongly Disagree
Responses:
Agree
Strongly Agree
Strongly Agree
Strongly Agree
Agree
Strongly Agree
I can create a pivot table with this data and get the following:
Row Labels
Count of Q1
Strongly Agree
4
Agree
2
Grand Total
6
This all works nicely, however I require that the other options ("Neither Agree or Disagree", "Disagree", and "Strongly Disagree") be present, even if their values are 0, like the following:
Row Labels
Count of Q1
Strongly Agree
4
Agree
2
Neither Agree or Disagree
0
Disagree
0
Strongly Disagree
0
Grand Total
6
What I tried doing was adding a new column and calling it something like Ratings with the following:
Ratings
Strongly Agree
Agree
Neither Agree or Disagree
Disagree
Strongly Disagree
Then I set the Ratings column in the "Rows" section of the pivot table and the Count of Q1 column in the Values section. This is what happened:
Row Labels
Count of Q1
Strongly Agree
1
Agree
1
Neither Agree or Disagree
1
Disagree
1
Strongly Disagree
1
Grand Total
6
I have the following data set with about 500 rows. It has one large value in column A, followed by a number between 1 and 24 in column B.
I am trying in Excel 2013 to develop a formula that will: divide A by the value in cell BPlace in cell C, D, E, F... the result of this division, where the number of times the value is repeated depends on the value in B. E.g.
Example of source values/data set:
A
B
18504
18
2011
2
23694
24
(about 500 rows more)
An example of the output result that I am looking for:
A
B
C
D
E
2011
2
1,005.5
1,005.5
In above example, the value in column B is 2, so the result of the division of value A with value B is repeated twice. In case value B would be 18, the value would be repeated 18 times...
I have been trying to do this in Excel for more than 2 hours, but I did not manage to complete it.
I am copying fomulas from one row to the next. Then I am using the Find and Replace tool to replace some key text. This technique has worked 65 times. Now when I try to Replace All a box comes up asking me to Update Values for the text im replacing. Why is this happening?
View 4 Replies View RelatedI’ve created a form, from which I want to search and replace information into several sheets. Ex: When I select the button ‘find record’ after entering a name, I want it to search through consecutive sheets to find the name and populate all information associated with that name into the form. From there, I would like to be able to edit that particular record’s information in my form and then select another button on my form ‘update record’ to have the information automatically replaced back into wherever it finds that record’s name in the sheets.
View 9 Replies View RelatedI have a simple problem I am trying to find a set a values on a column and what to replace once found with the first 5 most left characters. Example:
in column A I can have text and numbers; the numbers that I want to find are in the following format xy:zw / xy:zw on each case the values can be from 0 to 9 so this is tons of posible combinations, I been trying to use **:** ? **:** but I don't get none. This is what I am doing
Set target = Sheets("Sheet1"). Range("A1:A9000")
For Each cell In target
If cell.Value = "**:** ? **:**" Then cell.Value = "00.00"
Next cell
A example would be 02:47 / 03:15 the next row could have 03:15 / 04:09
and so on however I could have text in some rows. In reality I would like to find and replace the cell.value with the Left 5 characters if the cell contains ANY value maching that format. Text in that column will never have that format combination
I would like to mirror a range of cells between two sheets in the same workbook, so that if the data is manually input into one of the cells in range (E5:H11) in 'Sheet 1', it is automatically updated in the corresponding cell in (H33:K39) of 'Sheet 2', and vice versa.
Using Office 2013. T
I am using windows 7 64bit sp1 and excel 2010 32bit sp1
I am trying to fix this formula (it takes a range in a unique column, and multiple row:
Code:
=IFERROR(INDEX($B$1:$B$6,SMALL((IF(LEN($B$1:$B$6),ROW(INDIRECT("1:"&ROWS($B$1:$B$6))))),ROW(A1)),1),"")
from here
I want to make it work on my array in a row, like this: (i need to remove the blank cells and the output have to be still in a row)
value1
empty cell
empty cell
value 2
value 3
empty cell
output:
value1
value2
value3
I need a function becouse I have dynamic table with something like 10k rows and 50 columns.
I have a spreadsheet that keeps track of the equipment we have on rent. Whenever we have a new rental, I insert a row and enter the information. I have several conditional formats I am applying - (1) making the font a light grey so that it is hard to see on any rental that has been called off, but invoicing isn't complete on, (2) making the font red on any item that is within 3 days of the term, and highlighting any row that isn't showing an invoice in over 45 days. When anything is completed (rental has been called off, and the final invoice has been approved), I cut the row out and put it in a sheet entitled "Closed Equipment". When I cut out closed records or add new records, it will occasionally mess up my conditional formatting by only referencing one row. Additionally, I don't want the conditional formatting to transfer over to the "Closed Equipment" tab.
Would this be easier to keep clean and straight if I used VBA? Or should I just continue to occasionally check my conditional formatting and clean it up? I thought if I used a Named Range instead of cell references, it might work better, but entering named range "On_Rent" converts to the cell range ($A$4:$AA$194).
I am using Windows 8, Excel 2013. The file is on a network drive, and others in the office can look at it (they all run Windows 7, and either Excel 2010 or 2007), but they look at it so seldom.
I am trying to replace a range of cells with certain values but I can not figure it out. I'm almost there I think but don't know how to get the varying values I'm looking for. So I have a column (AJ in this instance) that has a bunch of "xx" values at different spots within the column. I want to replace those xx values with numbers 01 through 36. What I have below gets me just about there but it replaces every xx value with 01. How do I get it to go 01 on the first one, 02 on the second one, etc?
/code
Dim cell As Range
For Each cell In Range("AJ1", Cells(Rows.Count, "AJ").End(xlUp))
If cell.Value = "xx" Then _
cell.Value = "01"
Next cell
/code
How to find and replace a range of values. I've looked online, and searched the forum with no success.
I have a list of cells in a column labled "Check #1", "Check #2", "Check #3" ect. I'm trying to build a Macro that would replace any value from "Check #1" to "Check #50,000" with "Outgoing Check". I know this can be done manually with relative ease, but it will need to be done very very very often, so I'd like to automatic it as much as possible.
How to put the steps into a Macro or Formula that could be re-produced with ease.
I need to replace A1*FX! with A1/FX! within thousands of formulas (and many variants of A1)
Unfortunately whenever I attempt this is deletes the A1 (or whatever cell is being referenced)
How to stop find replace doing this?
I have one sheet in my workbook called 'mapping' which has a list of codes on it, the length of which will vary on a periodic basis.
I have another fairly large sheet on the workbook called 'data' (around 2000 rows) that will also vary in size. I want to do a search on the data tab for each code that is contained on column 4 of the mapping tab and if the code is found, enters the corresponding value from column 1 of the mapping tab to the cell 6 columns to the left of where the code was found on the data tab (cols H and B in this case).
The issue I have is the codes maybe contained more than once on the data tab so I need the find/replace command to search the whole of the data tab and perform the task each time.