I've got a problem with my workbook.
It gets datas from another workbooks and contains cells with formulas like these:
='C:YEAR[2006-ab.xls]'!A12
='C:YEAR[2006-bc.xls]'!C18
et.c.
Is it possible to write a code in VBA to replace a part of formulas above?
I want to replace a part of a formula with its value.
eg say I have a formula =vlookup($a55,$a$2:$e$50,column(),false).
I want to replace column() with its value. There will be plenty of cells with this formula What can be the best way of achieving this.
I am trying to modify a cell formula with the Replace function.
Code:
The Formula in the target cell =
='C:Documents and SettingsHoliday macro[Holiday2011.xls]Year 2011'!C5
I am using the below code but it returns just the end portion ='Year 2012'!C5
where I expect just the year to change
='C:Documents and SettingsHoliday macro[Holiday2012.xls]Year 2012'!C5
Where last year = 2011
Yr=2012
Range("C3").Formula = Replace(Range("C3").Formula, LastYr, Yr)
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).
I have a formula in cell J2:
='H:Folder[CI10001G.xlsm]Sheet'!$C$5
I need a macro that will change the part of the formula that is "CI10001G" to the value of cell A2 which would basically be something along the lines of "CI10004D" so the formula would change to the following:
='H:Folder[CI10004D.xlsm]Sheet'!$C$5
And then this formula will then successfully look in the newly directly file path.
Here is the code I'm working on:
I want this code to replace the value of s with the value of a cell.
sub Macro2()
Dim s As String
s= value of (cell F2)
Range("I8").Select
ActiveCell.FormulaR1C1 = "='[(s).xlsm]Payroll Computation '!R8C11"
End Sub
I'm looking for a formula or criteria within a formula to prevent a cell from recalculating or just causing it to revert to a value if another cell becomes populated. Something like =IF(NOT(ISBLANK(xx)),STOPCALC(xx),...
I'm trying to determine some weighted week to week projections and I want to be able to determine the accuracy of the projections without them being influenced by the actuals once they show up (the projections use historical actuals and related data which are all in the same range where the futures will come in). Also hoping for a shortcut to do what I described without having to manually go in and stop the (thousands of) cells from recalculating since the actuals for a given periodicity arrive staggered and not at one specific time.
I'm trying to replace text in formulas with text that is gathered from a macro. For example:
Cells.Replace What:="RangeX", Replacement:=Data1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Where RangeX is the text currently in the formula, and Data1 is a string that references a range in another workbook. Data1 is defined before the replace function.
I am working with a lot of data and have been making a table of statistical analysis for several rock types. I have it worked out to where i can filter the data and with the use of a bunch of IF functions, below, and it keeps everything the way I want.
{=MAX(IF($H$2:$H$22="sedimentary rocks",$W$2:$W$22))}
The problem is I have to introduce new rock types, but if I just copy the formulas over i then have to go through hundreds of cells and switch sedimentary rocks to volcanic or whatever new class i have.
How this can be done faster. Maybe some way to say "find "text" within this range and replace" i can do it for actual values but when its embedded in a function im not sure.
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?
As part of a macro i do a find and replace on a range cells that replaces "/1" with nothing (""). Therefore 2/1 becomes 2, what i don't want though is if the de-nominator includes a 1 but not only 1 to keep the whole of the de-nominator, say 8/11 does not want to become 8/1.
View 4 Replies View RelatedI am wanting to replace part of a cell ( the first two digits) with nothing - blank
eg cell contains 441298871657 and i want it to read 1298871657
I am looking for a function to do this, i have tried using the finf and replace function but if 44 appears somewhere else in the value this will also be replaced.
I'm trying to 'find and replace' part of a filepath which is buried in hundreds of formula, but when I hit 'Replace All' a file navigation window appears. Hitting cancel simply bring up another window, and again and again, each time a replace is executed.
View 3 Replies View RelatedI would like to use some vba code to search range T3 to U500
and search for the word "all out" which will appear within the text of some cells - in the format :-
64 all out(36 overs)
and replace it to
64 all out(50 overs)
the two digit total at the beginning of the line can be 1-3 digits and the number of overs can only be 1 or 2.
Is there a formula where it will find all that reads 07-06 and be able to change it to 08-06? Below are samples of formulas where I am looking to change 07-06 to 08-06 without having to go into each cell and manually changing 07-06 to 08-06:
= SUM(IF('07-06 data'!$C$2:$C$103="CAM",IF('07-06 data'!$I$2:$I$103="INCOMING - NEW",1,0)))
=SUM(IF('07-06 data'!$C$2:$C$103="MSP",IF('07-06 data'!$I$2:$I$103="INCOMING - NEW",1,0)))
=SUM(IF(('07-06 data'!$C$2:$C$103="CAM")*('07-06 data'!$I$2:$I$103="INCOMING - NEW"),'07-06 data'!$J$2:$J$103))
I have a worksheet - sheet 1 like below:
00001 - $2000
00002 - $3150
00004 - $2010
00001 - $1011
I would like to look up customer number (such as 00001) and replace to Peter
sheet2 like:
00001 Peter
00002 Marry
00003 John
00004 Mac
Expect Output:
Peter - $2000
Marry - $3150
Mac - $2010
Peter - $1011
Can you cut out part of a number and replace it in another cell. For example
A1 come in as ###+### and it will not always be a three digts all the time but I would like to keep the number in front of the "+" in A1 and the number behind the "+" into A2 cell.
Can you do this my a SEARCH("+",A1)LEFT("+)
I'm writing something similar to an auditing tool. I get a list of files from a pre determined directory. All these files are then listed on the spreadsheet. However, there is one file which has a different layout to the 88 other ones. I've written 2 subs (NonPerthLocation for the 88 stores, and PerthLocation for the 1 different sheet.) I'm trying to write a loop like the one below, however it skips the first sub and automatically calls the PerthLocation sub. In short, basically if the file has Loc 089 in any part of the filename, I want it to run the PerthLocation sub.
View 3 Replies View RelatedIs there away to replace text automatically in excell from a list. For example:
I want a list stating..
Hotels in ...
Acomadation in ...
Stay in...
Followed by:
England
US
Germany
Is there a way of doing this automatically without having to copy and paste the first list 3 times then find and replacing indivually for England, the US and Germany?
I am trying to find a way to automatically find and replace 2 letter symbols in a specific column into the words they represent. There are many symbols and rather than do a find and replace for each symbol, I would like to write a macro that would do them all at once.
I have a Table with MB and GB numbers.
I copy and Paste these into Excel, What I need, is for Excel to Automatically convert the MB into GB by /1024
I tried something like this
=REPLACE(A1,FIND("MB",A1),9,"(But can I enter a Maths here)")
I need to find the "x" in range C2:C44 and replace it with a value (variable) specified in cell I2
The catch is that I then need to be ablt to change that character when I2 changes.
Coloum C
*VER
*T’x’STATUS
*ON
*OFF
*T’x’BANDS
EG
I2 - Variable = B
*T’x’STATUS will become *T’B’STATUS
I2 - Variable = A
*T’x’STATUS will become *T’A’STATUS
But " *OFF " should not change.
I can change *T’x’STATUS to *T’B’STATUS but then I can change *T’B’STATUS to *T’A’STATUS
Please see attached example file as it shows it better than I can explain it.
Structure:
I have a worksheet with the cells in one of the columns having html tags inside of each cell.
The tags vary by location within the cell and there are anywhere from 0-10 tags per cell.
Column A is the text string. Inside these cells are the tags. The tags are of form
<A href="/mortgagecenter/article_alias">
The
<A href="/mortgagecenter/
and
">
parts are true for every instance of this string....
....article_alias is a variable per each string. I have columns B and C which are the aricle_alias names and article_ID's, respectively.
What I am trying to do:
From the [quote]<A href="/mortgagecenter/article_alias">[quote]
I would need to replace the
article_alias
portion with
ArticleID&"-"&article_alias
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1
I need to reference the formula in D1 with the cells headers names.
In a perfect world, it would take
=A1+B1+C1:
and produce:
=Bananas+Apples+Oranges
calculate a formula for the following: 1 per 25 for the first 50 and 1 per 50 for the remainder exceeding 50 ad infinitum (always rounding up).
View 6 Replies View RelatedI am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.
i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6
this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads
I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire
I am trying to create my household budget in Excel, but there is a sub-category.
I need a formula which will calculate the following:
Under the "Daily living" section, I have typed the names of the sub-categories, eg: ,
Groceries
General Household
Cleaning
Education/Lessons
Cash
I want it to find all totals next to the word "groceries" in the main part of the spreadsheet, where I type in my credit card statements according to date.
In other words, I want it to add up all my entries/totals next to the word 'groceries' and show the total up at the top of the document, in the "amount" column so I can see the monthly category totals at a glance.
I have a dump which looks like below sample:
3
3
2
2
3
3
2
2
[code]....
The actual dump will have around 200 - 500 rows of data.
From the above I need to manually group them which are similar.
E.g. the data of (3, 3, 2 , 2) in row 1 & 2 are matching, so they will be group 1 & row 3 will be group 2, row 4 will be group 3 respectively.
The sum & sort did not work for me as sometimes the data with the same range are not in same order.
E.g of the output below for the above data:
Group No.
1
3
3
2
2
1
3
3
2
2
[code]....
I have the following formula in my workbook;
=SUMPRODUCT((A2:A8702"")/COUNTIF(A2:A8702,A2:A8702&""))
The data in the workbook is imported automatically and the range 'A8702' is different every time the data is imported, although is always in column A.
Can I use the value of a cell to determine the range?
So I have used this to get the ast used row number;
=MAX(ROW(A:A)*(A:A""))
I need a cell formula (gets put in by macro) that returns part of a workbook name.
The worksheet name will always be different, but will contain either
SD1, SD4, BDO, BD (not neccessarily in Caps)
and it is this key code that i want returned in the cell (G2).
so if the worksheet name is
NW SD1 bobs your uncle. when macro runs G2 returns SD1
or
BDO xyz. when macro runs G2 returns BDO