I have two sheets, first sheet contain UPDATE LIST PRODUCT
and second sheet contain OLD LIST PRODUCT.
I want to insert and replace all data in OLD LIST PRODUCT
with UPDATE LIST PRODUCT based on PART NUMBER and TYPE.
How can I replace the border style with a macro across my entire data?
I would have assumed something like this would work:
[Code] .......
I am trying to create a function that will evaluate a cell, lets call it B10, and depending on this numeric entry input data in cell ranges B14,B22.
What I am trying to do is create a list of locations for a packing slip that when I enter the corresponding location number (i.e. 200) that it will populate the shipping address in B14,B22.
Is this possible?
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."
Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.
I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008
All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'
Sub Create_formula_result()
Dim Limit As Long
Dim r As range
Set r = range("A1")
r.FormulaR1C1 = _
"=IF(RC[0]"""",(SUBSTITUTE(RC[0],""."",""/"")+0) ,"""")"
Limit = ActiveSheet.UsedRange.Rows.Count
r.AutoFill Destination:=range(r, Cells(Limit, r.Column))
End Sub
I have a spreadsheet with 2 columns (let's pretend), the first column uses Data Validation for a dropdown list. The second column contains a formula that references the 1st column and uses lookup:
=IF(A35<>0;LOOKUP(A35;NewCons;NewGroups))
So as soon as a value is selected in column A, column B changes to display a value.
Rather than just displaying this value, I would like the cell in column B to be completely replaced by this value, so goodbye to the formula.
Is there a way that I can do this? Will I need to go and study how to use macros?
I'm using a large database - excel based, with lots of VBA and macros inside - where the default decimal format is “.” (period). When I copy and paste into a normal excel workbook format I want to replace the periods with comas. The paste function I'm using is “Match Destination Formatting (M)”.
I've recorded a macro to replace all of the periods with comas - still some of the values come up as a whole number, the period not being replaced with the coma.
I've tried almost all of the formats - text, general, number .... Still does not work.
I have a workbook (materials forecasting) that automatically pulls from another workbook (production schedule).
The materials forecasting workbooks are named in regards to the current Monday (ie 02-02-09.xls, 02-09-09.xls, etc). The production schedules are named in accordance with the financial periods and weeks, ie PD1WK1.xls, PD1WK2.xls, PD1WK3.xls, PD1WK4.xls, PD2WK1.xls, etc
The materials forecasting sheet looks two weeks out.
EXAMPLE (pretend that today is the first day of PD1WK1)
Cell C1 will reference PD1WK1.xls
Cell C2 will reference PD1WK2.xls
Each week I copy the old materials forecasting sheet and rename it to the current week (ie I take 02-02-09.xls and rename it to 02-09-09.xls). I have a bunch of macros set up to move my deliveries up a week and what not. One macro I would like to set up, however, is that by putting a value in a cell the find and replace macro will work.
EXAMPLE
Cell A1 will reference PD1WK2.xls
Cell A2 will reference PD1WK3.xls
Cell C1 will reference PD1WK1.xls
Cell C2 will reference PD1WK2.xls
Then I would have the macro find all instances of whatever value is in cell C2 and replace it with whatever value is in cell A2. Then likewise fore C1 and A1 respectively. (If you think about what I am doing here... all I am doing is moving the schedule up one week so that I am forecasting using the right schedule).
I can manually do this but I would prefer a macro based on a simple user input for when people fill in for me.
I have been having a problem with the worksheet (attached) and was wondering if anyone could give me any help.
I will explain the purpose of the sheet etc first and what it does at the moment:
This work sheet is to record peoples projects, hours related to those projects, and calculate their availability 37.5-(project hours+pto) for Belfast and dublin and 40-(project hours+pto) for chicago, and also their utilization which is =((37.5-Availability)/37.5)*100 for belfast and Dublin and ((40-Availability)/40)*100 for Chicago.
When a project is added I am currently going in and updating the formula for that person to incorporate the new row which their project is on, this is proving to be quite time consuming, and I was wondering if there were any macros that could be used so that it would search for a name on the first column and use each of the hours that are in the column for the given week to calculate the availability?
The utilization is not a big problem as it just uses the availability so as long as the availability is correct then the utilization is correct..
If there is not a macro do you know of any way this can be automated so that I do not manually have to add to formula for each cell when a new row is added with a project and hours?
I have a column of about 10,000 lines which contains a description Sheet 1 Column B. On another sheet (2) I have about 200 lines of abbrevations. In coulmn A2 is the full name, and column b is the Abbrevation. I would like to create a macro that would go down the list in A2 Sheet 2, go to Sheet 1 B find that word and replace it with Sheet 2 B2 abbrevation. Is there a way to do that?
Sheet 1
A B
680385 LEAD SET WITH GRABBER CONNECTION21379 ABSORBENT CARBON DIOXIDE SODASORB 3LB
Sheet 2
A B
WORDAbbreviationABDOMINALABDOMANGIOGRAPHICANGIO
There are 1000's of XML tag with values.. i want a macro which should replace all the tags(the value which is in between angel brackets should only be removed and angel bracekst also should be removed) with or without space and tag value should remain unchanged.
View 9 Replies View RelatedI have a customer data file in which some cells have been completed with x's and I need to replace any cell that contains x's with a blank.
I can easily do this when the cell contains a single x by using find and replace within the macro, but some customers have completed fields with xx or xxx or xxxx and so on - these x's may be in upper or lower case.
I can't just replace all instances of x (or X) with blank on partial cells as there will be company names or address details that will contain an x.
What I need is a way to identify cells that contain ONLY x character(s): so "x" would be replaced with blank as would "xx", "xxx" etc., but "XYZ Limited", "LOXX & Co." or "Essex" need to be left as is.
I can't even think where to begin on this. Anyone got any bright ideas or can point me in the right direction?
I had written a macro about two years ago that replaced longer company names with common abbreviations. I store it in Personal.xls, and it has been relatively fast (2-5 sec for most projects with < 2000 rows). However, I tried it on a file with 15,000 rows, and it choked Excel - i.e. it wouldn't run and had to be force quite (Excel XP on Windows 2000).
Here is a portion of the macro (I have about 40 companies that need to be abbreviated, names here have been simplified to protect the guilty ). I stepped through a couple of these lines to see what would happen, and each line took at least 1-2 min.
Sub ChgCompNames()
Application.ScreenUpdating = False
Cells.Replace What:="Company AAAA", Replacement:="AAAA", LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="Company BBBB", Replacement:="BBBB", LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="Company CCCC", Replacement:="CCCC", LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="Company DDDD", Replacement:="DDDD", LookAt:=xlPart, SearchOrder:=xlByRows
Application.ScreenUpdating = True
End Sub
I have two worksheets in same workbook.
Sheet1 on columnA contains my data.
Sheet2 on columnA contains the entries I am going to find on Sheet1 colA
columnB contains the entries I am going to replace with
I am currently working with these code but seems I cant make it work:
[Code] .........
I need to replace part of a formula. I need to delete the workbook reference: "[Combined Flow2.xlsm]". I need to do this for range A1:Z100 and this is what I record when deleting them one at a time:
[Code] ........
How can I do this to delete it from all worksheets for the range A1:Z100 besides one at a time.
Find and replace script / macro?
I'm not sure what type of function is needed for what I'm describing below. I'm thinking it is a script or macro? I don't know anything about writing scripts or macros and I am quite new to excel.
I'm trying to learn chinese and am making chinese flashcards for myself.
The fact that some of the cells have chinese characters probably doesn't matter, but I just want to be thorough in my description....
This macro finds and replaces multiple values found on another sheet. however, it does not only take the exact value, also others.
Example:
If i want to change "Example 1", "Example 2", ... "Example 10" into "One", "two", etc. Example 10 then changes into "One 0" because it changes the Example 1 (without the 0 in 10) into One. Is there any way to alter this (or do you have another macro to replace multiple values?
Using Macro, I want to replace the height of odd rows like row 1 , row 3, row 5, etc without making any replacement to even rows.
View 4 Replies View RelatedTrying to do:
Where ever "New Orleans Saints" shows up in my sheet. I want to replace it with "NO"
Where ever "Washington Redskins" shows up in my sheet. I want to replace it with "WAS"
.
.
.
This continues on for 32 different teams.
I have a long list of items in one column, with parameters that are all split by an underscore.
However, in some instances, some of the text that is separated by an underscore needs to be joined together.
So for instance I'll have;
This_is_a_very_good_thing
This_is_a_perfect_thing
Where I want the result to be that "good_thing" becomes "good-thing" and "perfect_thing" becomes "perfect-thing".
How can I get this done with a Macro, instead of constantly doing a Vlookup to change this instances.
I'm trying to create a macro to replace a link on a formula.
The formula is as below
='[Players Report 31121999.xls]Player1'!$AK$23
Where 31121999 is the date. there's a workbook everyday with different sheets for every player
I want to have a macro that gets the date I inputed in another cell and changes the date in the formula I displayed above (just changing the date).
Here is the find / replace vba I am using.
Sub Replace()
Cells.Replace What:=Chr(19), Replacement:="-", LookAt:=xlPart
Cells.Replace What:=Chr(24), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(145), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(146), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
End Sub
It makes the replacements up to line 37, then stops. If you then delete the first 37 rows and re-run the macro, it again performs replacements, but only on the next 37 rows.
I would like the macro to do the whole sheet...
way to find/ replace some null values in a range of selected cells in a column. I would like to select a first and last cell, and replace the value -999.25 with the average of the cells that are above and below it. If there are more than one -999.25 cells, I would like to do the same thing with the average of the cells that above and below the first and last -999.25.
I would also like the same macro to replace any value that is over 200 with the average of the cells above and below it...
I want a marco that can "find & replace" in a column, let's say K. Let's just say I want to find "AAA" and replace "BBB" and "CCC" and replace "DDD", I just need the code so I can change it myself. It will be more than 40+ replacement.
View 9 Replies View RelatedThe error is a Run-Time 450 (Too many arguments or invalid property assignment), when I click debug it highlights With Selection.Find
Sub TestTemplates()
Dim s1 As String
Dim s2 As String
Dim s3 As String
Dim appWD As Object
Set appWD = CreateObject("Word.Application")
Sheets("Sheet1").Activate
s1 = ActiveSheet.Range("Name")
s2 = ActiveSheet.Range("Add1")
s3 = ActiveSheet.Range("Add2")
appWD.Visible = True
appWD.Documents.Open Filename:="C:UsersRemy LabueDocumentsCareCore WorkTest Letter TemplatesTest Letter 1.DOCX"
Call DoFindReplace(FindText:="(1)", ReplaceText:=s1)...................
I have two worksheets in two separate workbooks. One contains only two columns - a list of codes and the corresponding name of an institution. The other workbook contains a column with the codes. I want to replace the codes in the second workbook with the names from the first workbook.
I found this code on [url] here: Find & Replace macro
Sub DoIt()
Dim rRange As Range, rCell As Range
On Error Resume Next
With Workbooks("Personal.xls")
Set rRange = .Sheets(1).Range("A1", .Sheet1.Range("B65536").End(xlUp))
End With
For Each rCell In rRange.Columns(1).Cells
Selection.Replace What:=rCell, Replacement:=rCell(1, 2), _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
Next rCell
On Error Resume Next
End Sub
This is exactly what I need, but like the last person who asked about it, I can't get it to work. I've gone through Dave's instructions a number of times, and now I'm stumped.
I have created a workbook that generates new worksheets with unique identifiers as tab names. I.e. On a "Create New Sheet" worksheet, input three pieces of data, click "Create New Sheet" and this data is applied to a template sheet, macro continues running to copy the template and rename is with a unique SKU.
My problem is as follows: I have yet to figure out how to generate an Index page that is updated as each new sheet is added with the following:
a) Cell containing the SKU and a hyperlink (or some other link) to that particular tab, and
b) Beside this cell, pull the contents of certain cells from that SKU's tab (such as description, but also the contents of calculated fields on that SKU's tab like current quantity and current price).
I would like the Index to be live so that if data is changed on the SKU's tab, it flows through to the Index......
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 have a excel which contains 5 columns in which 5th column data cells has to be replaced with another set of data which have relationship with other 4 columns data.
View 3 Replies View RelatedI have list of email ids and some of which contains accented characters.I need to replace these accented characters with regular character.
Example:
rian.pieda@intt.com.cn
ario.sancez@acca.com