Excel - Increment to the Next Column Loop Counter

[dj44]

Evening folks,

I am trying to increment the next column in Excel, but it just doesnt seem to work

How can i make it increment to the next column after it has completed the loop Start at Column A > B> C > D on each journey through the loop

                          
    Do While .Execute(FindText:=arrWords(i))
           
         
    iRow = xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).row + 1 
    
    xlSheet.Cells(iRow, 1) = oRng.Text
       
    oRng.Collapse 0

    Loop
  
    End With

    Next

The first time it goes to Column A, now next time on the loop go to Column B, C, D etc
Now I added many loops to it, but nothing seemed to work to go to the next column.

Where exactly am i supposed to increment the column counter, i added J , K letters as long - to make a column loop , but i admit defeat

Google has been of no help today

please do advice on why this Column wont increment

thank you

[mdmackillop]

Can you post the rest of your code?

[dj44]

Evening M,

this is good man Greg's code.

Well i got stuck in to the exel side, and i wanted to know how to make this column increase.

 Dim xlApp As Object, xlBook As Object, xlSheet As Object
    Dim oRng As Range
    Dim lngIndex As Long, lngRow As Long
    Dim oDoc As Document
    Dim arrWords() As String
    
    
    arrWords = Split("Test1,Test2,Test3", ",")
    
    Set xlApp = GetObject(, "Excel.Application")
    Set xlSheet = xlApp.Sheets("Sheet1")
    
    
    Set oRng = ActiveDocument.Range
    For lngIndex = 0 To UBound(arrWords)
        Set oRng = ActiveDocument.Range
        With oRng.Find
            Do While .Execute(FindText:=arrWords(lngIndex))
   
                lngRow = xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).Row + 1
                xlSheet.Cells(lngRow, 1) = oRng.Text
                oRng.Collapse 0
            Loop
        End With
    Next

this was taking some words from WORD document and pasting them to excel.

I wanted to paste each set of the array words to an individual column.

All the Test1's > Column A

Test2's > Column B

etc

But i researched everything today but even stack didnt have any column increment code for me

[SamT]

All the Test1's > Column A

Test2's > Column B

lngRow = xlSheet.Cells(xlSheet.Rows.Count, lngIndex + 1).End(-4162).Row + 1

[mdmackillop]

Untested but try

Sub Test()Dim xlApp As Object, xlBook As Object, xlSheet As Object
Dim oRng As Range
Dim lngIndex As Long, lngRow As Long
Dim oDoc As Document
Dim arrWords() As String


    arrWords = Split("Test1,Test2,Test3", ",")
     
    Set xlApp = GetObject(, "Excel.Application")
    Set xlSheet = xlApp.Sheets("Sheet1")
    Set oRng = ActiveDocument.Range
    For lngIndex = 0 To UBound(arrWords)
        Set oRng = ActiveDocument.Range
        With oRng.Find
            Do While .Execute(FindText:=arrWords(lngIndex))
                lngRow = xlSheet.Cells(xlSheet.Rows.Count, 1 + lngIndex).End(-4162).Row + 1
                xlSheet.Cells(lngRow, 1).Offset(, lngIndex) = oRng.Text
                oRng.Collapse 0
            Loop
        End With
    Next
End Sub

[dj44]

Thanks M, and Sam,

that did a marvelous job

I added so many i,j,k,longindex and lngrows in those few lines and various combinations thereof, in the end made a rights dogs dinner of it,
dont tell Greg he would have told me off

I extracted so many words and the column became very long with words.

I had to scroll all the way down a few thousand rows to the bottom and drag the words up and excel kept bouncing so i lost some words in the process, well who can go that far down

but this has nicely put them in their rightful place

thank you very much gentlemen and a good evening folks