EXCEL vba 將cells中多行文字轉到單行cells

前言:在監工日報中常有將一儲存格中輸入多行文字(要輸入下一行即按[alt]+[Enter])以下VBA程式碼即將多行文字轉到單行儲存格中,如果有3行即會轉到3個儲存格中,一行一格;

Private Sub CommandButton1_Click()
Dim st1 As String
Dim j As Integer

j = 0
st1 = ""
For i = 1 To Len(Cells(1, 1))
If Asc(Mid(Cells(1, 1), i, 1)) = 10 Then
j = j + 1
Cells(j, 2) = st1
st1 = ""
Else
st1 = st1 & Mid(Cells(1, 1), i, 1)
End If

Next i
j = j + 1
Cells(j, 2) = st1
MsgBox "ok"
End Sub