abulkhairi
New Member
- Joined
- Mar 5, 2018
- Messages
- 1
I have a code that searching for values and if match is found overwriting existing values, if not match found adding new records. The code does what I need except of one thing.
I want to replace Sheet2 reference with an external user-selected workbook. I presume that I have to use Application.FileDialog(msoFileDialogFilePicker) to do this.
Any thoughts are welcome.
I want to replace Sheet2 reference with an external user-selected workbook. I presume that I have to use Application.FileDialog(msoFileDialogFilePicker) to do this.
Any thoughts are welcome.
Code:
[COLOR=#333333]Option Explicit[/COLOR]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub Replace_From_List()
Dim cell As Range
Dim rngFind As Range
Dim Found As Range
Dim counter As Long
Dim lastrow As Long
With ActiveWorkbook.Sheets("Sheet2")
Set rngFind = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
End With
For Each cell In rngFind
Set Found = ActiveWorkbook.Sheets("Sheet1").Range("B2:B10000").Find(What:=cell.Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not Found Is Nothing Then
Found.Offset(, 1).Value = cell.Offset(, 2).Value
Found.Offset(, 2).Value = cell.Offset(, 3).Value
Found.Offset(, 3).Value = cell.Offset(, 4).Value
Found.Offset(, 4).Value = cell.Offset(, 5).Value
counter = counter + 1
End If
If Found Is Nothing Then
lastrow = ActiveWorkbook.Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
Cells(lastrow + 1, 1).Value = lastrow
Cells(lastrow + 1, 2).Value = cell.Offset(0, 0).Value
Cells(lastrow + 1, 3).Value = cell.Offset(0, 2).Value
Cells(lastrow + 1, 4).Value = cell.Offset(0, 3).Value
Cells(lastrow + 1, 5).Value = cell.Offset(0, 4).Value
Cells(lastrow + 1, 6).Value = cell.Offset(0, 5).Value
Cells(lastrow + 1, 7).Value = cell.Offset(0, 6).Value
End If </code>[COLOR=#333333] Next cell[/COLOR]