I need to find, then replace a particular value ("/0"), however, some cells may be hidden. And I just discovered that I can not replace the value of a hidden cell! Am I missing something, or do I have to unhide the row/column that the cell is in and then replace it? ...this does not replace the "/0" with "0" if the row the cell is in is hidden.
Sub test()
Dim rMatch
Set rMatch = ActiveSheet.Cells.Find(What:="/0", LookIn:=xlValues, LookAt:=xlWhole)
rMatch.Value = "0"
Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
I'm using the Find function in VBA on a column of data, but the range is being set to Nothing if the data item I'm looking for happens to be in a row that is hidden at the time. How do I set the find to look in all rows, hidden or not? Can this be done without unhiding all the rows first?
I have a spreadsheet with various hidden rows and columns that I do not wish the user to have access too as it contains too much detail for their purposes.
Copy/paste and copy/paste special also copies across the hidden columns and rows so what I need to do is to copy it to a new workbook but then remove the hidden rows and columns and just delete the existing formulas in the cells which total the data and insert a new autosum.
I have mapped data from Sheet1 to Sheet2 and created graphs in Sheet2 using the data. but when I hide the rows in Sheet2 the Graph is being cleared off. Need a solution so as to display the Graph in Sheet2 while hiding the rows in the same sheet.
I have a worksheet that is shared. It has hidden rows and columns. Problem is those hidden rows and columns are unhidden when someone apart from me opens the worksheet! Only way round it is to have the worksheet unshared. How do I keep the rows and columns hidden?
One of the fields I want them to enter is a library branch and I have used a data vaslidation list to provide a dropdown box.
To make it act more like a windows drop down box I have hidden 7 cells above this box with the 7 possible entries. This means that with autocomplete, if they start typing in the cell it will autocomplete the entry for them.
This work fine, but if someone presses enter from the cell above my hidden rows, one of the hiddenn cells is selected! WHY!
I have no explanation for this behaviour, nothing like it has ever happened before, and I have no idea how to get rid of it.
Here is the problem: A1 contains : current model numbers, B and C contain new model numbers and corresponding old part numbers. Based on Column B and C I need it to go through and find all the old part numbers in column A and reaplce it with the new part numbers: Here is my table:
Current # NEW AFS# current # 10773650204020000216802606 10773652204022000116035305 10773653204023000115744805 10773654204023000215744806 10773657204023001215744881 10773658204023022715744844 10773659204024000115836205
For example, I need it to find all instances of "10773650" (first data in column A) and look in column C to find the corresponding number if it exists, if it does exist I need it to be replaced by the corresponding data in column B
I work at a club. I've got a spreadsheet sent to me from another club we do business with. On it are charges from their club to ours that list the charge and associated member number.
I'm trying to use the text to columns feature to separate the member number from the other data in the column so I can sort by member number making it easier for us to bill our members.
Here is a sample of the data:
"Beach Club Bar,b500" "Beach Club Bar,s200" "Beach Club Bar,a150"
When I try to use text to columns what happens using the comma as a deliminator, everything including and after the comma disappears.
Thinking that maybe I needed a space between the comma and the member number (in the first line b500 is the member number) I tried to use find and replace to replace , with , and a space after it. This also resulted in all of the data including and after the comma disappearing. I'm sure I'm missing something here.
I tried copying the data into a separate spreadsheet. Tried changing it to text or general.
I typed in the exact data as you see it and tried the text to column function and it worked perfectly. What could be hidden in the data in that column that's causing this?
I have been put together some simple coding to find and replace a value (1) that i have obtained from a pivot table. My aim was to replace any "1" with the column header. The reason for this is that the cell(s) will be used in a VLookup at a late stage.
My problem is that I have the code to work for a single cell, but I want it to work for multiple columns so I don not have to create multiple macros.
I need to go from Column C to AW. I have a total row at the end - hence why I am using 'Step - 1'.
Code: Sub FindReplace() Dim i, lRow Application.Calculation = xlCalculationManual
I am writing a macro that will find several different values in sheet1 and replace them with values from a table in sheet2. It currently does exactly that but I realized that there are instances when replacing hyphens isn't appropriate and will ruin the upload of my data. I need to replace it in the rest of the columns though. The headers will always be the same, such as "MST" , so I could use that as a conditional statement I just don't know how to go about it with the replace function.
Code: Sub FindAndReplace() Dim LastRow As Integer Dim i As Integer 'Counter to loop through all the rows found in the table
'Finds the last row used in my replacement table and sets that as the searchable range
I need to average the columns of data and ignore both hidden rows and zero values. I have tried writing if statements as well as the subtotal function. Both functions either ignore null values or hidden rows but not both.
The system wont let me update a sample workbook but Im wondering whether there is a formula or combo formula for this.
I am trying to develop a compliance report with Excel 2007) based upon a simple pass/fail criteria. The subtotals must be tracked both by Device (column) and by Requirement (row). There are macros (not included in the attached sample) that hides both columns and rows. I successfully found an example which I modified to correctly calculates data for a column when rows are hidden (see GOOD function below). However, I'm totally clueless on how to calculate data by row when columns are hidden (see BAD function below). What I'm trying to figure out is highlighted in red in the attached spreadsheet.
GOOD =SUMPRODUCT(SUBTOTAL(103,OFFSET(B$2:B$5,ROW(B$2:B$5)-MIN(ROW(B$2:B$5)),,1))*(B$2:B$5="Pass"))
BAD (returns 0 and includes a circular reference) =SUMPRODUCT(SUBTOTAL(103,OFFSET($B2:$F2,,COLUMN($B2:$F2)-MIN(COLUMN($B2:$F2)),1))*($B2:$F2="Pass"))
I have a feeling the answer to this will be 'no' based on the searching I've done, but is it possible to allow formatting of rows/columns on a protected worksheet, yet prevent the user from unhiding some rows/columns I don't want them to see? I've allowed them to format rows/columns so they can "size" them to fit the text they enter, but that has opened up the ability for them to unhide columns. Using Shrink To Fit would be cumbersome due to the number of cells involved.
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 recently became owner of a spreadsheet with some issues, and I am trying to make it useful. Each row has a URL of a blog post, and I want to extract the date from it (which is present in each URL) while getting rid of the rest of the URL. I was able to get rid of everything up to the year (which comes first), but then the URL continues, for example, 2013/05/16/the-rest-of-the-url/ and I would like to just have 2013/05/16 remain.
I am trying to use find and replace with the find box reading 2013/??/??/*/ and replacing it with 2013/??/?? which effectively erases everything else in the url, but leaves ?? instead of the numbers. Is there any way to have it so that it keeps whatever was in the original box?
Trying to repeat a 550 or so character statement with a find/replace however I am getting type mismatch errors. When I use a smaller message in the "replace" it works.
I need it to post a message exactly as long as what I have in there. How do I get it to work?
Need assistance with the code for catching errors when using the find / replace function in excel? In particular, I am trying to write code to break to an error message when the value or string searched for isn't found in the find / replace. At the minute I have just copied the standard code using a macro and all this does is return a message box saying X entries replaced.
I am trying to create a macro where it finds a a certain word in a column for example C. What i want it to do is find anything that says FWD_EUR and then replace that cell (e.g C2) with CASH_EUR_FWD and after it has done that it replaces the adjacent cell (e.g. D2) with EUR_FWD. I then want this to do the same with FWD_USD to CASH_USD_FWD and adjacent cell to USD_FWD.
Range B3:B1000 is text strings. Column C2:C50 is a list of words that I would like to "Find" in Column B and replace with it's lowercase values unless they start off the string.
Example
Find all occurences of And or AND and replace with and Find all occurences of With or WITH and replace with with. Find all occurences of Or or OR and replace with or
I'm trying to come up with a formula to find the last row without any data in columns E and F and delete the 10 rows below them. Is there a way to do this?
I have a workbook in which I have two sheets. One sheet is a report and the other is a data dump. The data dump has headers in in column A starting in cell A6 and headers in row 5 starting in cell B5. There is then data going from B6:J20.
In my report I then I have same setup with headers in column A and row 5. The difference is that the headers are not in the same order as the dump. What formula could I use that would look for the two headers in my report sheet and then match it with the value in the data dump that uses the same two headers?
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23 Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
I have data in B4:B55 and need a formula to return a count of rows, including rows that are blank. However, there are hidden rows that need to be omitted from the count.
I have a macro that converts all my PDF Purchase Orders to a text file and inserts the data/text horizontally into another document. However because the PDF's or the text within the PDF can be fomatted differently (that is on different lines etc) it therefore imports the information and it looks mis-aligned.I have attached a simple spreadsheet showing some sample text as it is imported and then below this how it should look like, all in line.
The range where the highlighted text in red is, is variable (but say nothing more than a variant of 10 columns). The text can also be Uppercase or Lowercase.So, I was wondering if there is macro code to find the "text" on various rows/columns and align it all in another column?
