Special concatenation

[snb]

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

[DocAElstein]

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

[snb]

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

[DocAElstein]

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

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

[snb]

Or:

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

[DocAElstein]

Or:

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:

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

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

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

[snb]

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:

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

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

[DocAElstein]

Hi
. 1 )

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.

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

[snb]

@Doc

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

[DocAElstein]

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