Compile error: Expected end of statement

**RoOmIE** · 10-08-2013, 09:44 AM

Sub big_v()
    Dim LastRow As Variant
    Dim Lastcolumn As Variant
    Dim count1 As Long
    Dim count2 As Long
    Dim count3 As Long
    count3 = 10
    LastRow = Range("C6").End(xlDown).Row
    LastRow = -1 * (LastRow)
    Lastcolumn = ThisWorkbook.Worksheets("Data Sheet").Cells(3, 5)
    Lastcolumn = -1 * (Lastcolumn + 10)
    count2 = -3
    For count1 = -7 To Lastcolumn Step -1
        .Cells(6, count3).Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC["count1"],Sheet1!C["count1"]:C["count2"],5,FALSE)"
        count3 = count3 + 1
        count2 = count2 - 1
    Next count1
        
End Sub

i want to put the counters in the [] brackets but i get a compile error expected end of stantement

**ragulduy** · 10-08-2013, 09:49 AM

you need to add & signs when switching from strings to variables in your formula definition:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[" & count1 & "],Sheet1!C[" & count1 & "]:C[" & count2 & "],5,FALSE)"

**RoOmIE** · 10-08-2013, 09:55 AM

   Range("j6").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[count1],Sheet1!C[count1]:C[count2],5,FALSE)"
  
    Range("K6").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],Sheet2!C[-8]:C[-4],5,FALSE)"
    Range("L6").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],Sheet3!C[-9]:C[-5],5,FALSE)"
    Range("M6").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],Sheet4!C[-10]:C[-6],5,FALSE)"
    Range("J6:M6").Select

this is sort of what i want to do ..

**RoOmIE** · 10-08-2013, 09:52 AM

thanks yudlugar ... now if i want to change sheets at every count .. how would i do this ..

**ragulduy** · 10-08-2013, 10:01 AM

 ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[" & count1 & "],Sheet" & count3 - 9 & "!C[" & count1 & "]:C[" & count2 & "],5,FALSE)"

?

**RoOmIE** · 10-08-2013, 10:04 AM

Thanks.. i am somewhat not used to the language of vba .. but am learning .. thanks a lot!

Compile error: Expected end of statement

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Compile error: Expected end of statement

Re: Compile error: Expected end of statement

Re: Compile error: Expected end of statement

Re: Compile error: Expected end of statement

Re: Compile error: Expected end of statement

Re: Compile error: Expected end of statement

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Compile Error: Expected: End of Statement

[SOLVED] Compile error: Expected end of statement

VBA- Compile Error, Expected: line number or label or statement or end of statement

Compile Error Expected: End of statement

How to fix a Compile Error Expected: End of statement.

Bookmarks

Bookmarks

Posting Permissions