Hi OssieMac
I just tried to work on the OP's code..Actually you dont need to copy. The
below will do.
lngRow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(1, 11), Cells(lngRow, 11)).Formula = "=VLOOKUP(C1,price,4,0)"
If this post helps click Yes
---------------
Jacob Skaria
"OssieMac" wrote:
> Instead of looping through and creating the formual in each cell, try
> creating the formula in the first cell and then copy it to the remaining
> cells.
>
>
> Note the following.
> I have use I + 1 as your row number but as I said above there is no need to
> loop and increment I so you can replace I + 1 with the first required row
> number.
>
> A space and underscore at the end of a line is a line break in an otherwise
> single line of code.
>
> Copy and Paste is a one line operation in the following code.
>
> The code assumes that it is for the Active worksheet.
>
> Dim lngLastRow As Long
>
> 'Following line finds last row number with data in
> 'first column and assigns it to a variable.
> lngLastRow = Cells(i + 1, 1).End(xlDown).Row
>
> 'Enter formula in the first required cell only.
> Cells(i + 1, 11).FormulaR1C1 = _
> "=VLOOKUP(RC[-3],price,4,FALSE)"
>
> Cells(i + 1, 11).Copy _
> Destination:=Range(Cells(i + 1, 11), _
> Cells(lngLastRow, 11))
>
> --
> Regards,
>
> OssieMac
>
>
> "Jacob Skaria" wrote:
>
> > You dont need to select a cell to assign a formula. Try the below two macros
> > one assigning the formula and the other using worksheetfunction; and compare
> > the time taken....
> >
> > Sub Macro1()
> > Do While IsEmpty(Cells(I + 1, 1)) = False
> > I = I + 1
> > Cells(I + 1, 11) = "=VLOOKUP(C" & I & ",price,4,0)"
> > Loop
> > End Sub
> >
> >
> > Sub Macro2()
> > Application.ScreenUpdating = False
> > Do While IsEmpty(Cells(I + 1, 1)) = False
> > I = I + 1
> > Cells(I + 1, 11) = WorksheetFunction.VLookup _
> > (Range("C" & I), Range("price"), 4, 0)
> > Loop
> > Application.ScreenUpdating = True
> > End Sub
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "petedacook" wrote:
> >
> > > I have some code that a user has been running which plugs a vlookup formula
> > > into the spreadsheet:
> > >
> > > 'Do While IsEmpty(Cells(I + 1, 1)) = False
> > > ' I = I + 1
> > > ' Cells(I + 1, 11).Select
> > > ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],price,4,FALSE)"
> > > ' ActiveCell.Offset(1, 0).Select
> > >
> > >
> > > Loop
> > >
> > > This takes too long to run.....so I want to use code to perform the lookup
> > > and plug the result into the active cell.
> > >
> > > This code is running through several thousand rows...more than 2,000 and
> > > less than 10K, but the number of rows varies from week to week.
> > >
> > > My question: Is there a faster way to preform the lookup and plug the reult
> > > into the active cell other than using the application function method:
> > >
> > > Application.WorksheetFunction.Vlookup()
> > >
> > >
> > > Is this the best method to perform a vlookup?
|
Similar Threads
