I am looking for code that will look for text in column A and input text into column C. for example:
if column A contains "scat" then input "scatter" into column C
if column A contains "fab" then input "Fabric" into column C
I have values in row A and I have values in row C. I want to create a loop to look up xxx and replace it with the values consecutively in row C. Look at example for a better Idea. I found a way to find and replace, but I not sure how to use it with qoutes. I was thinking maybe I dont even need row A and just supply a list and excel could have the chunk of data in the code itself.
View 2 Replies View RelatedI have over 16000 cells with different information in them, each cell is unique. There are parts of these cells that have similar information.
I am looking to search part of the cell, and replace the whole cell with my new description, so I can run pivot tables on it.
=IF(SEARCH($C$2,'705334-0707-0408'!C3)>0,ROWS($1:2),"")
Subsequent data selection also results in #VALUE...
=IF(ROWS($1:1)>COUNT($A$5:$A$1000),"",INDEX('705334-0707-0408'!B:B,SMALL($A$5:$A$1000,ROWS($1:1))+1))
In sheet one I have a list of bank transactions. I want to find all transactions matching a specific search string (in $C$2). $C$2 is compared with data in column B of sheet 705334-0707-0408.
How do I get a nul value in column A (=IF(SEARCH($C$2,'705334-0707-0408'!C3)>0,ROWS($1:2),"")). I've tried to compare the result to #VALUE, and tried NOT and a few other combinations, but can't get it to work.
I cant use the contents of a different column; there are only date, details and amount. None of date or amount influence, or are influenced by the details in column B.
How can I reduce the code to perform this task ?
View 3 Replies View RelatedI have a Master work sheet consisting sale data of more than 400 Sales person for a period of 6 Months.
The Employee Codes are entered in column "F" starting from F6, F7, F8 and so on....
G5,H5, I5, J5, K5, L5 to GV5 has the product names.
G5,H5, I5, J5, K5, L5 to GV5 has the qty of product sold by that concern sales person.
Since each sales person's occurrence is 6 times in the master sheet, I would like to have a code to search each and every sales person by their employee ID through out the entire master sheet, sum each product they sold as per the product name and get it entered in their concern cells.
I have created a file which generates a file name from information entered in certain cells
Code:
Sub SaveAsExample() On Error GoTo Err1:
Dim FName As String
Dim FPath As String
FPath = "O:PHC BomsDropbox - Stage & Gate"
FName = Sheets("Control Sheet").Range("B1").Text & " " & Sheets("Control Sheet").Range("B2").Text
ThisWorkbook.SaveAs Filename:=FPath & "" & FName
Err1:
Exit Sub
End Sub
Control sheet B1 = SG999
Control sheet B2 = Product Description
EG - SG999 Prod Description.xlsm
Now here's the problem -
Very occasionally some users may edit the Prod Description & then need to re-save
So i need to create some code which looks for an element of the current file name in a Directory (O:PHC BomsDropbox - Stage & Gate) EG "SG999" (Which could be 5 or 6 characters in length (if we go over 1000)
Delete this file, replaces it with the new file with the edited information.
I have numerous formulas that use a macro with an indirect cell reference, for example:
INDIRECT(macro(A21))
I am moving these formulas to a different workbook and I need them to still refer to items in the old sheet. I accomplish this by using this formula:
INDIRECT(CONCATENATE("OtherSheet.xls!",macro(A21)))
This works. However, I need to do a global search and replace for all of these formulas without deleting my indirect cell reference. I could replace
macro
with
CONCATENATE("OtherSheet.xls!",macro
but that would leave me short a ).
Is there a way to use a wildcard to accomplish this? It does not seem that replace
macro(*)
with
CONCATENATE("OtherSheet.xls!",macro(*))
I have a requirement... which need to search from a cel if it march then replace with below name. When user gives a selection screen, month displays in a cell A1.
For example: If this cel (A1) is jan then it should replace with Jan, if mar then replace with mar, if MAI then replace with MAY, if sep then replace with SEP, if OKT then replace with OCT.... and so on...
or
It search the cell A1 for MAI, then replace with MAY & also should search for if A1 is OKT then replace OCT, & also if A1 is DZC then replace with DEC.
I know the function of ....
[=IF(ISNUMBER(SEARCH("*MAI*",A1)),"MAY",A1)] =
This formula brings me only 1 search, but I need 3 search if match replace with respective character. this should search for OKT & DZC ALSO....
present - new
JAN - JAN
FEB - FEB
MAR - MAR
APR - APR
MAI - MAY
JUN - JUN
JUL - JUL
AUG - AUG
SEP - SEP
OKT - OCT
NOV - NOV
DZC - DEC
I have a worksheet that I produced from optical character recognition, and there are a lot of funky characters that I need to get rid of. One is a line break or carraige return (I assume it's the same character that I could insert by entering ALT+ENTER). How do I search for this special character to replace it? How do I identify what that invisible character is?
View 2 Replies View RelatedIn a cell(s) I have for example {text}dd%2BMore_Text}
what i want to do is search and replace the final }
so it should be {text}dd%2BMore_Text
the regular search and replace in excel removes all } which is not what i need.
A1 value: HARD COVER + SCREEN PROTECTOR - IPHONE 5
B1 value: WH
Want to search for the character "-" and replace it with "WH" from B1 Cell
Do this for all $A column
I have a work problem where my spreadsheets have the following structure due to faulty optical character recognition:
Ringholm 8 G R hem.ag Bavik Postl 115 O Amtervik
Rinman K H B dir Johannebergsg 34 Goteborg S
Risberg L kass Mitandersfors Bogen
In some cases the letters in the beginning of the cells have been misread as a number (above S as 8), so I want to search and replace those numbers with the correct letter without changing the correct numbers at the end of the cells to letters too. Is there any way to write a search-and-replace code in VBA that will only implement the search-and-replace in (for example) the first five letter-spaces of the cell?
I would like to create a macro to search all blank cells within a certain range in multiple worksheets to replace it with a certain blank cell. Essentially i would like to use a special values function to do this.
View 6 Replies View RelatedI have a sheet that I often have to edit and import into SQL database.
I have no control over the format of the sheet given to me and it seems that something has changed causing me the following problem.
When I try to import the Data it’s showing ten times as many items than are actually there.
The problem I think is in a text column. I’ve tested this by using an old column from a previous sheet and pasting this into the new sheet and I can import it ok.
What I seem to have is a rectangular box character but I’m not sure what it is.
I can copy and past it into Notepad as it is, but pasted into Word it seems to be a carriage Return.
So whether it’s an actual charter or not I’m not sure.
Are there any tools out there so I can hover over characters in Excel to see what it is! So at least I can be sure what I’m trying to Search and Replace?
I am looking for a formula for a cell that will do the following:
from :
robert johnson hot dog
To this
robert+johnson+hot+dog
I would like it only to change the space between the words to a + sign.
I am looping in Excel to find "NUMBER OF EMPLOYEES". IF found I want to go up one row and replace the first 4 digits with " 9ZZ". The Line 1 data will alway be different so I just want to replace the first 4 digits. I don't want it to effect the rest of the line.
Example:
Line 1 - 100 6300 BRCH TOTALS
Line 2 - NUMBER OF EMPLOYEES
RESULTS NEEDED:
Line 1 - 9ZZ 6300 BRCH TOTALS
Line 2 - NUMBE OF EMPLOYEES
I want to replace all blank cells in column A with the word "BLANK" but my problem is that I need to tell Excel to stop searching for blank cells in column A once and only once column A is equal to the last data row in the column named "Product Number". I tried this macro but, naturally, it replaces all the empty cells in the WHOLE COLUMN.
I will always have a header column in row 1. The data is below that (data will always start at row 2).
Columns("A:A").Select
Selection.Replace What:="", Replacement:="BLANK", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
How can I change this so that the find/replace macro searches all the way down equal to the last data row in the "Product Number" column?
Below are 2 examples. Example 1 is pre macro and example 2 is post macro.
...............................
I have a text such as:
fdfs&s_kwcid
sfsd&s_kwcid=dfsads
&dfsdaf&dsafdsf&s_kwcid=
fdsf&dasfsdf&s_kwcid=dsfasfsdf
I want to do is search for "&s_kwcid" or anything containing "&s_kwcid" and replace it with blank. So above would then read:
fdfs
sfsd
&dfsdaf&dsafdsf
fdsf&dasfsdf
I tried =IF(SUM(COUNTIF(E2,{"&s_kwcid*"}))=1,E2,"") but it didn't work. I tried auto filtering, and using contains &s_kwcid* but it didn't filter out results, but find &s_kwcid did find results for text anywhere in string, so I know the problem is there.
Search multiple cells for certain string and then replace the text of the entire cell with new text. For example the text may contain:
"A Chestnut Leather Satchel Binocular Case "They'll Fight Over When You're Dead" (Binocularcase-SL-CH)"
I want to search for SL-CH and once that is found I want to replace the contents of the entire cell with the following: Satchel Chestnut (SL-CH)
I'm using the code below to do a simple search & replace. The code is working fine.
My problem is after the search & replace is done, certain cells that contain numbers look weird with decimal points & plus signs similar to Format Cells - Scientific. How do I prevent these cells from looking 'weird' in my code?
I've attached a workbook with 2 sheets called Before & After. You can see the 'weird' cells in After.
Sub Macro1()
Cells.Replace What:=": ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub
I am using heavily formula based modelling workbooks and I need to know how to search for variables (inputs) in these workbooks as they contain many thousand rows and it would take ages to go through line by line looking for these variables
View 3 Replies View RelatedI import some text from a web page. The data is separated in rows and columns so it easily slip into cells in a nice row and column manner.
Now, I want to parse some names in one column (column A:A). The names do not reside in any other column. I want to change the content for those cells that is "Alpha Beta" into just "Beta", and I only want the replacing to be carried out on a specific sheet (named "Import").
I've recorded a VBA script and it look like this:
Cells.Replace What:="Alpha Beta", Replacement:="Beta", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
But, when I try different setting for the range I get the same script, I mean, for when I try the search & replace with settings for both the whole workbook and for the current sheet.
How can I control the search & replace with VBA to only be valid for a defined sheet?
I have created a desk planning spreadsheet in excel. Each week a mailmerge sends out an email to everyone in the office asking them to confirm when they will need a desk in the next week.
As the normal employee doesnt get involved in desk planning I want them simply to state whether they will be in the building or not by entering "GH" (our building) in a cell under each day in the email they get sent.
When the reply comes in we select the cells from the email and then paste them into the desk planning spreadsheet.
-----
WHAT I NEED TO DO:
Select some cells (must be in the same row - that we have just pasted in).
Press a button on the Worksheet labelled "Auto Assign Desk", which does this:-
For each cell that contains "GH", replace with the right-most cell to the left of the selection that starts "GH_"... (this is the start of a unique desk reference (GH_1_1, GH_1_2,etc.)
-----
This will assign the employee to the desk they were sat at last, when they are in the office next week.
I have two work sheets where I have data.
Sheet1 contain daily input table which as follows:
Code:
Namesalary Bonus Check
XX1000 2 FN
XY900 1 NA
YY1100 2 FN
ZY1500 3 DP
ZZ1250 2 FN
AA1050 2 NA
AZ 950 1 FN
Sheet2 have table where all the information is saved. So we can say this is database of sheet1. Which store every day information of sheet 1.
Code:
SALARY DATABASE
Name SalaryBonus
What I want to do is that ---it copy all the data which fullfile condition FN and move to the Sheet 2 which is salary database. Two important things to check is that. IF name already exists in the sheet 2 then it replace old info with the new one. Second thing is to sort the whole table(Salary database-sheet 2) according to A-Z (Name column). I use office 2003. I hope I provide all the information.
I am using excel 2007. I have a worksheet with a list of words I wish to step through this list replacing the selected words in another workbook with nothing (ie deleting them).
I have the following code
Code:
Sub replacewords()
Dim MyWord As String
Dim wbLibrary As Workbook
Dim wbWorking As Workbook
Dim myExcelColumns As Integer
Dim myExcelRows As Integer
Dim MyRow As Integer
Dim MyRange As Range
[code].....
It does not seem to evaluate "myRange";
I have written a macro to convert data from a report exported from Salesforce.com to a format that is acceptable for upload to our website. One of the necessary conversions is to convert values from a Boolean value to a Y/N value. I wrote a macro with a series of search-replace commands such as this:
Code:
'replace 0s and 1s in "Direct Billing" column with appropriate data
Columns("R:R").Select
Selection.Replace What:="0", Replacement:="N", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
This code generates a warning when no "0" values are found:
"Microsoft Office Excel cannot find any data to replace. Check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet."
I am looking for a macro to replace the values in a range of cells with a specific array for each column heading
Here is what I have
Temp1
Temp2
Temp3
0
0
0
1
1
50
[Code] ....
And here is the expected result of the macro
Temp1
Temp2
Temp3
no
false
min
[Code] ........
Please note there is only one Active Sheet.
So I was given the task to translate some procedure instructions in an Excel file from English to Spanish. These are very simple instructions and in most cases repetitive throughout the document. There are a lot of instances where the instructions are the same except for a #. They are meant to be modified within the cell as the text that appears in the cell will be printed exactly as it appears.There are several instructions in the cell, it is basically a long continuous string in the cell. My idea is to create a macro that can search that range of cells for the instruction, replace it with the Spanish equivalent while leaving the original English instruction in the cell.
View 6 Replies View RelatedI have a large spreadsheet, within which i am trying to remove commas from all cells. I get the error 'formula is too long' when I carry out the search. Some of the cells are >1024 characters in length and contain dates, text etc.
View 5 Replies View Related