setting table array in vlookup using find

[jkicker]

Hi all,
New to VBA, learning a lot already. Here's my situation:
I'm trying to write a vlookup where I know what the text in the first and last cells in my table array are, but not their cell address.
I've tried to fix it by using a variable for my table array.

Sub vl()

Dim vl1 As Range
Set vl1 = Range("A:A").Find("C.I.")

Dim vl2 As Range
Set vl2 = Range("B:B").Find("17200")

Dim vl1a As Range
set vl1a = range(&vl1, &vl2)


MsgBox vl1a


ActiveCell.Formula = "=VLOOKUP(G5," & vl1a & ",2)*H5"


End Sub

I get "compile error: expected: expression" at the & when setting vla1. i'll accept other solutions. the goal is for the table array portion of vlookup to find itself based on the fact that the first cell will say C.I. and the last cell will say 17200. these cells will be located in A:A and B:B respectively

 

[Norie]

Try this.

Option Explicit

Sub vl()
Dim vl1 As Range
Dim vl2 As Range
Dim vl1a As Range

    Set vl1 = Range("A:A").Find("C.I.")

    Set vl2 = Range("B:B").Find("17200")

    Set vl1a = Range(vl1, vl2)

    MsgBox vl1a.Address

    ActiveCell.Formula = "=VLOOKUP(G5," & vl1a.Address & ",2)*H5"

End Sub

 

[jkicker]

I wasted a day on a problem that was solved in under a minute. Thank you so much. Can i give you internet points somehow?

 

[jkicker]

well, it was working until i added it to the rest of my code. Run-time error '1004': method 'range' of object '_global' failed

 

[jkicker]

well, it was working until i added it to the rest of my code. Run-time error '1004': method 'range' of object '_global' failed

it failed at vl1a

 

[jkicker]

it failed at vl1a

nvm worksheet was protected. but different error 1004: application-defined or object defined at vlookup formula

 

[jkicker]

i changed the formula to r1c1 format

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2]," & vl1a.Address & ",2)*RC[-1]"