Vlookup using VBA

zuko · Jun 30th 2005

I posted this topic before but no responses seemed to help. I will try a different approach to it.

Below are the values that need to be imported into the next sheet (picture2) referenced through UPC.

[Blocked Image: http://www.zuko.org/uploader/./userfiles/zuko/gib1.jpg]

[Blocked Image: http://www.zuko.org/uploader/./userfiles/zuko/gib2.jpg]

I need to be able to do this for multiple workbooks therefore needs to be written in VBA and ran by the macro. Is it possible to put the data into an array and use vlookup to return only the result of the vlookup into the sheet (picture 2).

It is really difficult to explain this in text, so if anyone needs more clarification, please let me know. Thank you all in advance.

spog00 · Jun 30th 2005

Re: Vlookup using VBA

You asked this before and got 10 replies, 2 of which were not even replied to.

http://www.ozgrid.com/forum/showthread.php?t=35608

So far as I can tell nothing has changed in this post, and the last reponses for the last post were both likely results.

Alastair

zuko · Jun 30th 2005

Re: Vlookup using VBA

I must have missed your last post for some reason. I got it to work, thank you very much. One additional question though.

In your code

Code

For Each ws In Worksheets 
    If ws.Name = "Sheet1" Then Goto JeffRocks3 
    ws.Select 
    C = 14 
    For C = 14 To 13 + SNum 
        Cells(7, C).Formula = "=VLOOKUP(C7,Sheet1!$A$2:$BB$3000," & C - 10 & ",False)" 
    Next C 
    Range("A1").Select 
JeffRocks3: 
     'Sheet 1 is Skipped
Next ws 
Sheets("1").Select 
 
End Sub

Display More

Everything works but when I do an autofill to go down it takes forever to calculate all those cells. Is there a faster way around it by chance? Way to just paste the values? Thanks!

spog00 · Jul 1st 2005

Re: Vlookup using VBA

In order to just paste, you would need to have copied from somewhere, therefore this is not possible.

With some adusting you could get the macro to fill in every cell in your range, but i think this would be just as time consuming, as it would have to do it singly (to my knowledge anyway).

Let me know if you need/want the code updating to do this.

Alastair

zuko · Jul 1st 2005

Re: Vlookup using VBA

Yes could you please post the new code in order to get the macro to fill in the Vlookup?? Thanks

spog00 · Jul 1st 2005

Re: Vlookup using VBA

Try this

Code

Sub test()


Dim sNum As Integer
sNum = Cells(5, 256).End(xlToLeft).Column
Dim ws As Worksheet
Dim r As Integer
Dim end_row As Integer
Dim c As Integer




For Each ws In Worksheets
    If ws.Name = "Sheet1" Then GoTo JeffRocks3
    ws.Select
    end_row = Range("m65536").End(xlUp).Row
    For r = 7 To end_row
        For c = 14 To sNum
            Cells(r, c).Formula = "=VLOOKUP(C" & r & ",Sheet1!$A$2:$BB$3000," & c - 10 & ",False)"
        Next
    Next
        Range("A1").Select
    
JeffRocks3:
         'Sheet 1 is Skipped
Next
Sheets("1").Select
 
End Sub

Display More

Alastair

Vlookup using VBA

Participate now!

Share