Special concatenation

1 Attachment(s)

Goog Day,

Need some help for some special concatenation.

Attachment 1786

The date look like:
AA NAME CC DD EE FF GG HH1
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

and need a VBA cod to do this:

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";"";

in other sheet.
Thank you.

Because this forum's comment processor "eats" multiple spaces, it is hard to tell what columns your original data is in. When I copy/paste it into a worksheet, the column labeled HH1 is empty. Is it possible for there to be blank cells within the data... for example, could cells C2, D2, F2, G2 with E2 and H2 being empty, or will all filled cells always be contiguous across the row?

1 Attachment(s)

Thanks for the reply guru Rick,

Indeed column H is very often empty. Therefore I need to defend this sign "" (double quote).
It is not possible to Cn, Dn, En, Fn, and Gn to be empty. And if there are empty rows means that no longer of interest. The cells can be only 0 or a positive number ...

Usual I have between 2000 to 7000 rows.

Thank you

There was a minor discrepancy in the output between what you posted in Message #1 and what you showed in on Sheet2 of the file you attached for the fourth field... Message #1 shows the fourth field as being formatted as number with two decimal places whereas your file shows you want that field to be a 3-digit whole number with leading zeroes. I assume Message #1 was correct; here is a macro for you to consider (I assumed sheets named Sheet1 and Sheet2 like you showed in the file you attached)...

Code:

Sub Booze() Dim R As Long, Data As Variant, Results As Variant Data = Sheets("Sheet1").Range("A2:H" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).row) ReDim Results(1 To UBound(Data), 1 To 1) For R = 1 To UBound(Data) Data(R, 4) = Format(Data(R, 4), "0.00") Results(R, 1) = Join(Application.Index(Data, R, Split("1 2 3 4 5 6 7 8")), """;""") Results(R, 1) = Replace(Results(R, 1), """", "", , 1) & """;" Next Sheets("Sheet2").Range("A1").Resize(UBound(Results)) = Results End Sub

Extraordinary, excellent

Yes, message #1 was correct.

Rick you are the best of the best.

Thank you very much.

another approach:

Code:

Sub M_snb() Sheet1.UsedRange.Offset(1).Copy With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .GetFromClipboard sn = Filter(Split(Replace(Replace(Replace(.GetText, vbTab, Chr(34) & ";" & Chr(34)), ";" & Chr(34) & vbCrLf, "|" & Chr(34)), vbLf, Chr(34) & "|" & Chr(34)), "|"), ";") End With Sheet2.Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn) End Sub

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.

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:

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

_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

_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";"";

Thank you Alan.

Quote:

Originally Posted by Ingolf

Thank you Alan.

you is welcome,
Alan

Code:

