Printable View
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
. 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…"Quote:
Originally Posted by snb
( 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
. This seems to work, as long as The Elements are Dimensioned as a String.Quote:
Originally Posted by snb
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 0,00
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...
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
Ahh, interesting that formatting the cell like that works with yoouur "Clipboard method"Quote:
Originally Posted by snb
. 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 BEER 0.00 VODCA 1.23 COGNAC 0.00
Sheet1
then take out this line
'ws1.Range("D2:D" & lr & "").Value2 = "0.00"
to get:
Using Excel 2007
Row\Col 1;"BEER";"1";"0.00";"1";"1";"1";"";"";""; 2;"VODCA";"1";"1.23";"1";"1";"1";"";"";""; 3;"COGNAC";"1";"0.00";"1";"1";"1";"";"";"";
Sheet2
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:
orCode: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
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 ). 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 .Quote:
Originally Posted by snb
. 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
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.
Waren sie schon da..Quote:
Originally Posted by snb
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?