Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

how to drag vlookup in vba for all the cells down with dynamic range

Posted on 2011-03-10

Microsoft Excel

1 solution

Medium Priority

595 Views

Last Modified: 2012-05-11

how to drag vlookup in vba for all the cells down
I have written

Cells(4, Newcolumn).Formula = Application.WorksheetFunction.VLookup(Range("A4"), Sheets("Basic_Data").Range("C2:J7129"), 8, False)

Selection.AutoFill Destination:=Cells(newrange & ":" & r & "7129")
Range(newrange & ":" & r & "7129").Select

but this is copying only values not formula. I need to use dynamic range

Comment

Question by:Ruchi_Sas

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

2 Comments

LVL 6

Accepted Solution

by:FernandoFernandes

FernandoFernandes earned 500 total points

ID: 350957672011-03-10

use this instead:

Range(newrange & ":" & r & "7129").FormulaR1C1 = "=VLOOKUP(RC1,Basic_Data!R2C3:R7129C10,8,0)"

now, if you want the 7129 to be dynamic, you could count the number of items on column C...
dim lngItems as Long
lngItems = application.worksheetfunctions.counta(Sheets("Basic_Data").Range("C1:C65000"))
Range(newrange & ":" & r & lngItems).FormulaR1C1 = "=VLOOKUP(RC1,Basic_Data!R2C3:R" & lngItems & "C10,8,0)"

LVL 6

Expert Comment

by:royhsiao

ID: 350970752011-03-10

here is another 2 ways to find the last rows

Sub Macro1()
Dim i As Integer
    
    i = ActiveSheet.UsedRange.Rows.Count
 
    MsgBox i
    
End Sub


Sub macro2()
Dim i As Integer
    i = Range("C65536").End(xlUp).Row
    MsgBox i

End Sub

Open in new window

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Connect to SAP R/3 and call custom FM from MS Excel

Article by: ExcelGuide

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.

How to calculate Duration from text and number cell value

Article by: Shums

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.

Creating a Cool Heat Map in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

Microsoft Excel

Binomial Distribution

Video by: Ed

Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…