how to cut string table array vlookup in excel vba

0
votes

How to cut string in table arrary vlookup in excel vba.

Application.WorksheetFunction.VLookup(sheet1.Range("F2"), sheet2.Range("B11:D25"), 3, False)

B11 IS date cell: i want to substring only Month and year, so i dont need day.

F2 is selected date (apr-2013) and B11 is date(01-apr-2013) for table lookup, i want to cut 01.

Find apr-2013 and Lookup value should apr-2013 too.

Regards,

1
excelvba
Have you tried with the True instead of False as the last parameter as the approximate match? – user2140173

1 Answers

0
votes

To start with, you can use "?" or "*" wildcards in the vlookup, then you can remove the "01-" from the returned string manually - something like this:

Dim strLookup as String
Dim strReturn as String

strLookup = "??-" & Sheet1.Range("F2").Value

strReturn = Application.WorksheetFunction.VLookup(strLookup, sheet2.Range("B11:D25"), 3, False)
'Remove first 3 characters
strReturn = Mid(strReturn , 4, Len(strReturn) -3)