Vlookup in vba - how to use absolute rows not relative

Hi

I have the following snippet of code:

    Sheets("mdata").Select
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "RVU"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = 
"=IF(ISNA(VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2)),0,VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2))"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E" & LastRow), 
Type:=xlFillDefault

This fills about 5,000 rows with look up data from the rvu ws, and puts in 0 
if #N/A is obtained. HOWEVER, if I was just using VLookup as a formula, I 
would use =VLOOKUP(E2,'cases-dump'!A$2:B$7238,2) so that as I fill the 
formula down the column, I keep the same constant reference from the rvu look 
up sheet. As is, the vba formula keeps incrementing the rows and I get all 
0's at the end.
How do I correct this?

Thanks in advance!

clsnyder
0
Utf
6/4/2010 3:49:01 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
1657 Views

Similar Articles

[PageSpeed] 4

Try the below

Sub Macro()

Dim lngLastRow As Long, ws As Worksheet

Set ws = Sheets("mdata")

lngLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
ws.Range("E1").Value = "RVU"
ws.Range("E2:E" & lngLastRow).Formula = _
"=VLOOKUP(E2,'Cases-dump'!$A$2:$B$7238,2,0)"

End Sub


-- 
Jacob (MVP - Excel)


"clsnyder" wrote:

> Hi
> 
> I have the following snippet of code:
> 
>     Sheets("mdata").Select
>     Range("E1").Select
>     ActiveCell.FormulaR1C1 = "RVU"
>     Range("E2").Select
>     ActiveCell.FormulaR1C1 = 
> "=IF(ISNA(VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2)),0,VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2))"
>     Range("E2").Select
>     Selection.AutoFill Destination:=Range("E2:E" & LastRow), 
> Type:=xlFillDefault
> 
> This fills about 5,000 rows with look up data from the rvu ws, and puts in 0 
> if #N/A is obtained. HOWEVER, if I was just using VLookup as a formula, I 
> would use =VLOOKUP(E2,'cases-dump'!A$2:B$7238,2) so that as I fill the 
> formula down the column, I keep the same constant reference from the rvu look 
> up sheet. As is, the vba formula keeps incrementing the rows and I get all 
> 0's at the end.
> How do I correct this?
> 
> Thanks in advance!
> 
> clsnyder
0
Utf
6/4/2010 4:20:54 AM
Reply:

Similar Artilces:

Primission / resticted access to files using the MS server
I wish I have NEVER deciced to use this facility!!!@ I should have kept to paper and pin.. This word file is totally useless to me now. I have been using the tool facilty sucessfully for years but now it won't let me get to my own files.., I think you can tell what I think about this product. Hello Ron Ron wrote: > I wish I have NEVER deciced to use this facility!!!@ I should have kept to > paper and pin.. This word file is totally useless to me now. I have been > using the tool facilty sucessfully for years but now it won't let me get to > my own fi...

Visio is unable to create a shape using this master shortcut.
I want to use a shape called 1-D Word balloon so I try to drag it from Charting Shapes to my work but I get the following message: Visio is unable to create a shape using this master shortcut. The target master cannot be found in the target document Dragging basic shapes works fine. I'm using Visio 2003. Thank you. On Thu, 9 Apr 2009 11:53:01 -0700, Pierre <idontwanttoreceiveemail@hotmaildotcomm> wrote: >I want to use a shape called 1-D Word balloon so I try to drag it from >Charting Shapes to my work but I get the following message: > >Visio is unable to creat...

Sort Problem using a button
I have several worksheets, that I need to sort. My idea was to put a button on what I call my 'input page' and sort each of these pages. But I get the error: "Run-Time Error 1004: The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort by Box isn't the same or blank." Now on sheet 1 is where the button is. Information that is to be sorted is on sheet2, sheet3 sheet 4 and etc. Sort code is: Private Sub Sort1() ActiveSheet.Range("A2:D14").Select Selection.Sort Key1:=Range("A2&quo...

Using Excell to input data into Access
Is it posible to use an excel spreadsheet to enter data into access? The actual reason for using excel to enter the fields in access is that over 100 of our users are familiar with excel, but do not know anything about databases. They actually call excel a database about 1/2 of the time. We work in a contract research facility that does lots of different projects in several scientific disciplines of varying complexity. I started using databases to store my research data in back in the 80's with db3. We did not even have an IT department until about 2 years ago. I have been trying to drag ...

Useful website
This is a multi-part message in MIME format. ------=_NextPart_000_00FA_01C37D48.8D545F20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable just have a look on my website. http://earnmoneytoday.0catch.com Thank you ------=_NextPart_000_00FA_01C37D48.8D545F20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859...

Average using Sumproduct or ....
I have 12 tabs (same worksheet) that I need to average a number in cell b6. The issue is sometimes there is a zero in b6 and I do not want to count it in the average. example: tab 1, b6=145, tab 2=276, tab 3=0, tab 4= 123, tab 5=0, and so on... What is a good formula? Try =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!B6"),"<>0")) /SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:12"))&"'!B6"),"<>0 -- HTH Bob "heater" <...

