Solved

Excel macro to lookup 'exact' value and copy corresponding field

Posted on 2011-02-14
2
606 Views
Last Modified: 2012-05-11
Hi Experts,

I got this excellent macro from MSmax:

Private Sub lookup()

Dim wb As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim lr1 As Long, lr2 As Long
Dim i As Long, j As Long
Dim count As Long

Set wb = ThisWorkbook
'Change Sheetnames if needed
Set sh1 = wb.Worksheets("Sheet1")
Set sh2 = wb.Worksheets("Sheet2")

lr1 = sh1.Range("A" & Rows.count).End(xlUp).Row
lr2 = sh2.Range("A" & Rows.count).End(xlUp).Row

For i = 2 To lr2
    count = 0
    For j = 1 To lr1
        If InStr(1, sh1.Cells(j, 3).Value, sh2.Cells(i, 1).Value, vbBinaryCompare) > 0 Then
            count = count + 1
            sh2.Cells(i, 4 + count).Value = sh1.Cells(j, 1).Value
        End If
    Next j
Next i

End Sub
 

For each value found in sheet2 column A, it checks if string is found in sheet1 column C, if found copies corresponding value of sheet1 column A back to sheet2.

The problem here is that the compare does not check the "exact" string,
so if
AAA-BBB-CCC is the value iin sheet 2 column A to be searched for in sheet 1,  
what is returned is the corresponding values of:
AAA-BBB-CCC and
AAA-BBB-CCC-DDD and
AAA-BBB-CCC-DDD-EEE etc

Whereas only corresponding value of exactely 'AAA-BBB-CCC' is needed.

Do you see a way of changing this? Maybe this makes 'vbBinaryCmpare' not suitable for this?

Thank you for helping.

W.
 
0
Comment
Question by:Watnog
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
Change the Instr line to:
           If sh1.Cells(j, 3).Value = sh2.Cells(i, 1).Value Then

Open in new window

and it should require an exact full match.
0
 

Author Closing Comment

by:Watnog
Comment Utility
Thank you Rorya.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now