VB for Excel problem - macros for two separate worksheets

I have exported all records of a Contact Management program into Excel. So now all fields have data that is "blah blah" for sake of argument, except for two which are right next to each other: Company and Contact (i.e., first name and last name). One file contains all contacts, whereas the second file is a subset of the main file.

I have assigned individual ID's to each record of the main file, and all I want to do now is in the second file, grab those ID's and tag each in the second file accordingly (i.e., if in the main file the ID is 100, want to write the macro so that it finds the same Company/Contact in the second file and tags it also with 100).

This is easy if there weren't many records, but there are several hundred in the second file alone! Also I've been playing with ActiveCell properties and the Cells properties, but since VB for Excel isn't exactly like regular VB, I trip over Excel's rules. In database terms, I've been treating Company/Contact as the primary key so-to-say in order to do a search.

Unless there's a better way!!! Maybe SQL server has its own way of manipulating tables? Just need some suggestions

Hi,

Following sample shows how to solve it. You need to adjust it in order to be implemented correct in Your case:


Option Explicit

Sub Update()
Dim wbSource As Workbook, wbTarget As Workbook
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim rnSource As Range, rnTarget As Range, rnValue As Range
Dim stAddress As String
Dim i As Long

Set wbSource = ThisWorkbook
Set wbTarget = Application.Workbooks("Timer.xls")

Set wsSource = wbSource.Worksheets("Blad1")
Set wsTarget = wbTarget.Worksheets("Blad1")

With wsSource
Set rnSource = .Range(.Range("A2"), .Range("C65536").End(xlUp))
End With

With wsTarget
Set rnTarget = .Range(.Range("A2"), .Range("A65536").End(xlUp))
End With

For i = 1 To rnSource.Rows.Count
With rnTarget
Set rnValue = .Find(What:=rnSource(i, 1).Value)
If Not rnValue Is Nothing Then
stAddress = rnValue.Address
Do
If rnValue.Offset(0, 1).Value = rnSource(i, 2).Value Then
rnValue.Offset(0, 2).Value = rnSource(i, 3).Value
End If
Set rnValue = .FindNext(rnValue)
Loop While Not rnValue Is Nothing And rnValue.Address <> stAddress
End If
End With
Next i

End Sub

Thanks, I'll check it out - Excel can be strange bird if you're not too familiar with it s constants and properties. Also has built in functions I could probably use.

Thanks, I'll check it out - Excel can be strange bird if you're not too familiar with it s constants and properties. Also has built in functions I could probably use.

Specifically, check out the VLOOKUP function. The Excel help file has a good rundown of it. It sounds ideal for what you want to do, and doesn't have to be incorporated into a VBA code macro.

Best, it can look for close matches OR exact matches, depending on how you set the last argument. So if the contact name is not spelled the same in the two files, it might still pick it up. The downside is that it might pick up the wrong contacts, though, if they are naturally close (Glenn Close and Glen Cooke, for instance)...so use that wisely.