Changing the user password without the use of SA or DYNSA accounts
Is there any way to save a user password in GP v10 without the use of the SA or DYNSA login? I would like to grant security to one user to do this. Currently the save button is greyed out unless they log in as SA or DYNSA. Thank you You can do this by granting that user sysadmin rights in SQL management Studio. In SMS, expand the Security folder, then logins, then find that user, right click the user, go to properties, click server roles and check the box for sysadmin. "Junior De Alba" wrote: > Is there any way to save a user password in GP v10 without the use of the S...

creating workflow using MS Outlook
hi! can anyone help me on how to develop a leave workflow app using outlook. ...

Vlookup only the last 6 characters
I have a spreadsheet starting in cell A4. I want to lookup only the last 6 digits in column A and then lookup the value in column J and return the appropriate value from column K. The values in column A are 000322341; 000333456; etc. The values in column J are 322341; 333456; etc. Here is my current non working formula... =VLOOKUP(RIGHT(A4,6),$J$4:$K$7,2,0) Any help would be appreciated. Thanks. The RIGHT function is returning a text value, and I'm guessing that the values in column J are true numbers. So you'd need to covert the text back to numbers in order to...

Correlation
I have a table of data as below. There are more Customers, and more dates' worth of data, but they won't fit in this window. For a number of our customers, we change delivery routes during the week in order to optimize our shipping capacity. Basically, I am trying to see for each delivery route, on a given day, how many of the same customers are on the same route. In other words, when one customer changes from one route to another over a date range, do other customers switch to the same routes on the same days? Cust # Rt# 8/27/2005 8/29/2005 8/30/2005 8/31/2005 a 1069 ...

65000 Row Limitation from Access to Excel
This question has been asked a couple times, but the answer seems to be unclear so I will ask again. I have query results in Access that I would like to work with in Excel - I like Excel pivot tables better than Access pivot tables. If I highlight the 287K line Access table, right click on copy, then paste into the 1 million plus row spreadsheet capacity in Excel, it only pastes 65,000 lines. Exports from Access to the million row plus spreadsheet work similarly only exporting 65,000 lines of the much larger table. I can successfully export the data from Access to a text file, then impor...

Excel Chart How do I create Y axis to represent numbers I am using
I am attempting to create a chart that is populating its data from another worksheet in the same book. I am pulling in numbers that range from 75000 to 3000000 and I would like my y axis to be 50K to 350K in multiples of 25K but I can not figure out how to adjust the axis key. Thanks for your help in advance. Krefty Select the axis and press Ctrl+1 (numeral one). Excel 2003 and earlier: In the dialog that appears, click on the Scale tab, and enter the values you want for min, max, and major unit, and don't check the corresponding Auto boxes. Excel 2007: Check Fixed for each of mi...

Reading one Record at a time till i reach EOF in VC++ using ODBC
Hi, i am working on Vc++6.0. I hve a req where in i need to read one record at a time till i reach EOF from the database.Can anyone tell me how to do this Very urgent Thanks Use CDatabase and CRecordSet classes. // Embed a CDatabase object // in your document class CDatabase m_dbCust; // Connect the object to a // read-only data source where // the ODBC connection dialog box // will always remain hidden m_dbCust.OpenEx( _T( "DSN=MYDATASOURCE;UID=JOES" ), CDatabase::openReadOnly | CDatabase::noOdbcDialog ); CRecordSet rs;rs.m_pDatabase=&m_db...

Help Freezing Multiple Rows
In earlier versions, you clicked below the rows you wanted frozen in place or to the right of the rows you wanted frozen. Now I see only how to freeze row 1. I have a main title in row 1, row 2 is blank and row 3 has column headings, so I want row 3 to stay frozen in place. I am having trouble accomplishing this and need help, please. Thank you. -- Virgo click on cell A4 then on the menu bar Window/Freeze Panes "Virgo" wrote: > In earlier versions, you clicked below the rows you wanted frozen in place or > to the right of the rows you wanted frozen....

