A
andrew.m.brown
I have an interesting issue. I'm working with a Lotus Notes Database
that I'm trying to extract data from. I'm using Crystal Reports and
NotesSQL to drop the necessary data to excel. The issue is that Lotus
stores multiple lines of data in one field, separated by a line break
(chr(10)). I would like to write a macro that could break the data in
this column out into separate rows whenever a line break is found. I
was then planning to use ASAP Utilities' fill blanks macro to populate
any empty cells.
I've tried to do this on my own based on what I've read in this forum,
but have not had much success. What I have so far is:
Public Sub separatecells()
Dim s As String, s1 As String, s2 As String
Dim iloc As Long
s = ActiveCell.Value
iloc = InStr(1, s, Chr(10), vbTextCompare)
If iloc <> 0 Then
s1 = Trim(Left(s, iloc - 2))
s2 = Trim(Right(s, Len(s) - iloc))
ActiveCell.Offset(0, 0) = s1
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(0, 0) = s2
Else
ActiveCell.Offset(0, 0).Value = s
End If
End Sub
This does not loop through the selected cells because I'm having
issues with my loop because of IF statement. I could provide a sample
file if someone would like to help but needs additional information.
Any assistance would be greatly appreciated!!!!
that I'm trying to extract data from. I'm using Crystal Reports and
NotesSQL to drop the necessary data to excel. The issue is that Lotus
stores multiple lines of data in one field, separated by a line break
(chr(10)). I would like to write a macro that could break the data in
this column out into separate rows whenever a line break is found. I
was then planning to use ASAP Utilities' fill blanks macro to populate
any empty cells.
I've tried to do this on my own based on what I've read in this forum,
but have not had much success. What I have so far is:
Public Sub separatecells()
Dim s As String, s1 As String, s2 As String
Dim iloc As Long
s = ActiveCell.Value
iloc = InStr(1, s, Chr(10), vbTextCompare)
If iloc <> 0 Then
s1 = Trim(Left(s, iloc - 2))
s2 = Trim(Right(s, Len(s) - iloc))
ActiveCell.Offset(0, 0) = s1
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(0, 0) = s2
Else
ActiveCell.Offset(0, 0).Value = s
End If
End Sub
This does not loop through the selected cells because I'm having
issues with my loop because of IF statement. I could provide a sample
file if someone would like to help but needs additional information.
Any assistance would be greatly appreciated!!!!