Hi i had created a drop downlist (Data Validation list) which consist of the city names and a masterlist of the respective City id to the city names on a different work sheet (same file).
But i would need to configure in such a way that when i select the city from the drop downlist it would appear as number ( City ID) on the excel file.
I'm trying to use the Worksheet_Change event to replace all cleared cells with a default formula. This allows users to override the default, or restore the default when they need to:
If Not Intersect(Target, Range("Data")) Is Nothing And Target.Value = "" Then
Range("DefaultFormula").Copy
ActiveSheet.Paste
ActiveSheet.Application.CutCopyMode = False
End If
Here are the results with various different regions selected:
Single cell selected and cleared -- works. Multiple cells selected and cleared -- doesn't work if the first area selected contains more than one cell. Otherwise, it works, no matter how many other areas are selected.
Example1: Selected range is (D2:E2, G2) in that order, it does not copy the formula into the cleared cells. Example2: Selected range is (G2, D2:E2) in that order, the code does copy the formula into all the cleared cells as desired.
The "IF" condition is not TRUE when the initial area has more than one cell.
I have not located anywhere an answer how to use in vba dropdown list, when you choose a cell it displays in the TextBox the cell right to it? How is it done by using VBA? Here's a picture about what I am trying to accomplish:
View 6 Replies View Relatedi want to include the dropdownlist to VLOOKUP formula. See the attachment
View 2 Replies View Relatedhow I can have the selected item in a dropdownlist also to be shown in another cell? Typing the cell number of the dropdownlist in another cell (f.e. ="dropdownlist cell number)" doesn't seem to do it.
View 2 Replies View RelatedI am using VBA to create a word document (.docx). This word document contains plain text content controls as well as picture content controls. I then use VBA to automatically select a picture based on the code below
Code:
Set oCC = Word.ActiveDocument.SelectContentControlsByTitle("TabPic").Item(1)
On Error GoTo TabErrorHandler
oCC.Range.InlineShapes.AddPicture Filename:="X:XFERANDREW-TDCD " & LblVL &
[Code].....
After the document has been closed down I try to open it again and I am told "The file cannot be opened because there are problems with the contents."
When I click details it says "Unspecified error" and "Location: Part: /word/document.xml, Line: 2, Column: 0"
If I click ok it says "Word found unreadable content in "". Do you want to recover the contents of this document? If you turst the source of this document, click Yes.
Clicking Yes opens the document with all the contents and it is now renamed to Document 1. If I click no it does not open.
I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
how to combine the content of 2 cells into one cell and have the information separated by a comma.
For example:
CELL 1:
Software 1
CELL 2:
Spreadsheet Software
The desired results is:
CELL 3:
Software 1, Spreadsheet Software
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
I have set up a forecasting module for my company, which I update on a weekly basis.
Basically, I centered my forecast on an 8 week average. Using all of the various equations in a regression, the 8 week series of data is used close to 500,000 times for each week’s forecast. What I am trying to do is move the 8 week series (A#:G#) and change it to the next set of cells (B#:H#) without having to do it using a filter and change all of the various instances of the occurrence. Because of the nature of the forecast, I will be "rolling" it forward every week, so to be able to do this all at once each week would save me a bunch of time. I tried using the Find/Replace fn by having it use the * symbol in place of the #...and this worked when I had it Find the data, but when I tried to use it to Replace the data, it reported an error?
I have series of companies and output ,some values of output are missed how can be replaced by the average of nearst two years values of output or replaced by the following or previous value of output,sample of my data is attached.
View 4 Replies View Relatedhow i would remove telephone numbers in a particular spreadsheet which are preceded with 44 which need to be removed and then replaced with a zero.
eg. 441234567890
I want to be able to replace the bolded and underlined areas of this javascript i have pasted in a cell with the value located in another cell. This means that those numbers would be replaced by a similar thing located in another cell. how can this be done? The way I would think you would do this by defining that say =b8 (an example of where the data could be coming from is ann excel formula, but not sure how to do this ...
View 6 Replies View RelatedOne of my friend send me a problem. I got frustrated and scold him lot to do work like this but then I tried to work on it. Although my Vb experience is not good enough.
The problem is this that we have different things written in cells in column A like this “SET-EDT SPRAY 3.4 OZ & BODY LOTION 3.4 OZ & TOILETRY BAG “ and in some cells “EDT SPRAY 1 OZ”
Now we want to replace value Oz with value Oz / (Value * 3.75) ml. the below example will clarify the situation: ...
What it does is to activate the desired worksheet, and for 3 different choices replace what's there with what I want to be there. I put this together a while back, and it's working fine, but I'm sure it can be cleaned up a bit. Any help would be appreciated.
View 9 Replies View RelatedI would like to set up a worksheet where once an ID number is scanned, the next cell over is populated with that person's name. (i.e. Johnny scans his ID 234567 and in the next cell, "Johnny Smith" is filled in. I have a worksheet with the ID numbers in one column and names in another, I just don't know how to get a second worksheet with a formula that will allow me to do that.
View 2 Replies View RelatedI need a formula that will replace a dollar amount with an X. In other words...I have 10,000 cels and half of them have dollar amounts in them...I would like to replace them with the letter X without having to manually do it...
View 9 Replies View RelatedI am a not too good programmer, but I construct chemotherapy regimens for my medical department. They are used to minimise the risk of wrong dosing to patients, etc. Every regimen is an .xlt file, and about a third of them (there are about 100) generate, in addition to pharmaceutical prescription, automatic letters to patients (for those regimens where patients administer oral dosing at home).
These patient-letters have the hospital's logo on them (the logo is a "shape").
However, the problem is that another hospital also wants to use my system. So I will have to replace all these logos with the other hospital's. I would like to do this in an automated way. I have received the other hospital's logo also as a shape.
I have already constructed a data-inserting macro in a separate file. This macro systematically opens each xlt file and replaces the text in key locations according to the file the macro is in (geographical names, etc.).
Is it possible to automatically replace the logo-shapes in a similar way, with these additional facts/wishes in mind:
1. There is always only one type of shape (a logo) in the xlt files.
2. Even though the shapes are exactly the same, it is possible the names are different.
3. The exact location for the shape is different in each xlt file, so I'd like the replacement to get the same top left coordinate as the one it replaces.
I have constructed 2 different files with the same data-inserting macro, each containing one of the logos (so I can change back to my own hospital) and geographic data. When the data-inserting macro is used, I'd like it to identify the shape in the data-inserting file (there's only one shape in this file), copy it, then identify the shape(s) in the present xlt file, and then replace these, in the exact same location.
can anybody write a macro code for the following: We want to have five values representing operative time in a row with a mean in the sixth position. The hard part is to get a macro that will allow us to put in a new value in the first column and have it shift the other values to the right and dump the existing fifth value and come up with a new mean.
View 9 Replies View RelatedI'm currently building a large spreadsheet which will require numeric codes to be within certain cells. At present these cells are filled with alphabetical codes and the person using them has little knowledge of the numbers, is there a simple way for them to type in the alphabetical code and it to then auto-replace it with the numberical?
View 8 Replies View RelatedHave a column of cells (521 rows), on the column each cell contains a group of number
Example:
27, 539, 484, 485, 506, 594, 523, 502, 505, 525, 541, 526, 527, 529, 542, 533
28, 540, 493, 494, 506, 594, 523, 502, 509, 525, 541, 526, 527, 529, 542, 530
28, 540, 493, 494, 506, 594, 523, 502, 509, 525, 541, 526, 527, 529, 542, 533
Let's say I would like replace 27 to 163 but when I find and replay the "527" also changed to 5163. How can i filtered out 527 and just change the 27. I tried Match Entire Cell Content, it doesn't work.
I have a long list of locations in an excel spreadsheet, stating either the country (eg India) or for US locations the state (in a two letter format, eg NY). I need to change all the state location to say "USA". How can I do this, without doing 50 find and replaces? Can I do something based around the fact all the USA locations are two letters, whereas all the others are longer?
View 4 Replies View RelatedI have a file with some statistics on how agents at work performed. This file gets sent to all of them, but before we send it we change the names for a number, so that they don't know how the other agents are doing (avoiding comparisons). People had been doing this manually, but it's getting really complex as the number of agents is increasing. I know that this can be automatized, but I have no idea on how to do it. A simple formula with an IF statement wouldn't be sufficient. Here's an example of the data. The file that is to be modified to be sent to all the agents looks like this:
View 3 Replies View RelatedI'm working with a lot of documents where I need to replace numberID:s with words.
I have a table of 2 columns, where each number has a corresponding number that it should be translated to.
For instace:
1 - cpu
2 - fan
3 - bus
Is it possible to use this list to automatically Replace all the numbers in a document with their corresponding words?
(Without having to spend time on manually program some kind of macro)
I have 2 colums like this. And I want whenever there is a reference like this âë. "some word" in column 2, to have the macro look up some word entry in column 1, find the text in the same row in column 2 and replace the âë. "some word" reference with the text found in column 2. (See attached, sheet 1 as it is, sheet 2 as I want it to be).
Sheet 1
another wordâë. "some word"
awordìåôÜöñáóç
bwordâë. "aword"
some wordêÜðïéá ëÝîç
Sheet 2
another wordêÜðïéá ëÝîç
awordìåôÜöñáóç
bwordìåôÜöñáóç
some wordêÜðïéá ëÝîç
I need in every column in every rows (that is in whole document ) change symbol, to, (that is before every comma, I need add back slash ).
View 1 Replies View RelatedI am currently trying to get a formula to be added into a range after a piece of code is run.
This is because every time my code has finished, the formula somehow changes and I cannot get it to stop changing values.
I have the formulas further down my sheet, without the = so that it doesn't return a value.
In VBA I have tried something along the lines of Range("A1").Formula = "=" and Range("Z1")
Where A1 is the cell I want the formula in and Z1 contains the formula without an =
It has not updated the cells and they still have the error that I am trying to fix.
Is it possible to use a string to replace code we use repeatdly?
I know that in C it is possible doing this by using what is called a MACRO
For example, replace: ....
Replacing text with formula
I have this so far