Dynamically adjusting Vlookup

  • Thread starter Thread starter angusrocks
  • Start date Start date
A

angusrocks

Hi,

At work we often paste multiple reports into excel. These reports are
always different sizes. I've created a search function which allows me
to to identify exactly where the report has been pasted. The function
also gathers information about where the report starts and stops. The
problem I'm having is being able to use variables within the Vlookup
function within Excel.

This is what I would like to do:


Dim Column1 as interger
Dim row1 as interger
Dim Column2 as interger
Dim row2 as interger
Dim Column3 as interger
Dim row3 as interger

{Program then assigns values to these varibles based upon the search
conducted.}

then this is what I can't figure out:

What's the syntax for passing my varibles into the vlookup
function?

' I've tried this, and it doesn't work.

=vlookup(cells(row1, column1), cells(row2, column2):cells(row3,
column3),9,false)

How come this won't work?

What I'm hoping to achieve is a dynamically adjusting vlookup field
based upon the inputs from the variables, but I can't seem to get the
syntax correct.


Any help would be greatly appreciated!!



Thanx,


Angusrocks.
 
Try it like this:

ActiveCell.Formula = "=VLOOKUP(" & Cells(row1, column1).Address & "," _
& Range(Cells(row2, column2), Cells(row3, column3)).Address & ",9,0)"

PS you should Dim the row variables as Long and not integer or you will get
an overflow error if you go over 32,767 rows.

Hope this helps
Rowan
 
Thank you Barb,


I'll try what you have suggested and post a reply as to my results.



Oh, and I know about declaring the variables as longs.... I was jus
in a hurry typing up my question. :)


Thanks again.




Angusrocks
 
Thank you so much!!


It works!


This is gonna save me and 5 other analysts so much time. :)



Thanks again.



Angusrocks
 
Back
Top