Trying to build array elements from a list
19909
19953
etc
My thought is in helper column to concatenate the quotes and comma "xxxxx", Then I will copy paste special transpose and should have an array list
I tried ="""&A2&"","
Returns
"&A2&",
But I need it to return "19909",
I am trying to join text in two different cells using concatenate function / & operator In the combined cell I want the text from second cell to appear in Bold while the text from first cell should continue to remain in normal font. Is there any way i could achieve this?
View 4 Replies View RelatedI have been trying to use the concatenate function to join some text strings to a cell on a different sheet - From an input sheet -Sheet 1 named Input, to a Notification Form (Sheet 2). Although the Function Argument display tells me that it will display the result I want it actually displays just the formula. It's a very simple thing
Address 1 10 Downing Street
Address 2 Westminster
Address 3 London
Postcode SW1A 1AA
I want displayed as: 10 Downing Street, Westminster, London, SW1A 1AA in a single merged cell. All I am getting on the Form is =concatenate(Input!c25," ",Input!c26," "Input!c27," "Input!c28). I feel sure that it is a very easy solution but I can't arrive at it!!
I have a marco that works perfects to Concatenate (join) all of the cells in Column A. However, I want to seperately join all of the cells in each column. So I wanted to the Concatenate of Column B to go in B1, Column C to go in C1, etc (or until there is no data in a column). I think there is a way to change this into a loop, but I don't know how. Below is the macro I have now.
Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
With ActiveSheet
Set rng = .Range("A2:A300")
For Each cel In rng
x = x & cel.Value
Next
.Range("A1").Value = x
End With
End Sub
I am attempting to join together up to 32 arrays. I haven't even managed to join 2! can it be done in excel 2003 VBA? the arrays are 2 dimensional (x Rows, 3 Columns).
I have tried this but just produced empty variants:
I'm trying to get the following formula into a cell using VBA code:
=AverageIf(A2:AXX,"HR",H2:HXX)
But it's being a problem.
This is the current code im trying to use:
[Code] ........
I've tried
[Code] .......
too but that didn't work either.
I am a SAS programmer and often use SAS Proc Export to dump data to Excel. Sometimes the data is an Excel formula, e.g.
=hyperlink("#Sheet1!r1c1","click here")
Because the data is text, what get's put in the cell is '=HYPERLINK("#Sheet1!r1c1","click here") (note leading single quote).
I cannot use the replace function to edit them out, so must hand edit each one out -tedious at best.
how do i put a formula using: activecell.formula"=CODE("A")"
with " in it
it just dosent work
and is there a way to use the returned value of these formulee in macros with out actually putting them into a cell
I have a macro that imports xml and then allows the user to update certain fields. The XML contains many quotation marks and inbound I deal with this by replacing " with ' using Find & Replace.
But before exporting the XML again I need to replace the ' with " but am stuck on how to do this.
For instance I'm trying the following with no success:
Cells.Replace What:="'", Replacement:=""", LookAt:=xlPart" _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells E39:E53 are conditionally formatted to fill red if cell $AB$13=the cell 3 to its left .. eg: cell E39 would fill red if cell B39 (the cell 3 to the left of E39) is equal to cell $AB$13
Only one cell in the range will meet the condition at any time.
I also have the same range conditionally formatted to fill black if cell $AF$13=the cell 3 to its left .. eg: cell E39 would fill black if cell B39 (the cell 3 to the left of E39) is equal to cell $AF$13
Ranges F39:F53, and G39:G53, H39:H53 etc etc etc (all the way up to X39:X53) are all conditionally formatted the same way.
What I would like is for the cell that fills red in the first range to have a line connecting it to the cell that fills red in the second range, etc etc etc.
And the same for the cells that fill black.
Creating a chart from my data is not an option for other reasons, so I'd like to explore this possibility.
I'm trying to create a shortcut that will allow me to add quotation marks to the begining and end of text in cells. I've tried the following, which works fine when only one cell is highlighted:
VB:
Dim rng As Range
Dim txt As String
Set rng = Selection
txt = """"
rng = txt & rng.Value & txt
However as soon as I highlight more than one cell, it errors. How to make it work for a range of cells?
I am having a problem with the correct coding needed for a qotation mark
i wish to place a quotation mark around some text with coding
example "TEST" by using the code below this appears fine and looks fine when i save the file, however when i open that file in a .txt file i see """TEST"""
This is the coding i am using and can see it may be where the problem lies
ActiveSheet. Range("C4").Value = """" & Sheets("PRODUCTS").Range("Y1").Value & """"
to correct it i have tried this
ActiveSheet.Range("C4").Value = " & Sheets("PRODUCTS").Range("Y1").Value & "
Column A2:A100 contains part description various len lengths max is 50 min is 22.
Column B2:B100 contains scrap qtys.
In column C2:C100 in need to join the text in A and B.
i want the scrap qty's all to be alligned i.e level, as column C is shown in a userformlist box.
meaning joining text/values would look like this (ignore full stops)
dispenser calsberg .........70
tap beer......................... 80
cowl................................100
instead of
dispenser calsberg 70
tap beer 80
cowl 100
I am trying to change the value in a cell by referenceing it to another sheet. My cell is: Today it is 33F, have a nice day. Now, 33F should be called from another cell and should be inserted in this new cell. If I change the 33F in the other cell this change should be reflected in my text file.
View 3 Replies View RelatedI have a multipage control on a userform. On Page 8 of that multipage, I have label1, part of that label is modified by other parts of the program. This is done by the program writing to a sheet of Data and then the program pulls that info into this label. My question is,what is the correct format to populate the label?
see below: This is what I have in my Userform_Activate and Userform_Initialize subs:
CurrentVersion = Sheets("Data"). Range("N2").Value
Label1.Caption = "This will allow you to change the Caption of the UserForms through out the operational program. Some UserForms cannot be changed. The ending of" + Chr(32) + Chr(34) + "Automatic 2005 V" + Chr(32) + CurrentVersion + Chr(34) + Chr(32) + " will be added to what you enter below as default."
CurrentVersion is pulled off of the datasheet which is a number like 5.9.8.2. however it errors "Run time error 13 - type mismatch" I can only suspect the label1.Caption needs something like: Multipage8.label1.caption =
I have a column with numbers in each cell. I need to add the same three letters before each of the numbers. Example:
number in cell: 25067 I need it: ABC25067
I did a macro for it but had to do each one individually. I imagine there is a simple formula to do this automatically but I don't know enough to figure it out (despite reading up on it for the last hour!)
Join an array with Text elements to create a string that can be Evaluated
So for instance if I have Array("A", "B", "C") and I want to evaluate("=({" & Join(array, ",") & "})="A)"). Is there any way to do this without having to loop or push to a Named array first? I'll even take this evaluate thing if I can do it with text and numbers
I am using the following formula to join several text strings together:
=CONCATENATE(B4,$A$1,C4,$A$1,D4,$A$1,E4,$A$1,F4)
(in the above $A$2 is just a few spaces in order to seperate the test)
I was wondering if there is a way of joing text strings that will align all info properly? Right now it just joiins them together but visually that is not really very appealing.
This is an example of what I am getting:
11 111 1111 11 111
22 2222 22 2222 222
333 33 3333 333 333
This is how I want it:
11 111 1111 11 111
22 2222 22 2222 222
333 33 3333 333 333
I am trying to append data in let's say column A:A with the variable text in J:J. The data in column A:A will look like this /dir/dir/dir/<from this point is where I need the data appended>.
View 4 Replies View RelatedFor the following formula:
=”TEXT1”&”TEXT2”
The result is:
TEXT1TEXT2
Is there any kind of modifier that I can use to force the output to multiple lines, i.e.:
TEXT1
TEXT2
If this wasn’t a formula I could just use ALT+ENTER. Wrapping the text will not work because I need fixed break points not dependent on the text box width.
I am trying to reference text and another document. The text I want is " stock option expense negatively impacted 1Q:07 by:" and the number i want it to bring back is currently in the model as a percentage. However I would like to bring it back in basis points which would be the percentage times 10,000. So if its in the model as 0.4%, I'd like to bring it back as 40 bps. Here is the formula I am using that is insufficient.
="Stock option expense negatively impacted 1Q:07 by: " & TEXT([FOSL2.xls]QTR!$BK$34,"0") & " bps."
I had a hard time to put a suitable title to this query, not sure if above is good enough for my query. I am entering combination of text and number in a cell using a formula. I am wondering if it is possible to add formula which can format number such that it is displayed in accounting format. Example:
Expected output = Trial 1,000
Using this formula
="Trial "&Sheet1!A1
where A1 in Sheet1 = 1000
Output is Trial 1000
i know this is easy but i don't have an idea how to do it. I have dates written on column A1:A7 like
29-Mar-09
30-Mar-09
31-Mar-09
1-Apr-09
2-Apr-09
3-Apr-09
4-Apr-09
what I would like to do is how can I combine 29-Mar-09 and 4-Apr-09 into one cell...I would like the output to be placed in cell A8 appear as "29-Mar-09 to 4-Apr-09".
I have two cells. In cell A1 there is a text "(fi) = " and in A2 a number, for example 30. (fi) means a symbol for diameter (letter "f", using Symbol font). Is it possible to joint those two cells in third cell using formula "=A1&A2", maintaining original fonts? What i get is "f = 30", not "(fi) = 30".
View 4 Replies View RelatedI am trying to write a bit of code to go into a macro and as I am very much a leaner, I thought I would create the formula I need and record it, then copy in the relevant bit. However, I cannot even get the formula to work - never mind the macro bit!! The formula I am trying to do is something like this...
In column S:S
IF the first digit in R2 (and eventually R2 to R lastrow) = 1 to 3, concatenate Q2 (and eventually Q2 to Q lastrow) + "/" + "Q1"
IF the first digit in R2 (and eventually R2 to R lastrow) = 4 to 6, concatenate Q2 + "/" + "Q2"
IF the first digit in R2 (and eventually R2 to R lastrow) = 7 to 9, concatenate Q2 + "/" + "Q3"
IF the first digit in R2 (and eventually R2 to R lastrow) = 10 to 12, concatenate Q2 + "/" + "Q4"
Column R:R has the year and the first digit of Q:Q hold the month so I want to end up with 2007/Q1 etc.
I'm trying to convert a set of numbers between 1 and 9999 into text strings, with the number at the end as a four digit string, so 1 becomes "COLUMN0001", 123 becomes "COLUMN0123", etc. In excel, it would be easy - I would just use the text function and convert the number to a string and append it to the word COLUMN, but as far as I know, there isn't an equivalent function in excel and running excel functions in VBA is notoriously slow (especially as I will need to do this about 360,000 times).
View 4 Replies View RelatedThis is not related to macro - it's an excel question regarding wrapped-around text within a cell (i.e.: text brought onto next line by <Alt>+<Enter>)...
SayI have the following in a cell :
Car<Alt>+<Enter>
Truck<Alt>+<Enter>
Van
and say I want to add a figure (in this example 1A) to the first part of the cell and then another to the second line of wrapped text and so on. How can I split the cell (or lookup the first 'wrapped' line) so that I can then perform changes (using formulas with '&' to combine etc or whatever) so that in the end cell have the following:
Car 1A<Alt>+<Enter>
Truck 2C<Alt>+<Enter>
Van 1B
Looking for formula with if then statement that involves a nested formula or concatenation. I need a formula that says: If cell A reads exactly ABCD then concatenate cell A with cell C. Very basic to most of you gurus but I'm fairly new with formulas.
View 3 Replies View RelatedI am trying to find a way to create a macro that will take the data selected from the combo box and concatenate it with the data found in cells.
For Example:
Combo Box Selection - "Test"
ID (Column A) - Before
12345
98765
99999
55555
empty cell
23232
ID (Column A) - After
12345 Test
98765 Test
99999 Test
55555 Test
empty cell
23232
I have a table that looks like this:
ID
HostLocation
NDaysinHost
marina.silva
USA
100
[Code]....
I would like to obtain a table in the following format where I can find
ID HostLocation NDaysinHost
marina.silva USA, Norway, Bolivia, Italy, Ecuador... 100,45,67,8,9...
tatiana.gottig Venezuela, Chile, Peru, Canada 89,54,32,6
Searching in the net I found the following code:
VB:
Sub groupConcat()Dim dc As Object
Dim inputArray As Variant
Dim i As Integer
Set dc = CreateObject("Scripting.Dictionary")
inputArray = WorksheetFunction.Transpose(Sheets(1).Range("A2:C9").Value)
[Code]....
However I only obtain the first two columns concatenated. It says that for more columns repeat the loop, however I did and the result was not the expected, it returned me the same two columns again concatenated. How can I arrange this to fit my criteria?