From: clsnyder on 3 Jun 2010 23:49 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 From: Jacob Skaria on 4 Jun 2010 00:20 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  |  Pages: 1 Prev: Automatic Prompt for Users to SaveNext: Trouble with UserName