Special concatenation

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