VLookup or Index_Match Macro

izzy

Board Regular
Joined
Jun 12, 2003
Messages
165
I'm just getting into VBA. :eek:
I need to at the very least do Vlookups with VBA to speed up my workbooks and reduce file size. Though I could be wrong.
My ranges are very large and the number of formulas is outrageous.

All cells from P13:P5000 contain this formula, dynamically.
VLOOKUP(A13,Orders!$Q$7:$S$7000,3,FALSE)

Q4000= IF(P4000="","",VLOOKUP(A4000,Orders!$Q$7:$T$7000,4,FALSE))

ect...

With this much math going on would a macro be helpful with speed and filesize or should I just leave it as it is?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
A few more details would help -- the data that you initially search -- what type of data are you looking for, andis it sortable or already in a sorted sequence?
 

izzy

Board Regular
Joined
Jun 12, 2003
Messages
165
I sort it when I import it, ascending. Text and Numbers mixed, formated as text due to the leading zeros and Vlookup issues.

Example Column A
000127482
00R567790
901268234


Column B (data I need to extract)
Could be dates in some instances or could be numbers in others.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Could you, using Colo's download below, load a screenshot of the raw data as well as a sample of the desired output? The reason I ask is that a formula set taught to me by Aladin might well help speed up you search process by a magnitude or two. But I'd need more details as to what the target search string/item was, what data type it was, whether the field was static, etc.
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416

ADVERTISEMENT

You could use a macro with the Find function.

Orders worksheet
You want to return information from column S by looking up items in column Q.
Book2
PQRSTUV
1A39915598171
2B261308258223
3C237435100376
4D211144342207
5E91484389262
6F352117198409
7G188443398209
8H353157168210
9I116500279483
10J464118205149
11
12
Orders


Sheet1
Items to lookup in column A – results from the Orders worksheet to be returned to column B.
Book2
ABCDE
1Itemto LookupResult
2
3A155
4H157
5B308
6ZValuenotfound
7F117
8C435
9
Sheet1


Put the following macro in the Sheet1 module.
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Variant
 If Target.Column = 1 And Target.Count = 1 Then
     With Sheets("Orders").Range("Q:Q")
         Set c = .Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
          If Not c Is Nothing Then
             Target.Offset(0, 1).Value = c.Offset(0, 2).Value
            Else
              If c Is Nothing Then
              Target.Offset(0, 1).Value = "Value not found"
              End If
            End If
        End With
    End If
End Sub
HTH

Mike
 

izzy

Board Regular
Joined
Jun 12, 2003
Messages
165
Ekim said:
You could use a macro with the Find function.

Put the following macro in the Sheet1 module.
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Variant
 If Target.Column = 1 And Target.Count = 1 Then
     With Sheets("Orders").Range("Q:Q")
         Set c = .Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
          If Not c Is Nothing Then
             Target.Offset(0, 1).Value = c.Offset(0, 2).Value
            Else
              If c Is Nothing Then
              Target.Offset(0, 1).Value = "Value not found"
              End If
            End If
        End With
    End If
End Sub
HTH

Mike

How Does this activate?
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Izzy,

The macro presented is an Event macro, so called because it is triggered by some event. The macro works when you enter data in column A of the BobberDataBase worksheet and then hit the Enter key. Hitting the Enter key triggers the macro to “lookup” the data in column A in the Orders worksheet. If you are not entering data in column A, item by item, and hitting the Enter key after each item, the macro will not be triggered

The macro needs to be in the relevant sheet module. In your case, that sheet module is “BobberDataBase” (post back if you are not sure how to put the macro into a sheet module).

Here is the revised macro (revised exhibits in next post):
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Variant
 If Target.Column = 1 And Target.Count = 1 Then
     With Sheets("Orders").Range("Q5:Q600")
         Set c = .Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
          If Not c Is Nothing Then
             Target.Offset(0, 15).Value = c.Offset(0, 1).Value
             Target.Offset(0, 16).Value = c.Offset(0, 3).Value
            Else
              If c Is Nothing Then
              Target.Offset(0, 15).Value = "NV"   ' No Value
              Target.Offset(0, 16).Value = "NV"   ' No Value
              End If
          End If
     End With
 End If
End Sub
You may be better off with a macro that looks up the data once all rows in column A (BobberDataBase worksheet) are filled in. If you require this, post back.

Note that your formula in P1444 (and Q1944) does not trap the “#NA” error that will occur if the value in A1944 is not found in the Orders database. If you plan to stick with your formula approach you may wish to change your Vlookup formulas to:

=IF(A5="","",IF(ISNA(VLOOKUP(A5,Orders!Q5:U14,2,FALSE)),"",VLOOKUP(A5,Orders!Q5:U14,2,FALSE)))


Regards,


Mike
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Revised exhibits:
Izzy_Demo.xls
ABCDEFGHIJKLMNOPQ
1
2
300P13875139998
400P138752261258
5abd12354NVNV
6
7
8SeecolRSeecolT
9OrdersOrders
10WorksheetWorksheet
11
BobberDataBase
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top