Search and Update Records in External Excel File

[yogeshwarv]

Hi,

I am using an excel template that can export a set of data from workbook "A" to workbook "B" with the help of VBA.

I wish if excel could search and match the record before exporting to workbook "B" and if found the record select the existing row and over write the said row.

can anyone help me how to do this?

Regards
Yogeshwar

[Doug Robbins]

Try

[VBA]Dim i As Long, j As Long, m As Long
Dim crange As Range
Dim blnMatch As Boolean
With Workbooks("A").Sheets(1).Range("A1")
For i = 1 To .CurrentRegion.Rows.Count - 1
blnMatch = False
For j = 0 To .CurrentRegion.Columns.Count
Set crange = .Offset(i, j)
With Workbooks("B").Sheets(1).Range("A1")
For m = 1 To .CurrentRegion.Rows.Count - 1
If .Offset(m, j) = crange Then
blnMatch = True
Exit For
End If
Next m
End With
If blnMatch = True Then
Exit For
End If
Next j
If blnMatch = True Then
m = m
Else
m = Workbooks("B").Sheets(1).Range("A1").CurrentRegion.Rows.Count
End If
For j = 0 To .CurrentRegion.Columns.Count
Workbooks("B").Sheets(1).Range("A1").Offset(m, j) = .Offset(i, j)
Next j
Next i
End With
[/VBA]