Sub M_snb() Sheet1.UsedRange.Offset(1).Copy With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .GetFromClipboard sn = Split(Replace(Replace(.gettext, vbTab, """;"""), vbCr, """" & vbCr), vbCrLf) For j = 0 To UBound(sn) sn(j) = Replace(sn(j), """", "", , 1) Next End With Sheet2.Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn) End Sub

Quote:

Originally Posted by snb

CODE......

. I must get around to learning / practicing that "thing" of yours as well sometime - using the clipboard, then manipulating the text string with a "One liner…"
( gives the results , just not the 0.00 - but neither does mine really, and looping like Rick does would take away the nice "one liner " bit.. shame

"0.00" can't be returned, since the value =0, only the formatted presentation of that value is 0.00

Quote:

Originally Posted by snb

"0.00" can't be returned, since the value =0, only the formatted presentation of that value is 0.00

. This seems to work, as long as The Elements are Dimensioned as a String.

Code:

Sub FormatInArray() 'Worksheet info Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Sheet1") 'Sheet Info Dim Data(1 To 2) As String Let Data(1) = 0 Let Data(2) = 0 Let Data(1) = Format(Data(1), "0.00") Let ws1.Cells(2, 4).Value = Data(1) Let ws1.Cells(3, 4).Value = Data(2) End Sub

Using Excel 2007

_Row\^Col
D

2
0,00

3

0

Sheet1

. But I guess that is what you are saying, sort of - To do with Formatting.. or Wotever (Whatever)

. I just did not realise that one can Put Format into Part of an Array in that way such that the output comes out in the spreadsheet like that. This is wot (what) I learned from Rick's code. That could come in handy as another "work around" I often need to cure problems I get with excel annoyingly changing comers to points, and / or changing numbers to date formats etc...

_Row\^Col	D
2	0,00
3	0

Or:

Code:

Sub M_snb() Sheet1.UsedRange.Columns(4).NumberFormat = "0.00" Sheet1.UsedRange.Offset(1).Copy With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .GetFromClipboard sn = Split(Replace(Replace(.gettext, vbTab, """;"""), vbCr, """" & vbCr), vbCrLf) For j = 0 To UBound(sn) sn(j) = Replace(sn(j), """", "", , 1) Next End With Sheet2.Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn) End Sub

Quote:

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

_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

_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";"";"";"";

I do not understand why you use 'Let'. The use of it has become redundant for more than 20 years.

To 'walk' in your approach:

Code:

Sub M_snb() sn = Sheet1.Cells(1).CurrentRegion For j = 2 To UBound(sn) For jj = 1 To UBound(sn, 2) c00 = c00 & IIf(jj = 1, sn(j, jj) & ";", """" & IIf(jj = 4, Format(sn(j, jj), "0.00"), sn(j, jj)) & """;") Next c00 = c00 & vbLf Next MsgBox Replace(c00, ";" & vbLf, vbLf) End Sub

or

Code:

Sub M_snb_002() sn = Sheet1.Cells(1).CurrentRegion For j = 2 To UBound(sn) c00 = c00 & """" & vbLf & Replace(Join(Application.Index(sn, j), """;"""), """", "", , 1) Next MsgBox Replace(Mid(c00, 3), """0""", """0,00""") End Sub

Hi
. 1 )

Quote:

Originally Posted by snb

I do not understand why you use 'Let'. The use of it has become redundant for more than 20 years……

. 1 a) Up until about 2 years ago, the last time I had the slightest thing at all to do with Computers was about 25 years ago. My first experience at all with a pc or “Windows” things was 2 years ago .

. 1 b) While learning VBA ( or rather OOP ) it helps me distinguish between, ( and the reasons for ) things that can be Let , things that must be Set and things that must have neither.

Quote:

Originally Posted by snb

…..
To 'walk' in your approach:.....

..
Thanks very much for that. I intend to go through that in some detail when I have the time to do it justice, and I expect learn much from it. ( But whether I will really get to grasp the how VBA is seeing its evaluate strings and quotes etc..... I am not sure if anyone really knows, like i am finding to my surprise with most things to do with software, especially Excel VBA these days!!. Maybe I will be surprised when I work through what you have kindly given.. )

Thanks again
Alan

@Doc

If you prefer we can communicate in your native tongue if we exchange views on Excellösung.

Quote:

Originally Posted by snb

.....
If you prefer we can communicate in your native tongue if we exchange views on Excellösung.

Waren sie schon da..
Bzw. Hier
http://www.office-loesung.de

mir scheint als ob kein anderer Mensch war, seit Jahren,
es war aber nur eine schnell “Google”
evtl. haben sie wo oder was anderes gemeint?

Stimmt doch, meinte ich auch: Office-Loesung.de - Forum zu Microsoft Excel, Outlook, Word, PowerPoint, Access

1 Attachment(s)

Quote:

Originally Posted by snb

Stimmt doch, meinte ich auch: Office-Loesung.de - Forum zu Microsoft Excel, Outlook, Word, PowerPoint, Access

ich habe keine Ahnung was da soll ist / ist …. Ich komme Lieder nicht mit, stehe auf die Leiter denk ich…was sol ,dann das ….. Aktuelles Datum und Uhrzeit: 03. Mai 2014, 13:06

scheinbar ist das Forum Zeit lange abgestellt / Zu / geschlossen, Tote Hosen ??

http://www.excelfox.com/forum/attach...tid=1797&stc=1

oder meinen sie das:
http://www.office-loesung.de/p/

diese:

Office Forum â€¢ Forum anzeigen - Excel Forum (provisorisch)

Quote:

Originally Posted by snb

diese:

Office Forum â€¢ Forum anzeigen - Excel Forum (provisorisch)

hat schon bemerkt, Danke.

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.

Quote:

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

1 Attachment(s)

. 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.. )

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

. 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;

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&";"""
...............................

. 3 ) Some conclusions “Quotes in VBA” ...

. Taking the lastcode lines, - I may happily remove the two extra" & "which as discussedare just linking text elements.. to give simplified finally

. 100 .-- VBA =Evaluate("=A1" &"&"";""""""")

.. my final solution isI have ( I think ) some theoretical argument given to a convention to produce aquote in a VBA Evaluate string, as well as seeing a similar logic for theSpreadsheet equivalent, that is to say for theSpreadsheet an extra "" to what one would usually write : Similarargument again : once in the String you write in the cell for example
=";" ( Result in cell is ; )
Or for an extra “ youwrite
=""";" ( Result in cell is "; )

SummaryAdding a Quote After a text bit that should beoutputted as Final result:

. 3 a) VBA Evaluate ( Wot you write in the String for the Evaluate ):
.
. ""TextBit""""""( The last" is removed from my above example as that is just thelast end “ at the end “) ). In words: the Text Bit and 6 quotes all green . ( Note if already within a long string with the "start text" and "stop text" pair &""____""& then 4 qoutes are needed """" )
Or
for no text 8 quotes all green.
. For nothing but a quote in a cell using evaluate,8 quotes all green in the middle, two blue quotes, one on each end, 10 intotal!!. .. ("""""""""") _____ ("" & """""""""" & "")
.
. 3 b ) Spreadsheet World ( Wot you write jn the cell ):
.
. TextBit""
.
......................
. A lot of people know those two summaries, But I do not think they have a theory as to why...( I have at least one I think has some logic in ... maybe.. ? )

. Thanks for watching
. Alan
.....

P.s.
@ snb

Quote:

Originally Posted by snb

.......

Quote:

Originally Posted by snb

To 'walk' in your approach:

Code:

........ For j = 2 To UBound(sn) For jj = 1 To UBound(sn, 2) c00 = c00 &IIf(jj = 1, sn(j, jj) & ";", """" &IIf(jj = 4, Format(sn(j, jj), "0.00"), sn(j, jj)) &""";") ....... MsgBox Replace(c00, ";" &vbLf, vbLf) ........

or

Code:

.... For j = 2 To UBound(sn) c00 = c00 &"""" & vbLf & Replace(Join(Application.Index(sn,j), """;"""), """","", , 1) ..MsgBox Replace(Mid(c00, 3),"""0""","""0,00""") ......

….. I think You have very nicely walked me through how to get the stringwhat I would type in a[SIZE=3]Spreadsheet.[COLOR=#222222][FONT="Verdana"]

Code Again, for last few Posts

Code:

Sub EvalutingQuotes() 'Posts from #25 http://www.excelfox.com/forum/f2/special-concatenation-2042/index3.html '---o00o---`(_)`---o00o--- Rem 1) Basics Dim v As Variant Let v = "3" ' Results in a Variant variable containing a string value "3" Let v = 3 ' Results in a Variant variable containing a Long Number 3 ( actually an Integer ? ) Range("I1").Value = Evaluate("=A1") 'Explicit Version Range("I1").Value = Evaluate("=" & Range("A1").Address & "") 'Explicit Version Range("I1").Value = Evaluate("" & Range("A1").Address & "") 'Implicit Default Range("I1").Value = Evaluate(" " & Range("A1").Address & " ") ' Range("I1").Value = Evaluate(Range("A1").Address) 'Common but dangerous variation Rem 2) Detailed code anylysis Dim strEval As String 'String to be used in Evaluate 10 strEval = "=A1" & "&" & "A1": Debug.Print strEval 'gives =A1&A1 Range("I1").Value = Evaluate("" & strEval & "") 'Result Gives 11 in cell I1 20 'strEval = "=A1" & "&"" & ";" & ""&" & "A1": Debug.Print strEval 'gives syntax error 'Range("I1").Value = Evaluate("" & strEval & "") 'errors 30 strEval = "=A1" & "&"";""&" & "A1": Debug.Print strEval 'gives =A1&";"&A1 Range("I1").Value = Evaluate("" & strEval & "") 'Result Gives 1;1 40 strEval = "=A1" & "&"";""": Debug.Print strEval 'gives =A1&";" Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1; 50 strEval = "=A1" & "&"";" & """": Debug.Print strEval 'gives =A1&";" Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1; 60 strEval = "=A1" & "&"";""""" & """": Debug.Print strEval 'gives =A1&";" "" Range("I1").Value = Evaluate("" & strEval & "") 'error 70 strEval = "=A1" & "&"";"";""" & """": Debug.Print strEval 'gives=A1&";";"" Range("I1").Value = Evaluate("" & strEval & "") 'error 80 strEval = "=A1" & "&"";""""" & """": Debug.Print strEval 'gives =A1&";""" Range("I1").Value = Evaluate("" & strEval & "") 'Did not error Gives 1;" !!!!!!!! 90 strEval = "=A1" & "&"";""" & """" & """": Debug.Print strEval 'gives =A1&";""" Range("I1").Value = Evaluate("" & strEval & "") 'Did not error Gives 1;" !!!!!!!! Rem 3) Quotes in VBA ........... 100 strEval = "=A1" & "&"";""""""": Debug.Print strEval 'gives =A1&";""" Range("I1").Value = Evaluate("" & strEval & "") 'Did not error Gives 1;" !!!!!!!! 110 strEval = "=A1" & "&""""""""": Debug.Print strEval 'gives =A1&"""" Range("I1").Value = Evaluate("" & strEval & "") ' Gives 1" 120 strEval = "=A1&""""""""": Debug.Print strEval 'gives =A1&"""" Range("I1").Value = Evaluate("" & strEval & "") ' Gives 1" 130 strEval = "=" & """""""""": Debug.Print strEval 'gives ="""" Range("I1").Value = Evaluate("" & strEval & "") ' Gives " 140 strEval = "=""""""""": Debug.Print strEval 'gives ="""" Range("I1").Value = Evaluate("" & strEval & "") ' Gives " 150 strEval = """""""""": Debug.Print strEval 'gives """" Range("I1").Value = Evaluate("" & """""""""" & "") ' Gives " Range("I1").Value = Evaluate("""""""""") ' Gives " End Sub 'EvalutingQuotes() ---o00o---`(_)`---o00o---

@Alan

Your posts are increasingly unreadable.

If you are interested in 'Evaluate' have a look at:

VBA for smarties How to fill a Combobox / Listbox

Quote:

Originally Posted by snb

@Alan
Your posts are increasingly unreadable...If you are interested in 'Evaluate' have a look at:.....

@snb

. Danke für das Info and hinweisen!
(. Ich probier nur meines beste zu beides verstehen Code die ich benutzen und gleich Mitteilung was ich ( denke ) ich verstehe. )

. Thanks for the info and advice!
(. I just try myself to understand Code I use and at the same time to share what I ( think ) I understand )

Alan