Replace Contents Of Calculated Location
Sep 9, 2006
I used to write macros in 123 and compile and sell them. Now working in VBA I feel like a pre-schooler. I'm just trying to do a simple macro that will look at a cell (say D4) and evaluate the formulat there which might result ing a new location (like G10), and put a "Y" in G10, , then, after I enter a few more bits of information, it will, upon my keystroke (like CTRL-F) do it again, enter the "Y" in the new cell, whatever D4 says now. I know that this isn't hard--but I sure can't figure out how to do it!
View 6 Replies
ADVERTISEMENT
Dec 17, 2009
I have this code that works, except, for addressing with the Goto - I need to go to the CONTENTS (new row,column) of that location which is being changed elsewhere and I can't figure it out
View 6 Replies
View Related
Oct 4, 2012
Have a slightly unique issue that I can't seem to find the specific solution for. I am wanting to filter a range, using row 1, then copy the visible cells (Excluding the title row 1) and paste into the same location, so over the data as Values.
I can do the filtering part and the selection of the visible row. Only trouble is pasting it to the same location.
this is the code so far - anything better would be great
Sub Macro2()
Sheets("GF_Scoring Database").Activate
Dim extvalue As String
Dim rng As Range
[Code]....
View 2 Replies
View Related
Nov 20, 2013
I have a macro that imports results from a website. I want the macro to continue working but every now and then when the format of the webpage changes I get the message 'Do you want to replace the contents of the destination cells?'. Is there anyway I can get my macro to not show the pop up box and to automatically choose OK (I do want to replace the contents of the destination cells) instead?
View 2 Replies
View Related
Oct 7, 2009
I am writting code that copys a range and then pastes the values back over the same range, which gets rid of the functions in the cells. I am doing this for multiple sheets. When the code pastes the copied selection back over the same range the code is interrupted by a message box that asks me wether I want to replace the contents of the destination cells or not. I do, how do I get my code to answer yes to this message box?
View 4 Replies
View Related
Apr 3, 2007
I want to have a macro that will look at a few cells say A1 & A2 and then use the contents of the cells say 1Q07 & 2Q07 respectively to do a find and replace find 1Q07 and replace it with 2Q07. I want it to use the contents of the cell and not just the text because the contents of the cell will change each time used. I tride to do it here but I dont have it yet. It doesnt seem to pass the value to the variable and the variable doesnt seem to do the find and replace.
Private Sub Update_Click()
Application. ScreenUpdating = False
Dim quarter As String
quarter = CStr(txtquarter.Text)
txtquarter.Text = CStr(quarter)
Sheets("Income Statement").Select
Dim oneq As Single
Dim twoq As Single
Dim threeq As Single
Dim fourq As Single
Dim fiveq As Single
oneq = ActiveSheet.Range("A2").Value
twoq = oneq - 1
threeq = twoq - 1
fourq = oneq - 4
fiveq = oneq - 5
View 9 Replies
View Related
Apr 9, 2008
I want to set up a sheet macro that will replace the contents of a cell (when a specific number is typed in) with a specific formula.
For instance, when "43" is typed into any cell within specified columns (say columns C and H), it changes to the following formula: =IF(D47="",43,IF(D47<=3030,408,43))(WHERE D47 WOULD REPRESENT THE CELL ADJACENT TO THE CELL WHERE "43" WAS TYPED.)
IE:
If "43" was typed into cell C1, then it would change to the formula: =IF(D1="",43,IF(D1<=3030,408,43))
If "43" was typed into cell C50, then it would change to the formula: =IF(D50="",43,IF(D50<=3030,408,43))
View 12 Replies
View Related
May 2, 2008
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.
View 7 Replies
View Related
May 2, 2012
I am creating a macro to automate data analysis for work, but I've become stuck.
I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.
View 1 Replies
View Related
Apr 28, 2014
I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.
I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?
In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.
View 5 Replies
View Related
Oct 6, 2009
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow.
simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
View 6 Replies
View Related
Feb 5, 2009
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.
View 6 Replies
View Related
Jul 2, 2013
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?
View 4 Replies
View Related
Feb 5, 2014
[Code] .....
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?
View 6 Replies
View Related
Apr 20, 2006
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.
View 3 Replies
View Related
Jun 15, 2006
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 Related
Dec 30, 2013
I have attached a workbook stating my problem.
file1.xlsx
View 10 Replies
View Related
Jan 14, 2013
I am trying to combine the contents of two cells depending on the contents of another, I have tried to use the If function but am coming up stuck!
I have provided a link to the example file below:
[URL]
View 4 Replies
View Related
Mar 18, 2009
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.
View 2 Replies
View Related
May 22, 2013
I'm wanting to use Excel's built in replace function to replace ANY date with "Call:"
I'm not going to go into details about why, but I cannot use a code, as I only want to change them at specific times.
The dates are currently formatted as 12/09/2009. So I need to change the 12/09/2009 and any other date there may be to "call:" without having to go through every possible date.
View 9 Replies
View Related
May 18, 2009
I 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.
View 9 Replies
View Related
Jun 6, 2013
What I need to calculate is:
If Quota 1 percentage => 90%, then Quota 2 percentage = sales / quota. If Quota 1 percentage < 90% then Quota 2 = sales / quota, with the maximum # being 200%.
If Quota 2 pecentage => 90%, then quota 1 percentage = sales / quota. If Quota 2 percentage < 90% then Quota 1 = sales /quota, with the maximum # being 200%
I was able to calculate this fine with one of the scenarios above, but not sure how to make it work with both at the same time.
Quota
Sales
Percentage
5000
4500
90%
Quota #1
7500
16000
213%
Quota #2
View 1 Replies
View Related
Jul 6, 2008
I have a pivot table with a calculated item. The problem is that it shows data for all rows EVEN when there is no data there. So the pivot table is a lot longer that it needs to be. How do I get the pivot table to show only rows with data in it? Also, all of the fields DO NOT have checks beside "Show Items With No Data".
View 9 Replies
View Related
Sep 19, 2007
I've written a custom function that calculates commissions (as a %) based on three variables.
I wanted to know what code I can use to cap the amount of commission paid out to a specific % amount. Something like "as soon as X exceeds 3 times the amount of Y, payout 3 times Y as the maximum".
View 4 Replies
View Related
Jul 21, 2012
I am trying to write a VBA macro that will mimic what i have already done in an excel worksheet i have made. The excel worksheet uses inputs for a heat exchanger and finds the temperature as it leaves the heat exchanger. This temperature is then the new input for the second line of the worksheet and it changes quite a few of the values in the sheet. I have the excel worksheet working where you just drage the row down and it does it automaticly, but i would like to have a VBA macro that all the values can be input in with the number of cycles until the heat exchanger equalizes out.
I am able to get the macro to work when it calculates just the first cycle, all the equations work my variables are ok for just one time. What i don't know how to do is make my program realize that it has to use the calculated temperature out of the heat exchanger as my new input for the heat exchanger. I know how to do it once so it is more of a syntax or understanding of a loop or an array for the values that are changing.
View 4 Replies
View Related
Nov 14, 2013
I have a column in powerpivot that has Products total amounts. Some of them are negative amounts but I want them to be positive. How would I write an IF statement for it? I want it to say " If Division amount column is a negative, then multiply by -1 but it is a positive , leave it as a positive" .
View 2 Replies
View Related
Jun 21, 2014
Take a look at the attached spreadsheet: Value_Lookup.xlsx
We have a pivot table with the "Product" on the Row context and a date ("FullDate") selected on the filter context.
The FactTable is related to the Calendar table by "FullDate" (which is marked as Date Column in Powerpivot)
In the Calendar Table, next to "FullDate" we have 2 more dates in the same row: "1 Month Back" and "1 Quarter Back".
We just need 2 calculated fields that return the "Price" value from the selected "Product" on the Calendar dates "1 Month Back" and "1 Quarter Back".
View 8 Replies
View Related
Aug 20, 2008
It is a few years since I worked in the IT industry and used EXCEL a lot.
I want to use (formula) SUM on a cell which 'sums' 3 other cells one of which has a the result of a formula but the result of that SUM is always 0 (zero). I am sure I can remember doing this in the past but cannot determine how it is done.
eg.
Cell Contents
C10 1000 (value)
C11 =A1*(A10/A20) (formula)
C12 2000 (value)
C13 =sum(C10:C12)
What do I need to do so that (cell) C13 results in the correct value instead of 0 (zero)
View 5 Replies
View Related
Sep 16, 2008
I am entering data for a month on one worksheet. The next month I change the date and reenter new data. I want another worksheet to record the data on a month to month basis, one month in one column, the other month in another column, to have year to date. Is there anyway to do this. Currently, I use an if statment =If(a1="January",b1), but I need to change these to values before I do the next month, and alter the formula to reflect the new month name)
View 11 Replies
View Related
Jan 7, 2010
if its possible to place a value (the product of a formula) into a cell ie:
Cell A1 = "a"
Cell A2 = "b"
I would like to be able to say: If A1="a" and A2="b", then place "Yes" into D1.
I realize that =if(and(a1="a",a2="b"),"Yes",0)
placed in D1 would give the same result, but I do not want to have a formula in D1, I simply want the characters "Yes" placed in there.
View 3 Replies
View Related
