PDA

View Full Version : Excel VBA - vLook-Up if match first 2 characters of one column with another column



gloriag
02-24-2014, 11:37 PM
I am a newbie on VBA. I have a table as below (see also the attachment) and need to do the vlookup with "dept" if the left 2 characters of "EE Code" between 91 and 95. Is it possible to have a macro/VBA to complete the task?




EE Code
dept
How to generate from VBA Macro




9216000
12345678



vlookup arrary

A123456
23456700



39914123
test
7856370
39914123



38214125
test
9155800
57889056



40964197
test
9326600
38214125



77014155
test
9135800
39914123





3456999
40964197





9458000
77014155





9537903
00123432





9158000
12345678





0456999
57889056





5856370
39914123

Bob Phillips
02-25-2014, 06:23 AM
You don't need VBA, just use a formula

=IF(OR(LEFT($C2,2)="91",LEFT($C2,2)="95"),VLOOKUP($D2,$A$3:$B$7,2,FALSE),"")

patel
02-25-2014, 06:31 AM
Sub a()
LR = Cells(Rows.Count, "C").End(xlUp).Row
drow = 2

For r = 2 To LR
If Val(Left(Cells(r, 3), 2)) >= 91 And Val(Left(Cells(r, 3), 2)) <= 95 Then
Cells(drow, 6) = Cells(r, 4)
drow = drow + 1
End If
Next
End Sub

gloriag
02-25-2014, 07:23 PM
Patel, the code doesn't work. The result shall be equal to Col E as shown below (see also the attachment in my first post).




Col A
Col B
Col C
Col D
Col E
Col F




EE Code
dept
if left 2 characters of EE code= 91 to 95. then vlookup with dept
How to generate from VBA Macro




9216000
12345678




vlookup arrary

A123456
23456700




39914123
test
7856370
39914123




38214125
test
9155800
57889056




40964197
test
9326600
38214125
test



77014155
test
9135800
39914123
test





3456999
40964197






9458000
77014155
test





9537903
00123432






9158000
12345678






0456999
57889056






5856370
39914123