Loop adding text to the columns startign at the wrong column number

[Legacy 93538]

Hi

I have ths macro which should be looping through the two sheets and for each sheet it should be looping through the columns in the array and for each one putting a vlookup formla into Row 2 and starting at column B and then for each column in the array it should go to a new column. And it is doing this but its starting at column S instead of B

    NCol2 = 2
    For Each shName2 In Array("DE", "PL")
        For Each sht In Array(27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43) 'Loop through the vlookup cells and autofill the rest of the columns
            With PPNew.Sheets(shName2).cells(2, NCol2)
                .Formula = "=IF(A2=0,0,VLOOKUP($A2,ConvertManual!$AA$1:$BB$2000," & sht & ",FALSE))"
                .AutoFill .Resize(500)
                NCol2 = NCol2 + 1
            End With
        Next
        PPNew.Sheets(shName2).cells.Copy
        PPNew.Sheets(shName2).Range("A1").PasteSpecial Paste:=xlPasteValues
    Next shName2

Can anyone help me as i cant see way its starting at column S?

Thanks

Jessicaseymour

 

[Andrew Poulsom]

Why is cells in lower case?

 

[Legacy 93538]

Hi

Is it being in lower case important? should it be upper case?

I have found out why it is starting in S.

In the first sheet its starts at B and ends at R and then in the second sheet it starts again at S

 

[Andrew Poulsom]

That's because you haven't reset NCol2 before Next shName2.

I would expect the VBE to convet cells to Proper Case.

 

[Legacy 93538]

Hi

Thanks working now!