I managed to extract data from a wrapped cell from a workbook and sending that data to another workbook. The wrapped cell has text going line by line and I'm using split text to iterate through each one to get the data I want. The problem is after its done iterating through that wrapped cell it doesn't go to the next column. I would like for it to go to the next column and repeat the process as I just mentioned.

I do know that the variable I set that is called splitText has a range showing as .Range("I3"). So I know that this is the range that is set. I just don't know how to code it to go to next column. The data from the wrapped text is in column I3. I need my script to go to I4 and so on after the extraction is done.

My code is below:

    Sub Get_Data_From_File()
        Dim FileToOpen As Variant
    '    Dim i&, s, v
        Dim rng As Range
        Dim lr As Long
        
        Dim value As String
        Dim x As Variant
        Dim i As Integer
        Dim pos1 As Integer
        Dim pos2 As Integer
        
            
        'Variable declaration
        Dim sWorkbook As Workbook
        'Create New Workbook
        Set sWorkbook = Workbooks.Add
            
        'Save Above Created New Workbook
        sWorkbook.SaveAs Filename:="C:\Users\username\Desktop\file.xlsx"
        FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")

        Set OpenBook = Application.Workbooks.Open(FileToOpen)
   
        Set cRange = OpenBook.Worksheets("my-worksheet").Range("I3:I10")
        
        For Each column In Range("A3:A4")
            splitText = Split(OpenBook.Worksheets("my-worksheet").Range("I3").value, Chr(10))
            Debug.Print value
            counter = 3
            
            For i = 0 To UBound(splitText)
                value = splitText(i)
                pos1 = InStr(value, "show")
                pos2 = InStr(value, "more")
                myArray = Split(value)
                
                
                If pos1 = 1 Or pos2 = 1 Then
                    Debug.Print value
                    If InStr(1, value, 9, 1) Then
                        textWithNumberUse9 = Replace(value, "show", 1)
                        textWithNumberAnd9 = Replace(value, "more", 1)
                        
                        removeForUse = Trim(Replace(textWithNumberUse9, "and", ""))
                        removeForAnd = Trim(Replace(textWithNumberAnd9, "and", ""))
                        
                                        
                    End If
                    
                    If InStr(1, value, 6, 1) Then
                        
                        textWithNumberUse6 = Replace(value, "show", "")
                        textWithNumberAnd6 = Replace(value, "more", "")
                        
                        removeForUse = Trim(Replace(textWithNumberUse6, "and", ""))
                        removeForAnd = Trim(Replace(textWithNumberAnd6, "and", ""))
                        
                        
                    End If
                    
                                        
                    If InStr(1, value, "show", 1) Then
                        Workbooks("Test.xlsx").Worksheets("Sheet1").Range("D" & counter).value = removeForUse
                    End If
                    
                    If InStr(1, value, "more", 1) Then
                        Workbooks("Test.xlsx").Worksheets("Sheet1").Range("D" & counter).value = removeForAnd
                    End If
                    
                    counter = counter   1
                                       
                        
                 End If
                
            Next i
        Next column

CodePudding user response：

Change the splitText = ... line to this

splitText = Split(OpenBook.Worksheets("my-worksheet").Range("I" & column.Row).value, Chr(10))

(given how this looks, you should really reconsider the name of your column variable...)