VLookup or Index_Match Macro

[izzy]

I'm just getting into VBA.
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?

 

[just_jon]

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]

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]

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]

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
	P	Q	R	S	T	U	V
1		A	399	155	98	171
2		B	261	308	258	223
3		C	237	435	100	376
4		D	211	144	342	207
5		E	91	484	389	262
6		F	352	117	198	409
7		G	188	443	398	209
8		H	353	157	168	210
9		I	116	500	279	483
10		J	464	118	205	149
11
12
Orders

Sheet1
Items to lookup in column A – results from the Orders worksheet to be returned to column B.

Book2
	A	B	C	D	E
1	Itemto Lookup	Result
2
3	A	155
4	H	157
5	B	308
6	Z	Valuenotfound
7	F	117
8	C	435
9
Sheet1

Put the following macro in the Sheet1 module.

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]

BobberStuff.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1944	00P138752	ItemName	Unique	0	#VALUE!	0	339	0.0	339.0	128440CAT	DependentPart	1	339.00	0.00
BobberDataBase

 

[izzy]

BobberStuff.xls
	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1944		#VALUE!	#VALUE!										0.00
BobberDataBase

 

[izzy]

You could use a macro with the Find function.

Put the following macro in the Sheet1 module.

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]

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):

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]

Revised exhibits:

Izzy_Demo.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3	00P138751															399	98
4	00P138752															261	258
5	abd12354															NV	NV
6
7
8																SeecolR	SeecolT
9																Orders	Orders
10																Worksheet	Worksheet
11
BobberDataBase