Special concatenation

**Ingolf** · 08-11-2015, 06:13 PM

snb, thanks for reply

Result of VBA cod sould be;

1;"BEER";"1";"0,00";"1";"1";"1";"";
2;"VODCA";"1";"0,00";"1";"1";"1";"";
3;"COGNAC";"1";"0,00";"1";"1";"1";"";
4;"WHISKY";"1";"0,00";"1";"1";"1";"";
5;"BEER";"1";"0,00";"1";"1";"1";"";

Your VBA cod result is:
1";"BEER";"1";"0";"1";"1";"1";"";
"2";"VODCA";"1";"0";"1";"1";"1";"";
"3";"COGNAC";"1";"0";"1";"1";"1"
"4";"WHISKY";"1";"0";"1";"1";"1"
"5";"BEER";"1";"0";"1";"1";"1"

left numbers should not be surrounded by quotation marks.
If in the seventh column is not a number, must be empty quotation marks (""). I add to first 2 line (in red)
After last number must be ";" (without quotation marks)
It must be like result of Rick VBA code.
Can you fix it? Thanks.

**DocAElstein** · 08-30-2015, 03:28 AM

Hi Ingolf,
. A bit late, and you have a working solution but I caught this as a good chance to practice my “Quote in Evaluate String” Stuff. Something I have to practice to get right since I have been struggling a year to understand and still can’t quite understand it..
. Anyways, this code alternative, I think will also work for you. _ It gives by me exactly the same results as Rick’s, which is a good sign ( and most of these sort of codes I have from his Threads anyways.. )
. Note, you will need to put your zeros in the cell as 0.00 and format it as text for my code to work ( Rick’s work’s without that , but that is what you would expect!!)…..

Your input for a few rows
Using Excel 2007

_Row\^Col	A	B	C	D	E	F	G	H
1	AA	NAME	CC	DD	EE	FF	GG	HH
2	1	BEER	1	0.00	1	1	1
3	2	VODCA	1	0.00	1	1	1
4	3	COGNAC	1	0.00	1	1	1

Sheet1

......
. My typical output for a few rows….

Using Excel 2007

_Row\^Col	A
1	1;"BEER";"1";"0.00";"1";"1";"1";"";
2	2;"VODCA";"1";"0.00";"1";"1";"1";"";
3	3;"COGNAC";"1";"0.00";"1";"1";"1";"";
4	4;"WHISKY";"1";"0.00";"1";"1";"1";"";
5	5;"BEER";"1";"0.00";"1";"1";"1";"";
6	6;"VODCA";"1";"0.00";"1";"1";"1";"";
7	7;"COGNAC";"1";"0.00";"1";"1";"1";"";

Sheet2

Code:

'
Sub IngolfBoozeConcatenatingQoutyStuff() 'http://www.excelfox.com/forum/f2/special-concatenation-2042/
'Worksheet info
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Sheet1") 'Sheet Info
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets("Sheet2")
Dim lr As Long: Let lr = ws1.Cells(Rows.Count, 2).End(xlUp).Row 'The Range Object ( cell ) that is the last cell  in the column of interest has the property .End ( argument Xl up ) appisd to it. This returns a new range ( cell ) which is that of the first Range ( cell ) with something in it "looking up" the XL spreadsheet from the last cell. Then the .Row Property is applied to return a long number equal to the row number of that cell. +1 gives the next free cell.    ( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
Dim lc As Long: Let lc = ws1.Cells.Find(What:="*", After:=ws1.Cells(1, 1), lookat:=xlPart, LookIn:=xlFormulas, searchorder:=xlByColumns, searchdirection:=xlPrevious).Column 'Get last Row with entry anywhere for Sheet1. Method: You start at last cell then go backwards, effectively starting at last column ( allowing for different XL versions ) searching for anything ( = * ) by columns then get the column number
 
'Range Info
Dim rngA As Range: Set rngA = ws2.Range("A1:A" & lr - 1 & "") 'Output Range
rngA.ClearContents 'Just so I know the conctnating lines work!!
 
'String argument for Evaluate "One Liner
Dim Evalstr As String
Dim c As Long, r As Long 'Columns, 'Rows in Sheet
'Let Evalstr = Evalstr & "" & ws1.Range(ws1.Cells(2, 1), ws1.Cells(lr, 1)).Address & "" & "&"";" & """" & """&" 'DON'T WORK !!!
Let Evalstr = Evalstr & "" & ws1.Range(ws1.Cells(2, 1), ws1.Cells(lr, 1)).Address & "" & "&"";""""""&" 'Concatenate cell values with  ; inbetween
    For c = 2 To lc - 1 Step 1 '
    Let Evalstr = Evalstr & "" & ws1.Range(ws1.Cells(2, c), ws1.Cells(lr, c)).Address & "" & "&"""""";""""""&" 'Concatenate cell values with  ; inbetween
    Next c
Let Evalstr = Evalstr & "" & ws1.Range(ws1.Cells(2, lc), ws1.Cells(lr, lc)).Address & "" & "&"""""";"""  'Concatenate last row ( usually .Address & ""  -  without any  ;
 
