Compile Error: Expected: end of statement ? Simple code

[billyheanue]

Hi everyone again!

I have a very simple line of code : I am trying to set up a value contained in cell O59 as my col_index_value in a Vlookup. So i dont have to type the column index number in the formula, I can just use ColIndexVal.
Im getting a compile error.

Sub VertLookLiteral()

dim ColIndexVal = cells([59],[15]) as Integer




End Sub

am i referring to the cell right? Whats wronghere?

Thanks all!

 

[billyheanue]

I figured it out and feel dumb. But is it possible to use a variable in a vlookup formula in VBA?

 

[RoryA]

Yes it is. What have you tried?

 

[billyheanue]

Thanks for replying Rory! I'm still struggling in the preliminary stages of getting everything set up to do it.

Is it possible to use something like

Set Rng = Range("A37").CurrentRegion

and then refer to Rng for the "Table Array" in Vlookup? My big problem is that the Vlookup table dimensions are never definite.

Thanks, and sorry for the late reply Rory.

 

[RoryA]

Yes it is. Are you trying to put a formula in a cell or the result of the closeup?

 

[billyheanue]

I'm trying to use these cell references so I dont have to define anything *strict* in the vlookup vba code.

Using this as a template, how should I go about changing the ranges from "A1:A10" notation and use my variables I set up (such as rng) instead?

Sub ADDCLM()On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
Table1 = Sheet1.Range("A3:A13") ' Employee_ID Column from Employee table
Table2 = Sheet1.Range("H3:I13") ' Range of Employee Table 1
Dept_Row = Sheet1.Range("E3").Row ' Change E3 with the cell from where you need to start populating the Department
Dept_Clm = Sheet1.Range("E3").Column
For Each cl In Table1
  Sheet1.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
  Dept_Row = Dept_Row + 1
Next cl


End Sub

As always, Thanks a ton.

-Bill