Excel VBA userform paste text in expanded format not just to one cell

0 votes

I'm not sure if the title is the right way to address the question, but anyway,

The user of my userform pastes a block of data into it. I've managed to correctly copy the data from the form so far into a single cell, but I want the data to paste so that it appears as though the user right-clicked and expanded it into numerous cells.

Exists a way to accomplish this? Bad explanation, I know; for clarification, view the screenshots.

Private Sub IPButton2_Click()
ActiveWorkbook.Sheets("Site_IP_List").Activate
InputForm.Hide
Dim text As String
text = TextBoxIPData.text
Range("D1").Value = text

Form: Form Image

What I'm looking for: Right ClickIntended Result

What it's doing: Form Result

I have tried different ways to express the data and have it input, but it all ends up stuck in one cell.

Jan 21, 2023 in Others by Kithuzzz
• 38,020 points
598 views

1 answer to this question.

0 votes

It should work if you simply supply your string to this sub in place of its existing sValue. A few items might need to be changed to fit your data.
One risk of presenting photographs rather than text is simply that.
You might need to add a leading zero back in after the trim(txtstr) line if there are several of them. Beginning at the top: " " & TxtStr & TxtStr

Option Explicit
Sub TextToArrayToRange()
    
    Dim RG As Range         'Output range
    Dim TxtArr1             'Text Array 1 (broken into lines)
    Dim TxtArr2             'Text Array 2 (broken into words)
    Dim TxtStr As String    'Original String
    Dim LB As String        'LineBreak
    Dim Y As Long           'Iterate Y axis position
    Dim X As Long           'Iterate X axis position
    Dim lRows As Long       'Rows Count
    Dim lCols As Long       'Columns Count
    
    ' > Choose your linebreak character chr(13), chr(10)
    LB = Chr(10)
    
    ' > Choose output range
    Set RG = Sheet1.Range("B2")
    
    ' > Make txtstr = your text string
    TxtStr = "TEST-TEST-1 AA:BB:CC:DD:EE:FF 100.100.100.100" & LB & _
            "TEST-TEST-2 AA:BB:CC:DD:EE:FF 100.100.100.100" & LB & _
            "TEST-TEST-3 AA:BB:CC:DD:EE:FF 100.100.100.100" & LB & _
            "TEST-TEST-4 AA:BB:CC:DD:EE:FF 100.100.100.100" & LB & _
            "TEST-TEST-5 AA:BB:CC:DD:EE:FF 100.100.100.100" & LB & _
            "TEST-TEST-6 AA:BB:CC:DD:EE:FF 100.100.100.100" & LB & _
            "TEST-TEST-7 AA:BB:CC:DD:EE:FF 100.100.100.100"
    TxtStr = Trim(TxtStr)   'Remove dupe spaces
    
    'Debug.Print TxtStr
    TxtArr1 = Split(TxtStr, LB)
    lRows = Len(TxtStr) - Len(Replace(TxtStr, LB, ""))
    lCols = Len(TxtArr1(0)) - Len(Replace(TxtArr1(0), " ", ""))
    
    ReDim TxtArr2(0 To lRows, 0 To lCols)
    
    For Y = 0 To lRows
        'Debug.Print TxtArr1(I)
        For X = 0 To lCols
            TxtArr2(Y, X) = Split(TxtArr1(Y), " ")(X)
            Debug.Print TxtArr2(Y, X)
        Next X
    Next Y
    
    Set RG = RG.Resize(lRows + 1, lCols + 1)
    RG = TxtArr2
    RG.EntireColumn.AutoFit
    
End Sub

Current Output:

enter image description here

answered Jan 21, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,540 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,720 points
3,558 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,720 points
1,159 views
0 votes
1 answer

Append same text to every cell in a column in Excel

Solution All your data is in column A ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 63,720 points
4,536 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
929 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
1,926 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
1,682 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,720 points
1,231 views
0 votes
1 answer

Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

Use a LineFeed character to create a ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,720 points
991 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,720 points
620 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP