Having some trouble using increments in While...Wend loop

[hdman]

hi,

I'm having trouble incrementing inside a While...Wend loop.

i = 0

While <condition>

<statement>
i = i + 2
<statement>

Wend

I am able to execute my While statement properly but I am only able to do the first increment of i with this piece of code and then it stops after. Any help would be much appreciated. Thanks.

[Rabbit]

That depends on what the condition is.

[hdman]

Here is my code. I am copying data from one spreadsheet to a new spreadsheet (the code is run from the new spreadsheet). I am trying to format the text in the new spreadsheet so that there is some space between tables and so that the column header text is wrapped. I am trying to increment i to do the formatting but it only seems to work the first time:

Sub findRanges()

    On Error Resume Next
        
    Dim Wb1 As Workbook, wb2 As Workbook
    Dim startRow As Long
    Dim endRow As Long
    Dim i As Integer
    LookupString = "Unique Well ID"
    
 
    'copy from workbook 2'
    Set wb2 = Workbooks.Open("C:\Users\hdhingra\Desktop\Example\EX.xlsx")

    'To This
    Set Wb1 = ThisWorkbook
    
    startRow = 1
    endRow = wb2.Sheets(1).Range("A1").EntireColumn.find(What:=LookupString, After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row - 1
    
    'paste production data of first well to template***
    
    wb2.Sheets(1).Range("A" & startRow & ":H" & endRow).Copy Wb1.Sheets(1).Range("C4" & ":J" & endRow + 3)
    Wb1.Sheets(1).Range("C6:J6").WrapText = True
        
    '*************************************************************************************************************************************
    startRow = endRow + 1
    i = 0
       
    While wb2.Sheets(1).Range("A" & startRow).value <> "" And i <> "20"
    
    endRow = wb2.Range("A" & startRow).EntireColumn.find(What:=LookupString, After:=Range("A" & startRow), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row - 1
        
    'Range("K2") = endRow
    'Range("K3") = startRow
    'Range("K4") = wb2.Sheets(1).Range("A1").End(xlDown).Row
        
        'find last row of data
        If Err.Number <> 0 Then
            endRow = wb2.Sheets(1).Range("A1").End(xlDown).Row
        End If
       
    i = i + 2
    wb2.Sheets(1).Range("A" & startRow & ":H" & endRow).Copy Wb1.Sheets(1).Range("C" & startRow + 3 + i & ":J" & endRow + 3 + i)
    Wb1.Sheets(1).Range("A" & startRow + 5 + i & ":J" & startRow + 5 + i).WrapText = True
    
    startRow = endRow + 1
   
    Wend

Thanks.

[hdman]

There is also an End Sub at the end, sorry....

[Rabbit]

I'm surprised it runs even once. On line 30 you set the start to be equal to the row after the last. It should never even loop since you're checking to see if the row after the last row has any data, which it never should.

[hdman]

Actually, with reference to the spreadsheet that I am copying data from; the point where I check to see when I have reached the last row of data is line 45.

in line 30, i defined the variables startRow and endRow. the spreadsheet that i am copying from actually has several tables (same number of columns and rows).

I use the LookupString variable to help identify the row number at which a new table begins. Thats what I use to define startRow = endRow + 1.

Still stuck :(

[Rabbit]

It would have helped to know that earlier. The problem is line 35, there is no range property on a workbook. Compare that line to line 20.

[hdman]

That's it, Thanks a bunch!!!!!!

[Rabbit]

No problem, good luck on the rest of your project.