help! Error in loop

[everettjsj2]

[COLOR="RoyalBlue"]Sub Tabloop_Test3()
    Dim WS_Count As Integer
    Dim I As Integer
    Dim N As Integer
    Dim CellNum As String
    
    Application.ScreenUpdating = False
     
    WS_Count = ActiveWorkbook.Worksheets.Count
    
    
    For I = 3 To WS_Count
        Sheets("Overview").Cells(3, I - 1) = Sheets(I).Range("A1").Value 'Note that we subtract only 1 from I because we are not offsetting.
        
        Sheets(I).Range("B8").Copy Sheets("Overview").Cells(4, I - 1)
        Application.CutCopyMode = False
        
        Sheets("Overview").Cells(5, I - 1) = Sheets(I).Range("A5").Value
            
        Sheets("Overview").Cells(6, I - 1) = Sheets(I).Range("A6").Value
         
         For N = 1 To 242
            CellNum = Sheets("Overview").Cells(N + 8, 1).Value
            Sheets("Overview").Range("A8").Offset(N, I - 2).FormulaR1C1 = "=VLOOKUP(VLOOKUP(' & CellNum & ','RawNames'!R3C2:R365C3,2,FALSE),' & Sheets(I).Name & '!R9C2:R150C4,3,FALSE)"
         Next N
    Next I
    
    Application.ScreenUpdating = True

End Sub[/COLOR]
[\code]

I am getting a 1004 error on the line :[COLOR="SeaGreen"]Sheets("Overview").Range("A8").Offset(N, I - 2).FormulaR1C1 = "=VLOOKUP(VLOOKUP(' & CellNum & ','RawNames'!R3C2:R365C3,2,FALSE),' & Sheets(I).Name & '!R9C2:R150C4,3,FALSE)"[/COLOR]

any suggestions would be greatly appreciated!

 

[Domenic]

Try...

Sheets("Overview").Range("A8").Offset(N, I - 2).FormulaR1C1 = "=VLOOKUP(VLOOKUP(" & CellNum & ",'RawNames'!R3C2:R365C3,2,FALSE),'" & Sheets(I).Name & "'!R9C2:R150C4,3,FALSE)"

 

[everettjsj2]

thank you for the attempt but unfortunately I am still getting the same error. Very frustrating! thanks again for the try

 

[Domenic]

Which error are you getting, and at which line of code?

 

[everettjsj2]

im getting error 1004 at this line:

Sheets("Overview").Range("A8").Offset(N, I - 2).FormulaR1C1 = "=VLOOKUP(VLOOKUP(' & CellNum & ','RawNames'!R3C2:R365C3,2,FALSE),' & Sheets(I).Name & !R9C2:R150C4,3,FALSE)"

 

[Domenic]

That's because you're mixing apostrophes (') with quotes ("). Have another look at the code I offered.

 

[everettjsj2]

I used your exact code and VBA still returned the same error. Any other suggestions??

 

[Domenic]

After replacing that line of code with mine and running your code, no error occurred. So I don't know why you're getting that error. Hopefully some else will be able help.