Using Vlookup in VBA code

[Anis574]

VBA.png
Dear Sir;
I need a VBA code that copy some cells based on a specific value.
the details shown in attached image
tnx

[mehmetcik]

Please Login or Register  to view this content.

[Marc L]

A bit unclear as the copy destination is missing …

As a VBA beginner starter (not tested as the attachment is missing too ! ) :

Sub Demo1() 
    Dim F, T 
    With ActiveSheet.UsedRange.Columns 
        F = Application.Match(.Cells(1, 5).Value2, .Item(1), 0) 
        T = Application.Match(.Cells(2, 5).Value2, .Item(1), 0) 
        If IsError(F) Or IsError(T) Then Beep Else Range(.Cells(F, 2), .Cells(T, 2)).Copy .Cells(1, 7) 
    End With 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[alansidman]

Here is Chip Pearson's tutorial on the subject: http://www.cpearson.com/excel/Callin...ionsInVBA.aspx

[Anis574]

Thank you for reply mehmetcik;
your code correctly identifies E1 and E2 from column A and paste to column C. But what i need is just copy the corresponding values from column B (B5:B22 for this example). Please the attached photo for more detail.
tnx
Attachment 667389

[Anis574]

Hi Marc L;
Nice job, it works.
Is that possible to assign the worksheet in this code. I mean if i be in another worksheet (sheet 4 for example) the automatically executes in sheet1?
and is that possible that i enter the values of F and T dynamically (by an inputbox)?
thanks

[Marc L]

You can replace ActiveSheet …

[MarvinP]

Hey Marc L,

In your code above you have a Dim statement of:

Dim F, T

I use Option Explicit as the first line of my code to insure each variable is typed (as in string, long, double,...) Did you know your two variables above start as type Variant? Also if you use things like:
Dim i, j, k as integer , only the k will be an integer and the i and j will be variant. Just an FYI. Read:
http://www.learnexcelmacro.com/wp/20...dim-statement/

[Marc L]

Hi Marvin !

You can check yourself both variables must be Variant as you can read in Excel help for MATCH worksheet function !

I also explained several times why within this forum like in others, the reason why I wrote the procedure as it is …

[Marc L]

For your eyes only Marvin :

• Index and Match Run time error 1004
  
  
• Converting "Evaluate formula"