Prev: Automatic Prompt for Users to Save
Next: Trouble with UserName
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 Save
Next: Trouble with UserName