Hi,

I'm making a program in Visual Basic which sends some data to an Excel spreadsheet. This data is numbers separated by full stops (e.g. 3.2.1). When the spreadsheet encounters a full stop, I would like it to put the following number in the cell directly below. I tried using replace (myData,".",vbLf), but this resulted in multiple lines in one cell. I also tried vbCrLf and vbCr but these were no better. I know that if you copy a vertical column of numbers from, say, Notepad into Excel it will put each value into a separate cell in the way I want. How do I replicate this behaviour?

Many thanks,

Jonathan Stratford

Hi Jonathan,

One way,

Sub Test()
Dim sStr As String
Dim Arr As Variant

sStr = "1.2.3.4.5"

Arr = Split(sStr, ".")

Range("A1").Resize(UBound(Arr) + 1) = Application.Transpose(Arr)

End Sub

---
Regards,
Norman


"Jonathan Stratford" <Jonathan wrote in
message ...
Hi,

I'm making a program in Visual Basic which sends some data to an Excel
spreadsheet. This data is numbers separated by full stops (e.g. 3.2.1).
When the spreadsheet encounters a full stop, I would like it to put the
following number in the cell directly below. I tried using replace
(myData,".",vbLf), but this resulted in multiple lines in one cell. I also
tried vbCrLf and vbCr but these were no better. I know that if you copy a
vertical column of numbers from, say, Notepad into Excel it will put each
value into a separate cell in the way I want. How do I replicate this
behaviour?

Many thanks,

Jonathan Stratford

Hi Jonathan,

To add, the Split. function was nnot available prior to xl2k If this is
relevant, you could use Tom Ogilvy's Split97 function:
Public Function Split97(ByVal sIn As String, _
Optional ByVal sDelim As String = ",") As Variant
'By Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sIn, sDelim, """,""") & """}")
End Function

---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi Jonathan,

One way,

Sub Test()
Dim sStr As String
Dim Arr As Variant

sStr = "1.2.3.4.5"

Arr = Split(sStr, ".")

Range("A1").Resize(UBound(Arr) + 1) = Application.Transpose(Arr)

End Sub

---
Regards,
Norman


"Jonathan Stratford" <Jonathan wrote
in
message ...
Hi,

I'm making a program in Visual Basic which sends some data to an Excel
spreadsheet. This data is numbers separated by full stops (e.g. 3.2.1).
When the spreadsheet encounters a full stop, I would like it to put the
following number in the cell directly below. I tried using replace
(myData,".",vbLf), but this resulted in multiple lines in one cell. I
also
tried vbCrLf and vbCr but these were no better. I know that if you copy a
vertical column of numbers from, say, Notepad into Excel it will put each
value into a separate cell in the way I want. How do I replicate this
behaviour?

Many thanks,

Jonathan Stratford