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

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