Looping Question

For N = 1 To 242
            Range("A8").Select
            CellNum = "R[N+8]C1"
            ActiveCell.Offset(N, I - 2).Select
            ActiveCell.FormulaR1C1 = "=VLOOKUP(VLOOKUP(Range((CellNum,'$_Names'R3C2:R365C3,2,FALSE),Sheets(I)!R9C2:R150C4,3,FALSE)"
        Next N

This snippet of code is supposed to run inside another for loop that loops through the integer "I" where "I" is the worksheet number. The first loop steps through all worksheets in the file and copies information from each and pastes into the first "overview" sheet. The second loop, shown above is supposed to step through the values in each row and perform a nested VLOOKUP each time. I am having trouble with the syntax for this however. I think my problem is having the sheets object inside the vlookup formula. Help would be greatly appreciated. Thank you!!