• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup in database for multiple cells

fher9728

New Member
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

1585185007512.png



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:
1585185406377.png


I want to get Store name and region

the code that I have until now is:
VBA Code:

>>> use code - tags as written in Forum Rules <<<
Code:
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"
1585185702548.png


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

I upload a sample of the case

*NOTE* I have a cross post, this is the link:
https://www.mrexcel.com/board/threads/vlookup-in-database-for-multiple-cells.1128662/#post-5454424
 

Attachments

  • prueba1.xlsm
    92.7 KB · Views: 5
Last edited by a moderator:
Seems mostly to have been solved at MrExcel, but the runtime error 1004 mentioned in your msg#16 of that thread was being caused by the
For Each cell In Target
line, which goes through every cell that you've changed (deleted), not, as I suspect you want, only those in column F (if you've deleted say cells in range C3:F6, if will run through all of those cells instead of just F3:F6 (and offset(,-4) of a cell in column C doesn't exist, which caused the error)).
The solution offered in msg#17 there still iterates through all the cells but it doesn't need to.
It can be restricted to just those cells changed in column F thus:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, Rg As Range, CellsToProcess As Range

Set Rg = Sheets("BD CLIENTES").Range("B2").CurrentRegion
Set CellsToProcess = Intersect(Target, Range("F:F"))
If Not CellsToProcess Is Nothing Then
  For Each cell In CellsToProcess
      'your code here
     'you code here etc.
  Next
End If
 
Seems mostly to have been solved at MrExcel, but the runtime error 1004 mentioned in your msg#16 of that thread was being caused by the
For Each cell In Target
line, which goes through every cell that you've changed (deleted), not, as I suspect you want, only those in column F (if you've deleted say cells in range C3:F6, if will run through all of those cells instead of just F3:F6 (and offset(,-4) of a cell in column C doesn't exist, which caused the error)).
The solution offered in msg#17 there still iterates through all the cells but it doesn't need to.
It can be restricted to just those cells changed in column F thus:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, Rg As Range, CellsToProcess As Range

Set Rg = Sheets("BD CLIENTES").Range("B2").CurrentRegion
Set CellsToProcess = Intersect(Target, Range("F:F"))
If Not CellsToProcess Is Nothing Then
  For Each cell In CellsToProcess
      'your code here
     'you code here etc.
  Next
End If
thankyou so much
 
Back
Top