Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically adjusting Vlookup
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. -- angusrocks ------------------------------------------------------------------------ angusrocks's Profile: http://www.excelforum.com/member.php...o&userid=25583 View this thread: http://www.excelforum.com/showthread...hreadid=390061 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically adjusting Vlookup
Look at the ADDRESS function and see if that does what you want.
Barb Reinhardt "angusrocks" wrote in message ... 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. -- angusrocks ------------------------------------------------------------------------ angusrocks's Profile: http://www.excelforum.com/member.php...o&userid=25583 View this thread: http://www.excelforum.com/showthread...hreadid=390061 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically adjusting Vlookup
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 "angusrocks" wrote: 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. -- angusrocks ------------------------------------------------------------------------ angusrocks's Profile: http://www.excelforum.com/member.php...o&userid=25583 View this thread: http://www.excelforum.com/showthread...hreadid=390061 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically adjusting Vlookup
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 -- angusrock ----------------------------------------------------------------------- angusrocks's Profile: http://www.excelforum.com/member.php...fo&userid=2558 View this thread: http://www.excelforum.com/showthread.php?threadid=39006 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically adjusting Vlookup
Oh Thanks to Rowan too. Being in a hurry is always gonna get me!! :) Angusrocks -- angusrock ----------------------------------------------------------------------- angusrocks's Profile: http://www.excelforum.com/member.php...fo&userid=2558 View this thread: http://www.excelforum.com/showthread.php?threadid=39006 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically adjusting Vlookup
Thank you so much!! It works! This is gonna save me and 5 other analysts so much time. :) Thanks again. Angusrocks -- angusrock ----------------------------------------------------------------------- angusrocks's Profile: http://www.excelforum.com/member.php...fo&userid=2558 View this thread: http://www.excelforum.com/showthread.php?threadid=39006 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adjusting formula for month in vlookup? | Excel Discussion (Misc queries) | |||
Adjusting series | Charts and Charting in Excel | |||
Adjusting Formula | Excel Worksheet Functions | |||
Adjusting Vlookup Values | Excel Worksheet Functions | |||
adjusting Y-axis | Excel Programming |