If, then inside a for, next statement?

Hi All,

I have the below code working but not doing everything that I expect. It counts the number of columns on one sheet and copies the headings to rows to another sheet. One thing is that sometimes there are more than 10 headings and there are only room for 10 rows. So after 10 I need to insert rows. Currently the below code counts and inserts rows correctly but for some strange reason it only copies headings to rows for the first 9. What am I missing here?

Thanks,
~RUTH~

Sub test()
Dim NumCol As Long
    Sheets("TREAT data").Select
    NumCol = ActiveSheet.Cells(71, 256).End(xlToLeft).Column - 1  'counts number of measures
    Sheets("Detailed Measures").Select
    For i = 0 To NumCol  'cycle through all the columns of measures
        If i >= 10 Then
            Rows("15:15").Select    'measure #10
            Selection.Insert Shift:=xlDown  'insert a row before measure #10
        End If
        Sheets("Detailed Measures").Range("B6").Offset(i, 0).Value = Sheets("TREAT data").Range("B71").Offset(0, i).Value
    Next i
End Sub

No neeed to select the sheets, try this I'm not able to test it without an example workbook.

  Dim NumCol As Long
    Dim i      As Long
    With Sheets("TREAT data")
        NumCol = Sheets("TREAT data").Cells(71, 256).End(xlToLeft).Column - 1    'counts number of measures
    End With
    With Sheets("Detailed Measures")
        For i = 10 To NumCol    'cycle through all the columns of measures
            Rows("15:15").Insert Shift:=xlDown
            .Range("B6").Offset(i, 0).Value = Sheets("TREAT data").Range("B71").Offset(0, i).Value
        Next i
    End With

Deducting 1 will not prevent the first colmn being used, but the last. Can you attach a small example?

Originally Posted by EnergyEngineer

No I don't think I need with sheets statements. Everything works with my original code. Except when i is greater than 10 and more rows need to be added. Then rows are added but for some reason the headings are not copied.

~RUTH~

The With .. End With is to avoid unnecessary selecting of sheets. Have you tested the code? An example workbook would help.

Hi Guys,

Thanks for you help. Below is new code I am using after your comments. I tried reming the select sheet and un unremming your with sheet idea and it never selects the "Detailed Measures" sheet. Instead it just inserts the rows on the "TREAT data" sheet. I have attached a simple version of the problem. Be aware I can't just copy the whole table and paste as transpose b/c there is actually more data in the column that I want and will add more code once I get this row to work.

Thanks,
~RUTH~

Dim NumCol As Long
Dim i As Integer

    NumCol = Sheets("TREAT data").Cells(71, 256).End(xlToLeft).Column - 1  'counts number of measures
    Sheets("Detailed Measures").Select
    'With Sheets("Detailed Measures")
    For i = 0 To NumCol  'cycle through all the columns of measures
        If i >= 10 Then
            Rows("15:15").Insert Shift:=xlDown   'insert a row before measure #10
        End If
        Sheets("Detailed Measures").Range("B6").Offset(i, 0).Value = Sheets("TREAT data").Range("B71").Offset(0, i).Value
    Next i
    'End With