inspiration

텍스트를 정렬하고 짜르고 붙이는 건 노가다가 빠른지 코드를 짜놓는게 좋은지 판단이 서지 않을 때가 많다.
문득, 문장중 한 단어를 고르면, 그 단어 뒤만 남게 되는 코드를 짜보고 싶었다.



execution

  • 텍스트 영역을 선택 하고,
  • 매크로 실행, 원하는 문자를 입력
  • 해당 영역의 우측셀에 해당 문자 이후 자동 복사

myimg



Sub kk()

    Dim targetRange As Range
    Dim c As Range
    Dim myWord As String
    Dim pos As Long
    Dim cellText As String

    ' Verify whether a valid cell range is selected
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a valid cell range before proceeding."
        Exit Sub
    End If

    Set targetRange = Selection

    ' Request input text from the user
    myWord = InputBox("Please enter the character or text to be used as a reference.")

    ' Check for empty input
    If myWord = "" Then
        MsgBox "No input was provided. The process has been terminated."
        Exit Sub
    End If

    ' Process each selected cell
    For Each c In targetRange

        cellText = CStr(c.Value)

        ' Determine whether the input text exists in the cell
        pos = InStr(1, cellText, myWord)

        If pos = 0 Then
            MsgBox "The specified text does not exist in the selected cell." & vbCrLf & _
                   "Cell address: " & c.Address
            Exit Sub
        End If

        ' Return the text following the first occurrence to the right-hand cell
        c.Offset(0, 1).Value = Mid(cellText, pos + Len(myWord))

    Next c

    MsgBox "The operation has been completed successfully."

End Sub


끝.