Sending text into the cell below

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
Hi,

I'm making a program in Visual Basic which sends some data to an Excel
Click to expand...
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?
 
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
 
Back
Top