Macro: Concatenate Text, Variable & Double Quotes
Nov 23, 2006
I need to write a line of code which puts the following into a cell:
=+C4 & " - " & 1
The cell reference at the beginning will always be C4, however the "1" at the end will be variable named "GroupNo"
So my code will be something like:
Range("B46").Value = "=+$C$4 & " & "" - "" & "GroupNo"
This isn't working. I've tried every variation of what it should be but I keep getting errors like "type mismatch" or just the wrong thing being put in the cell.
View 7 Replies
ADVERTISEMENT
Aug 1, 2013
how to use the indirect function to pull data from a pivot table. This is the formula I am trying to recreate: =GETPIVOTDATA("sum of BOE",$A$14,"CLASS","PROVED","Years",2013)
I can't seem to get the indirect function to work properly with the words in double quotes, such as "PROVED". How do I format that part of the formula properly?
This is as far as I can get. =GETPIVOTDATA("sum of BOE",INDIRECT($M13),"CLASS","PROVED","Years",$N13) $M13 refers to $A$14 and $N13 refers to 2013
View 2 Replies
View Related
Nov 21, 2006
I'm trying to set up a macro to be assigned to a button to copy the text in a cell into an application which uses similar text capabilities to Notepad.
The simple macro of:
Range("D5").Select
Selection.Copy
... results in the text of the cell being copied to the clipboard with quotation marks before and after the text. I can manually enter the cell, select the text, the copy it... but this process can not be recorded in a macro - I can edit the cell and copy the text but when leaving the cell it gives the error "unable to record".
Is there any way I can copy the text to the clipboard without getting these quotes?
View 9 Replies
View Related
Feb 13, 2008
=IF($A2="","",IF(O$1="","",VLOOKUP($A2,HQA01!$A$24:$AG$525,6,FALSE)))
The above statement works great, but when I try to replace the HQA01 (worksheet name) with a cell reference it doesn't work anymore. I want to point to the cell that has that name of the sheet in it rather than hardcode each sheet name.
=IF($A2="","",IF(O$1="","",VLOOKUP($A2,$Q$1&"!$A$24:$AG$525",6,FALSE)))
When I try the &, or the concatenate function it appears to put quotes around the entire result
View 9 Replies
View Related
Jul 23, 2014
I have a table that looks like:
7300
3
7301
2
7302
5
And I am trying to make a macro that will go concatenate the first 3 columns of all rows in worksheet 7300, the first two columns of all rows in worksheets 7301, etc, and insert them into column A of each corresponding worksheet.
View 7 Replies
View Related
Feb 11, 2013
Attempting to match acct #s on source sheet with account numbers on Template but the numbers on the source are = to the cell value while the numbers on the template are equal to finding the source cell value in the template. Let me explain with code.
Set Source value:
Code:
Set FirstIO = zfir.Range("e8")
Set Template value:
Code:
Set CheckIO = Range("Accounts").Find(IO, LookIn:=xlValues)
When I get here:
Code:
If FirstIO = CheckIO.Value Then
I get a mismatch because
FirstIO = 3333333 and CheckIO = "3333333"
How do I make these 2 values Match?
View 2 Replies
View Related
Jan 30, 2009
I'm running the following to put quotes around data in a workbook:
Sub test()
For Each s In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
s.Value = """" & Format(s.Value, "00000000") & """"
Next
For Each s In Range("B1:B" & Cells(Rows.Count, 1).End(xlUp).Row)
s.Value = """" & Format(s.Value, "####") & """"
Next
End Sub
And exporting as a CSV file for loading into another program. The problem is everything looks OK in Excel, but when I open the file in Notepad to check the output, the quotes are all triple. How can I get only single quotes around the data?
View 9 Replies
View Related
Sep 2, 2007
Formulae Solution prefered if possible--
Is it possible to extract the two text strings between the quotes, placing them in Col. C & D respectively, e.g.
Application.WorksheetFunction.Substitute(c.Formula, " ANTI ", " ANTI-HERO ")
Application.WorksheetFunction.Substitute(c.Formula, "SEC9", "SECTOR 9")
Application.WorksheetFunction.Substitute(c.Formula, "LNGBRDC", "LONGBOARD COMPLETE")
--Col. C---------------Col.D
Anti------------------Anti-Hero
SEC9--------------- SECTOR 9
LNGBRDC---------- LONGBOARD COMPLETE
There are three types of scenarios-
1) Values enclosed in quotes that begin and end w/ spaces:
Application.WorksheetFunction.Substitute(c.Formula, " ANTI ", " ANTI-HERO ")
2) Values enclosed in quotes that does not contain a leading space or trailing space:
Application.WorksheetFunction.Substitute(c.Formula, "SEC9", "SECTOR 9")
3) Values enclosed in quotes that have a space between itself:
Application.WorksheetFunction.Substitute(c.Formula, "LNGBRDC", "LONGBOARD COMPLETE")
View 9 Replies
View Related
Jan 22, 2009
I'd like to insert a formula into a cell based on selections made on a userform. The problem is, the formula contains quotes, and in VBA, when I do this:
View 8 Replies
View Related
Jul 29, 2012
I would like to query your knowledge database (too much VBA I guess ) as I have a predicament that I cannot seem to solve.
Here is the thing: I am trying to write a sub that would enable me to automatically put in place extensible name ranges (the Offset worksheet function).
The problem is that, despite everything seems to go well, the reference in the name box displays : ="DECALER($C$2;0;0;5-1;1)" (the string comes from a variable and Decaler is Offset in French). These quotation marks are the culprits for sure, as taken off the formula works beautifully.
I've tried everything I've found on the internet, that is to say: Replace(String, Chr$(34), "") so on and so forth, but these marks are not recognized a part of the string so they escape the replacement.
View 9 Replies
View Related
Jan 2, 2013
I have a simple task and I use the concatenate formula to resolve alot of keystrokes. I now have an issue where I have to edit that formula because of a additional character for the string won't upload into a database. Here's my example:
1. Cell A1= 12345xxxx Cell B1= 67 Cell C1= 8
I use =CONCATENATE(A1,"-000"&B1,"-000"&C1) and my result is displayed in D1= 12345xxxx-00067-0008
Easy enough! Now adding the following is the problem:
1. Cell A2= 12345 Cell B2= 67 Cell C2= 89
I use =CONCATENATE(A2,"-000"&B2,"-000"&C2) and my result is displayed in D2= 12345xxxx-00067-00089
Now D2 has one too many characters from C2
I modify the formula by reducing to "-000"&C2 to be "-00"&C2 manually but now the line items have quadrupled and manually isn't going to work for this being so time consuming.
Is there a way to use a variable with the concatenate or am I using the wrong formula period ??
View 3 Replies
View Related
Oct 24, 2012
I have a problem with a worksheet that my company accounting system exports every month.
Attached : sample of the worksheet.
In column ( F ) , I need a macro to do the following calculations:
1- Check for the Title - if it begins with "Cost Center"
2- Check for the Title - if it begins with "Account Code"
3- Detect the Range Start just below "Account Code" , and End with the row above "Total"
4- Concatenate the string written in each row of the range with the string in "Cost Center"
The Story is:
Each Month I've this worksheet with hundreds of Cost Centers and subsidiary Account Codes, And to be able to analyze the accounts efficiently I need to concatenate both Cost Centers & Account Codes manually ( as you see coloring sample in the attached file ). Which led to wasting many hours , and high risk of error while copying and pasting formulas.
View 3 Replies
View Related
Feb 28, 2008
Following advice from this forum I have developed the following code to save a workbook with a filename made up from Ranges within a worksheet. What I had also hoped to do was include the first 2 letters of a Range.
The code that works is as follows
View 14 Replies
View Related
Dec 1, 2013
I have a macro that imports text file and puts a comma after each number (alphanumeric also). I have tried to change it to put single quote around the number and comm afterwards.
Text file listing
123
456
789
When I run my marco I get the following
123, 456, 789
which is what I want...
Now I want the same text to have single quotes around the data example:
'123', '456', '789'
All that I have tried to adjust keeps missing single quote at the front end and also missing the single quote at the rear end example: 123', '456', '789
Here is the vba I'm running
Sub GetserialNumbers()
Dim FileNum As Long, PathAndFileName As String, TextFromFile As String
Const Delimiter As String = ", "
[Code].....
Whe I run this I have to select the text file to get. I keep it in my documents as a quick access. I'm using MS VBA 6.5 Excel 2007 Windows 7 Enterprise.
View 2 Replies
View Related
Mar 27, 2008
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
View 4 Replies
View Related
Mar 27, 2009
I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this.
Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this?
Or in the attached file, I would like to say A and 15 and get A15.
View 3 Replies
View Related
Sep 15, 2014
I have two columns - A and B. In column A there are blank cells and filled cells. There is a blank cell and after it - a variable number of filled cells, then a blank cell again and a variable number of filled cells. I am trying to concatenate the filled cells from column A into a single cell in column B, which is corresponding to the blank cell in column A. Values should be comma delimited.
For example: A2-blank; A3-27; A4-63; A5-blank; A6-31; A7-blank .. and in column B should be B2-27,63; B3-blank;B4-blank; B5-31,B6-blanc and etc.
View 5 Replies
View Related
Dec 15, 2006
I have two sheets of data, exported by a program. The first sheet uses a single row for information of each log entry. The second page will have 1 to X rows for information of each log.
What I am would like to do is combine that data into one cell, and place it in the corresponding row on the first page. This will be used in a mail merge, and I would need commas and an "and" between the data points, where appropriate for proper English. I know how to get the data together, but am having issues trying to get the correct grammar within the cell.
Willing to use either VBA or other formulas.
View 9 Replies
View Related
Nov 14, 2007
I want to do a concatenate for multiple fields, but want the user to specify which cells should be concatenated by entering this in a few fields. What does this formula (or VBA) look like?
Let's assume there is the possibility to concatenate a maximum of 4 cells. Call the 3 cells that will be concatenated cell1, cell2, cell3 and cell4.
On the first sheet of the workbook the user enters the following:
cell1 = Sheet2!A4
cell2 = Sheet2!B4
cell3 = Sheet2!C4
cell4 = Sheet2!D4
Here are some cells and their values:
Sheet2!A4 = Hel
Sheet2!B4 = lo
Sheet2!C4 = World
Sheet2!D4 =
Upon enterring the information after the = sign for the cell1, cell2 and cell3, the formula for the concatenate becomes: =CONCATENATE(Sheet2!A4, Sheet2!B4,Sheet2!C4 ).
The result of the formula is: HelloWorld
If I now change the values of cell1, cell2, cell3, and cell4 the following should happen:
cell1 = Sheet2!B4
cell2 = Sheet2!C4
cell3 =
cell4 =
The formula for the concatenate becomes: =CONCATENATE(Sheet2!B4, Sheet2!C4).
The result of the formula is: loWorld
View 9 Replies
View Related
Jan 26, 2008
I have a spreadsheet that imports data in column A with quatation marks foe eg "7000", "7101A" etc
I would like VBA code that will convert these into numbers.
View 9 Replies
View Related
Feb 18, 2008
I have A UDF now in my Personal.xls file
It appears that I must reference as so, in order to acheive my goal
=Personal.xls!CellType(A8)
Can this be shortened to soehow exclude the Personal.xls! portion?
View 9 Replies
View Related
Jul 20, 2007
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 & "
View 9 Replies
View Related
Feb 9, 2010
I have populated a listbox from a filtered list and when 'Double Clicking' a name I want a macro to run. All seems to be OK apart from it is not finding/returning any data; I know the data is there and I think it may be the way I'm referencing the listbox value;
Sub CopySignificant()
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Clients to Invoice")
Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long
sCount = 0
dRow = 1...................
View 9 Replies
View Related
Oct 26, 2006
I want a X to be put in the activecell if empty or removed if already there. By searching this forum I found and read the article "Adding a Command Button to the Excel Right Click Pop-up Toolbar/Command Bar" and was able to add the code to do this to the right click sub menu. Is there a way to do the same thing by double clicking on the cell? This probably seems a bit nit pickey but I would just like for it to work using double click for my users. I wrote the following code to performe the action
Sub Toggle_Macro()
If IsEmpty(ActiveCell) = True Then
ActiveCell.Value = "X"
With Selection
.HorizontalAlignment = xlCenter
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.ColorIndex = 3
End With
Else: Selection.ClearContents
End If
End Sub
And placed the code below in Private Module of the Workbook Object.................
View 2 Replies
View Related
Mar 31, 2014
My cells have wrap around text enables. However when I double-click the row, it does not automatically adjust the row height to accommodate all of the text. It shrinks to a small row height. How do I fix this?
View 1 Replies
View Related
May 22, 2014
I am trying to run a macro when I double click on a certain cell (D34). The cell has data in it (Southeast NSC). What I am expecting to happen is once I double click on the cell the macro will run the retrieve and I will end up at expense chart page.
The macro I am trying to run is an Essbase retrieve.
[Code] ....
I tried using the macro below but no luck:
[Code] ....
And i tried:
[Code] ....
View 1 Replies
View Related
Dec 25, 2008
In Excel 2007 I want to concatenate two columns of text. In Column A all the cells contain a single statement that I want to prefix the statements in the cells of column B (the statements in column B differ from cell to cell) I have used the formula =A1&" "&B1 and this is fine for that row, when I use the fill handle and pull it down the page the formula changes accordingly i.e.=A2&" "&B2, =A3&" "&B3 etc. But when I make the text appear using control+ I only get the concatenation of the first row repeated all the way down, irrespective of the contents of other cells in Column B.
View 14 Replies
View Related
Feb 2, 2009
I am concatenating a date with some text. However, after concatenating, the date becomes a text string. How do I preserve the date format? Pls see the attached file.
View 3 Replies
View Related
Feb 6, 2009
I cant quite get this, I have 4 cells in one row (a selection and can be any row), I need to string the values together and add some simple text.
Example:
Assume row 1 and columns A, B, C, D.
496 | 6d7 | Minor | Ø.375 +/-.010
What I need this to read as one long string:
#496, 6d7, Minor, Ø.375 +/-.010
Adding the text in Red.
Column A may be a single digit or up to 10 digits in length. Once the string is configured I want to copy it and manually paste in another application. If the string is placed any where else in the worksheet for formatting, it can be deleted after the copy/paste or macro end.
View 5 Replies
View Related
Feb 25, 2007
I have a date, 03/17/2007, in column A
I have the word, Cashiers in column B
I want to concatenate the 2 and use this in a vlookup. Can this be done? If so could someone explain please?
I keep getting the N/A error on this.
View 9 Replies
View Related
