Vlookup in database for multiple cells

[fher9728]

Hi, its me again,

I have another problem, the thing is that I want to find the name of multiple Stores, I have the store id and I have the Stores Database in another worksheet, the the thing that I want is to run a macro that search me automatically every code that i put in a extensive table(many data that i put here daily), I'll give an example

What i want is when i put the code in column F, it appears me automatically the store name in column b , and the region in column c, but I want it in vba because using vlookup in too many cells will blow up my Workbook and need it to remain light, the database of Stores is in another Worksheet this is an example:

I want to get Store name and region

the code that I have until now is:

Sub buscarnom()



Dim Cl As Range
   
   For Each Cl In Range("f2", Range("F" & Rows.Count).End(xlUp))
      If IsNumeric(Cl.Value) Then
         Cl.Offset(, 10).Value = Application.VLookup(C1.Value, Sheets("BD CLIENTES").Range("C3:D69"), 2, 0)
      Else
         Cl.Offset(, -3).Value = Application.VLookup(C1.Value, Sheets("BD CLIENTES").Range("C3:G69"), 4, 0)
      End If
         Next Cl
      
      End Sub

Second Part,

here I have another table and what I pretend to do is to delete rows with Status "Vencido"

But i want to be deleted the entire row, but only the ones with status "vencido" the rows with status vigente not,

Hope you may help me like last time, thank you

 

[mse330]

I just tried pasting data in column F in my file in post #17 and it is working fine … It will not work only if the data you are pasting extends to more than 15 columns