Faster method for VLOOKUP

[j.smith1981]

I am having some memory problems with my VBA application.

I thought the quickest way to solve this would be to discuss this with someone people.

The problem is this part of my code, very simple really:

ActiveCell.FormulaR1C1 = "=vlookup(RC[1],'C:\UPLOADcategories_VLOOKUP.xls'!categories,2,FALSE)"

This is set in a loop that checks the value to the right, and of course fills in the active cell with the corresponding value, this file I am making it go through has 8,000 lines of products, so its rather memory intensive, plus the file its referencing from (array is located) is in an external file with probably double the entries, I have sorted them however into A-Z order, which has speeded it up a little but still needs quite a bit of work to make it faster.

Can someone come up with an alternative, non of the ones I have seen in examples lying around on the web seem to work for me:banghead:, can someone help me please?

Thanks as ever in advance of course,
Jeremy.

[Bob Phillips]

First, you don't need a loop to fill a set of cells with a aformula, you can do it in one hit. Second, why not open the other workbook to grab the values?

[Bob Phillips]

First, you don't need a loop to fill a set of cells with a aformula...

... especially not selecting the cell every time.

[j.smith1981]

Sorry I am still learning using these kind of functions with VBA so I am a little bit clueless about this.

How would I go about this?

Could potentially with allot of other parts of my macro speed this process up.

Do you have any previous examples you could share with me?

Thanks for your help, much appreciated.

[Bob Phillips]

The code to load them all at once would be



LastRow = Cells(Rows.Count,"A").End(xlUp).Row

Range("B1").Resize(LastRow).Formula = "=vlookup($A1,'C:\UPLOADcategories_VLOOKUP.xls'!categories,2,FALSE)"

[j.smith1981]

The code to load them all at once would be



LastRow = Cells(Rows.Count,"A").End(xlUp).Row

Range("B1").Resize(LastRow).Formula = "=vlookup($A1,'C:\UPLOADcategories_VLOOKUP.xls'!categories,2,FALSE)"


Beautifull way of working this one out wonderfull!

But for some annoying reason now I am getting the following error:


Compile error:

Variable not defined


Any suggestions as to why this is happening?

I have changed it to see if its because I am using this change in several places, I thought that it might be that each LastRow must be different for each like say LastRowCategories etc but its still coming up with the same error.

Would love to get some idea of why this is happening, I will try in advance of your reply in copying over a working macro (albeit the longer one) and replace again.

Kind thanks for your help you dont call yourself lord for nothing, thank you so much!:thumb

Jeremy.

[Bob Phillips]

You probably need to declare LastRow in your macro



Dim Lastrow as Long

[Bob Phillips]

Kind thanks for your help you dont call yourself lord for nothing, thank you so much!:thumb

I don't call myself it, it is an honorary label that the admins decided upon when I hit 10,000 posts. I am responsible for the avatar, and the signature, but not that.

[j.smith1981]

Ah I've sorted it, it for some reason was saying there was no variable declared, frustrating I know, VB isnt the best programming language in the world but it works now.

Many thanks for your help though, this has really speeded everything up.

Is there anyway of making it do the same to find any invalid values as there are some values that deliberately come up with N/A or 0 when there's no product existant on the file where excels getting the values from.

Is there anyway of doing the same thing for that rather than looping down?

This is the code I have at this moment for that:


Cells(1, 2).Select
Do
If ActiveCell.Formula = "#N/A" Then
Selection.EntireRow.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell.Offset(0, -1)) 'Loop until current selected cell is empty


'Only until gareth sorts out x ref!
Cells(1, 2).Select
Do
If ActiveCell.Value = "0" Then

Selection.EntireRow.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell.Offset(0, -1)) 'Loop until current selected cell is empty

I can then amend to what I want it to do from your example.

Many thanks again,
Jeremy.

[j.smith1981]

I don't call myself it, it is an honorary label that the admins decided upon when I hit 10,000 posts. I am responsible for the avatar, and the signature, but not that.

Ahh still should be called that as far as I am concerned thats wonderfull, you've helped me out loads and I really appreciate it.:thumb

[j.smith1981]

Its also appearing to attempting to calculate each cell everytime the range is calculated.

Is there anyway of stopping this from happening? Finding this most annoying as this is taking up sometime.

Although wouldnt this cause the excel sheet not to calculate the true values of those cells and show the formula? (ie not what I am wanting basically).

Thanks yet again of course THUMBS UP TO YOU!!

[Bob Phillips]

Is there anyway of making it do the same to find any invalid values as there are some values that deliberately come up with N/A or 0 when there's no product existant on the file where excels getting the values from.

What do you want to do with the #N/A!s?

[j.smith1981]

What do you want to do with the #N/A!s?

Basically select a whole range down an entire column and remove all N/A's

Without doing a loop prefferably, if this isnt possible though I dont really mind too much.

Just another note

I am trying to make a more robust version, basically I am wanting also to remove all duplicate (only 1 column exists with any data in it).

Removes all duplicates and moves up any empty rows, I am convinced I have done this before but its not working anymore, do you know a way of doing this straight up that works no matter what?

Bit stuck with those 2 really, if you could help that would be wonderful again.

Many thanks in advance your helps been invaluable to me.

Regards,
Jeremy.:thumb

[Bob Phillips]

Some questions as I am now lost as to where we stand regards the original question.

Are we looking at some code that :
- adds the formula to all rows
- replaces the formula with the value (Paste Values)
- removes any duplicates
- removes any errors (or just #N/A)?

Are the errors induced by the formula?

If we remove the #N/A they all become blank. Do you want them all collapsed to a single row?

When there are duplicates, which row stays?

[Bob Phillips]

PS Can you post a sample workbook?