Vlookup question

[Costa_Mukhar]

Hello experts,

Sub Bond_Accruals()

    With Worksheets("cost report").Range("C5:C" & Range("A" & Rows.Count).End(xlUp).Row)
            .FormulaR1C1 = "=vlookup(RC[-1],'BA'!C4:C8,5,False)"
            .FormulaR1C1 = .Value
    End With

End Sub

I am using the above code for a vlookup and it is working fine, but when applying the formula on the range, it is applying it on one more cell. Range("A:A") is till row 15, but it applies the formula till row 16 in Range("C:C").
Is there something wrong with the code? appreciate you help....thanks

 

[GCExcel]

I don't see a problem with your code. It should place the value in C5:C15 only.

Is cell A16 really empty of there is a trailing space?

 

[Costa_Mukhar]

no, there is nothing there, the cell is empty.
I am filling the data in column A using the below code, it is a normal copy.paste

Range("E6").Select
Range(Selection, Selection.End(xlDown)).Copy
Worksheets("Cost report").Range("A5").PasteSpecial Paste:=xlPasteValues

 

[GCExcel]

if you type

msgbox [COLOR=#333333]Worksheets("cost report").Range("C5:C" & Range("A" & Rows.Count).End(xlUp).Row).address[/COLOR]

what result do you have?

Note:
You should avoid using .Select in as it slows down your code. Try this instead :

Range("E6:E" & Range("E6").End(xlDown).Row).Copy

 

[Costa_Mukhar]

msgbox giving $C$5:$C$15

and thanks for the note, it really helps...I am still new to VBA and welcome any advice