Let Evalstr = Replace(Evalstr, "$", "") 'Get rid of $. Not too important here but can help in keeping <255 for longer Strings
 
Let rngA.Value = Evaluate(Evalstr)
MsgBox ("I have """"Done" & """" & " It") '!? But why DONT " & """" & " WORK in me Evaluate String like it does in the Msgbox string?????
End Sub 'IngolfBoozeConcatenatingQoutyStuff()
'

Alan Elston

**Ingolf** · 08-30-2015, 11:03 AM

Thank you Alan.

**DocAElstein** · 08-30-2015, 01:17 PM

Originally Posted by Ingolf

Thank you Alan.

you is welcome,
Alan

**DocAElstein** · 08-31-2015, 05:08 PM

Originally Posted by snb

Or:

Code:

Sub M_snb()
  Sheet1.UsedRange.Columns(4).NumberFormat = "0.00"
  Sheet1.UsedRange.Offset(1).Copy
  .......

Ahh, interesting that formatting the cell like that works with yoouur "Clipboard method"
. I tried versions of that with mine and fell down - I guess because I am "evaluating stuff" and then so getting a 0. You are copying and pasting.
. But you inspired me to take another look, and got it in the end:

... this bit did the trick:

Code:

'Attempting to get 0.00 format
'ws1.UsedRange.Columns(4).NumberFormat = "0.00" 'Don't Work
ws1.Range("D2:D" & lr & "").NumberFormat = "@"
ws1.Range("D2:D" & lr & "").Value2 = "0.00"

…………………………………………

Code:

Sub IngolfBoozeConcatenatingQoutyStuff() ' Post #16 http://www.excelfox.com/forum/f2/special-concatenation-2042/
'Worksheet info
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Sheet1") 'Sheet Info
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets("Sheet2")
Dim lr As Long: Let lr = ws1.Cells(Rows.Count, 2).End(xlUp).Row 'The Range Object ( cell ) that is the last cell  in the column of interest has the property .End ( argument Xl up ) appisd to it. This returns a new range ( cell ) which is that of the first Range ( cell ) with something in it "looking up" the XL spreadsheet from the last cell. Then the .Row Property is applied to return a long number equal to the row number of that cell. +1 gives the next free cell.    ( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
Dim lc As Long: Let lc = ws1.Cells.Find(What:="*", After:=ws1.Cells(1, 1), lookat:=xlPart, LookIn:=xlFormulas, searchorder:=xlByColumns, searchdirection:=xlPrevious).Column 'Get last Row with entry anywhere for Sheet1. Method: You start at last cell then go backwards, effectively starting at last column ( allowing for different XL versions ) searching for anything ( = * ) by columns then get the column number
 
'Range Info
Dim rngA As Range: Set rngA = ws2.Range("A1:A" & lr - 1 & "") 'Output Range
rngA.ClearContents 'Just so I know the conctnating lines work!!
 
 
'Attemping to geet 0.00 format
'ws1.UsedRange.Columns(4).NumberFormat = "0.00" 'Don't Work
ws1.Range("D2:D" & lr & "").NumberFormat = "@"
ws1.Range("D2:D" & lr & "").Value2 = "0.00"
 
'String argument for Evaluate "One Liner
Dim Evalstr As String
Dim c As Long, r As Long 'Columns, 'Rows in Sheet
'Let Evalstr = Evalstr & "" & ws1.Range(ws1.Cells(2, 1), ws1.Cells(lr, 1)).Address & "" & "&"";" & """" & """&" 'DON'T WORK !!!
Let Evalstr = Evalstr & "" & ws1.Range(ws1.Cells(2, 1), ws1.Cells(lr, 1)).Address & "" & "&"";""""""&" 'Concatenate cell values with  ; inbetween
    For c = 2 To lc - 1 Step 1 '
    Let Evalstr = Evalstr & "" & ws1.Range(ws1.Cells(2, c), ws1.Cells(lr, c)).Address & "" & "&"""""";""""""&" 'Concatenate cell values with  ; inbetween
    Next c
Let Evalstr = Evalstr & "" & ws1.Range(ws1.Cells(2, lc), ws1.Cells(lr, lc)).Address & "" & "&"""""";"""  'Concatenate last row ( usually .Address & ""  -  without any  ;
 
Let Evalstr = Replace(Evalstr, "$", "") 'Get rid of $. Not too important here but can help in keeping <255 for longer Strings
 
Let rngA.Value = Evaluate(Evalstr)
MsgBox ("I have """"Done" & """" & " It") '!? But why DONT " & """" & " WORK in me Evaluate String like it does in the Msgbox string?????
End Sub 'IngolfBoozeConcatenatingQoutyStuff()

Thanks for coming back, Snub, and encouraging me to get it right, and understand why
Alan

........
Edit: Once you have actual test data in,

Using Excel 2007

_Row\^Col	A	B	C	D	E	F	G	H
1	AA	NAME	CC	DD	EE	FF	GG	HH
2	1	BEER	1	0.00	1	1	1
3	2	VODCA	1	1.23	1	1	1
4	3	COGNAC	1	0.00	1	1	1

Sheet1

then take out this line
'ws1.Range("D2:D" & lr & "").Value2 = "0.00"

to get:

Using Excel 2007

_Row\^Col	A
1	1;"BEER";"1";"0.00";"1";"1";"1";"";"";"";
2	2;"VODCA";"1";"1.23";"1";"1";"1";"";"";"";
3	3;"COGNAC";"1";"0.00";"1";"1";"1";"";"";"";

Sheet2

**DocAElstein** · 09-17-2015, 03:01 AM

Hi...
.. i have a feeling I will be looking once a year at building strings for Evaluate and other Excel and VBA things, and trying to relearn. It seems that the mastering of quotes in Excel and VBA is the key to the understanding. Once you master that, the VBA Evaluate Method seems to become very useful.

Originally Posted by snb

.......To 'walk' inyour approach:...

.. I think I need to go through at a Snail's Pace….
. Maybe a small follow up here could be appropriate to share my thoughts. This Thread did set off my yearly thoughts again….( and to keep it relevant: see****** right at the end, for yet another 2 Codes to meet the OP's Post # 1 original request) ...
.......

. 1) Basics

. It helps at the outset, I feel to try and maintain a colour convention where possible to try and distinguish betweenExcel Spreadsheet andVBA "Worlds" as I call them..
.. ( For code lines here I will drop my irritating habit of explicitly referencing all Ranges so , In a normal module, an unqualified Range call equates to Application.Range, which in most cases is the ActiveSheet.Range. Or alternatively put the code in the sheet module of the sheet to which it relates, and that will work I think that an unqualified Range call equates to that sheetAnd I will mostly let Let out for snb_..)
....
. Fundamentally a quote seems to inform / prepare / warnExcel Spreadsheet and VBA that a string is coming.

Hence we have

Dim v As Variant
Let v = "3" ' Results in a Variant variable containing astring value "3"
Let v = 3 ' Results in a Variant variable containing a Long Number 3 ( actually an Integer ? )
...

. Coming to understanding the Evaluate, whichhas become a strange obsession with me. A very basic, common , but not reallycorrect IMO explanation is that Evaluate allows VBA to do whatever one can doby writing in some ( string ) of characters, numbers, mathematical operators, Functionsand the like within aExcelSpreadsheetcell.
. This leads to a syntax
= Evaluate("___________")
. often verbally given as Evaluate ( Stringexpression )
. where string expression is what one writes ina cell . At the outset i like to try and stay explicit, not missing bits outwhich VBA often allows you to do as a sort of recognised shorthand..
. So the classic example is
Excel Spreadsheet =A1 -------- VBA =Evaluate ( "= A1 " )

. I have a modified definition, and why not, ExcelandVBA seems so badly documented that no one really "knows" for sure anymore:what goes on Evaluate encompasses a lot of what Excel Spreadsheetand VBA [COLOR=#000000]is about and if it was not for the annoying 255character string limit[FONT="Verdana"][SIZE=3] ( . 4 ) ( IMPORTANT : that is the string it actuallysees

**DocAElstein** · 09-17-2015, 03:04 AM

. Coming back to the relevance to this Thread.. Understanding quotes in VBA ( ( .1 ) ) seems to play a big role in understanding wot's going on here..
............One last useful observation here: One can of course do just VBA . Following on from that last code line, a classic example given is using VBA to get the=Evaluate ( " = A1 ") . The corresponding mainly or exclusively VBA Code Evaluate would be
Excel Spreadsheet =A1 = Evaluate("=" &ws.Range("A1").Address& "")'Explicit Version
Excel Spreadsheet =A1 = Evaluate("" &ws.Range("A1").Address & "")'Implicit Default
A characteristic of theVBA Evaluate Method, ( or rather The building of its String Argument ), which can be helpful in particular when trying to understand the tricky Theme of the different type of quotes in a long complicated string Argument, is that the pair of effectively empty VBA Evaluate quote pairsused in conjunction with an ampersand to link The Spreadsheet"world" to theVBA CodeWorld can have any amount of spaces between them with no effect on the code line.
. So that last line May be rewritten
Let ws.Range("B2").Value = Evaluate("big empty space" & ws.Range("A1").Address& "big empty space") '
. As long as there is something in the total string that can be evaluated this will not error. The explanation would be that within theVBA Evaluate " "pair nothing is being evaluated. It is a dead space.
There will be situations where this can help later to identify which quotes are which, or to which "world" they belong**................ so further
...................................

. To try and maintain some relevance to this Thread, let me say i have a simplified start point in A1 and in attempt to get just the start of the Final output ( originally intended in sheet2 ) in the same sheet in I1
Using Excel 2007

_Row\^Col	A	B	C	D	E	F	G	H	I
1	1								1;"

EvaluateQuotes

. The code might be useful to follow stepping through in F8. I put the code Here, as well as in the last post here of these rambling Posts...
Post 4
http://www.excelfox.com/forum/f17/appendix-thread-codes-for-other-threads-html-tables-etc-2056/#post9507

( and A file with most of the codes in I also upload.. )

...........................

**DocAElstein** · 09-17-2015, 03:05 AM

. 2) Some Detailed code line anylysis

. VBA will always try fundamentally to pair up quotes and see a string within it. And in the simplest Evaluate example given we had then the basic definition of the Evaluate Method and a simple string within the(“___________”) looking like that in a spreadsheet cell is what the Evaluate Method is happy to “Evaluate”.
. The further definition by me proposed requires then full understanding of gluing the combination of things together along witht he tricky allowance for quotes. I have tackled it before ( ( .5 ) ) .....
.. At the end of the day , or thread., I want to produce a string, ( by wotever combination ofSpreadsheet and &and & andVBA code) which Excel will see as it does see what is written in a cell.

. This simplest example as in the references at the end of this Thread I have done many times now:

.10 - Excel Spreadsheeti in cell H1 =A1 &A1 -------- VBA =Evaluate ("= A1"&"&"& "A1")' Result in H1 is 11

And likewise, for a space between, ( or rather for the OP’s case a ; between , or at the end

. 20 - This was found not to work: VBA =Evaluate(" = A1" & "&"" & ";" & ""&" & " A1 ")In fact it gave a syntax error. It appears a conflict with the generalEvaluate ( String expression ) – a sort ofunmatched pair of quotes..........

then further , I confess slightly by trial and error, this did work...

. 30 - ExcelSpreadsheet =A1& ";"&A1------------VBA = Evaluate("=A1" &"&"";""&"& "A1"") Result1;1
. 40 - Excel Spreadsheet =A1& ";"------------VBA = Evaluate("=A1" &"&"";""") Result 1;

The start of the whole understanding for me comes with consideration of the intimacy the"" in"&"";""&" . Immediately after "&"seems to be still in thespreadsheet “world “ and sees the “or rather the total“;“ Any**Attempt to break that pair with a space between errors. Indeed Debug.Print in the Immediate Window shows me=A1&";"&A1 or =A1&";" . However this only works with the matched end of "" and missing out one" puts a spanner in the works again........
. However my reasoning was / is that I can break in and out of VBA World at any text Point as usual with" & "

. 50 - Excel Spreadsheet =A1& ";"------------VBA = Evaluate("=A1" &"&"";" & """")

And this is fine as an alternative Evaluate string. Result in cell I1is 1;

**DocAElstein** · 09-17-2015, 03:06 AM

The next few explanations I confess I do not understand my own logic but which never the less seems valid?!?
Somehow the logic so far suggested to me that I could happily introduce a"" ""grouping at some Points such as

. 60 ------------------------------------------- VBA = Evaluate("=A1" & "&"";"" """ & """")
. 70 (or this---------------------------------------- VBA = Evaluate("=A1" & "&"";"";""" & """") )
.It seemed ok with this syntaxly but errored ( Spreadsheet =A1&";" "" or =A1&";" "")

This however was OK syntaxly and did not error, giving a last quote in the final output
. 80 ------------------------------------------- VBA = Evaluate("=A1" & "&"";""""" & """")

.. So some warped thinking by me was that I have with this new 4 Spreadsheet grouping"""" introduced a pair of expectable Going in when I am already in and out again ofSpreadsheetworld. But I have done nothing and have no completed String. Maybe I have fooled VBA into bringing up a " , the basic thing it does to define a string, but then abruptly stopped it , not completing a string, having no string. It gets "hung up" at the " point and givers me a " .
.
. well surprise, surprise
. 90-- VBA =Evaluate("=A1" &"&"";""" & """" & """")
........does not error .. and Result 1;" !!!!!!!!!!!!!!

.... Further more Debug.Print ( which in my code i used all along to get "what VBA sees" - so what isSpreadsheetworld)
GivesSpreadsheet=A1&";"""
...............................