Use of Paid Transaction Removal
Hello, I was wondering if anyone can tell me the reason a person would use the Paid Transaction Removal Screen and select only checks and then a date. When I did this in a test environment, it removed all checks before that date. Wouldn't it make more sense to provide a range, so as to not remove so many checks going backwards? Or am I using this tool wrong? Also, what are the ramifications of using this tool, account balancing wise? Thanks JOsh Josh, The Paid Transaction Removal process simply moves transactions from the OPEN status and tables to HIST (Historical) status and...

Cant use word as e-mail editor
If I check Word as e-mail editor in Outlook options, I get following message: "This form requires Word as your e-mail editor, but Word is either busy or cannot be found. The form will be opened in the Outlook editor instead." I de- and re-installed Office XP complete but issue is still occuring. If you open Word, normally, does it run? >-----Original Message----- >If I check Word as e-mail editor in Outlook options, >I get following message: "This form requires Word as your >e-mail editor, but Word is either busy or cannot be >found. The form will be open...

add row
Hello, I have a spreadsheet that has 250 rows, I have to add a blank row after every fifth row, is there a formula to apply to my spreadsheet. Manually is quite a hassle. Thank you, all -- smile Sub addrowevery5() counter = 6 Do Until Cells(counter, "a") = "" Rows(counter).Insert counter = counter + 6 Loop End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "israel" <israel@discussions.microsoft.com> wrote in message news:040FF41D-ECA7-40CA-A17F-2AD1FCFBE89A@microsoft.com... > Hello, > > I have a spread...

Using Exchange 2003 to filter and route non-spam emails
Hello, We've got Exchange 2003 running and want to be able to route emails received from particular domains to specific users. Are there filters/policies that can be applied in Exchange '03, or is this functionality available only with third party add-ins? Thanks for your time, -w No native functionality to do this. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- "wartbegone" <wartbegone@discussions.microsoft.com> wrote in message news:BCF31E7E-0598-4615-BDD0-39C69E5...

Adding row's with formula
How can I allow users to insert row's with the formula added automaticly without unprotecting sheet regards Chris Like your previous post while protecting the worksheet check the Insert Rows Checkbox and enter your protection password and give ok. So the users are not required to unprotect the workbook for inserting rows. Alt+T+P+P>>Allow All User of this Worksheet to:>>Check the Insert Rows check box About the filling of Formula to a newly added row should be done manually by pressing Cntrl+D or you should go for macro solution. Remember to Click Yes, ...

EXcluding Zeros from the average in a row
HI I am trying to average a row of numbers (F35:U35) that have numeric zeros in some of the cells. However, I would like to exclude them, and the cells from the calculation "=AVERAGE(F35:U35)". Is there a way to do that? Thanks -- Geo Hi Geo This array formula will do the job: =AVERAGE(IF(F35:U35<>0,F35:U35)) To be entered with <Shift><Ctrl><Enter> instead of <Enter>, also if edited later. -- Best Regards Leo Heuser Followup to newsgroup only please. "Geo" <Geo@discussions.microsoft.com> skrev i en meddelelse news:9C2B0B65-1AF...

Using F9 Financial Report Program
The F9 Financial report writer recommends auto-recalc be set to manual. For those individuals using F9, there seems to be problems with recalc when viewing spreadsheets that do not employ the F9 report program. Has anyone had similar problems? ...

conditional format from row to row
Hi, I am using Excel 2003 and would like a conditional format to work with a named range, is that possible? I have a named Range called City in column b. When the city changes I would like for the entire row to change to a specific color I designate. I have used this formula =$b16<>$b15 and this works until I filter the data. Please if anyone has done this or something close I would like to see your formula. Thanks, John For a "2 color" band...one group of rows will be the color you select. The next group of rows will not be colored in effect giving you a 2 color band....

How does one print shipping labels using Money 2004 Small Business?
Sir or Ma'am: Since Money 2004 Small Business would seem to be for the small businessman, I've been looking for a way to print a simple shipping label (right from the Invoice). No joy. What am I missing? The Merc ...

Delete rows in table from Module or Form
Hi fols I've been trying to delete som rows from a table: in a form - wont work in a module - wont work either in a query that's called from a module - and ofcoursre , wont work --- tried this in the module Set db = CurrentDb() SQLstr = "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And [magasineId] = '" & myMagsineId & "' & "" ;" CurrentDb.Execute SQLstr and this: DoCmd.RunSQL "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And [magasineId] = '" & myMagsineId & &quo...

FRx row format account labels
Hello: When an FRx row format is created by using "Edit...Add Rows from Chart of Accounts" and so forth, how does FRx determine the name of the account to place within the row? What I'm getting at is that a row format that is based on the natural account segment can represent several accounts--for example, an account that represents several departments--can be "divided" into different departments based on the segment of the account. So, for an account that has as many as ten departments, how does FRx determine the name for the account. The account description ...