M
Max
I was helped on this site by someone called Per.
He wrote this code for me that works very well. It is much appreciated.
This code compares two files for corresponding numbers in column B.
But I did not realise that it would cause a problem that I did not expect.
When a number does not show in one file a pop-up shows up. This is fine but
one of the files has more numbers than the other and this causes this pop-up
to show up more that i want it too.
Here is a possible solution. Insert a combo box into this code so that you
can select the number in either file to start with. Could you help with the
code for this insert or perhaps an alternative suggestion, but not to disable
the pop-up.
Here is the code. (the scrapy bit at the end was me).
Sub MergeData()
Dim wbA As Workbook
Dim wbB As Workbook
Dim shA As Worksheet
Dim shB As Worksheet
Dim IdRangeA As Range
Dim IdRangeB As Range
Dim IdCol As String
Dim FirstRow As Long, LastRowA As Long, LastRowB As Long
Set wbA = ThisWorkbook
Set wbB = Workbooks.Open(Application.GetOpenFilename)
Set shA = wbA.Worksheets("Sheet1")
Set shB = wbB.Worksheets("Sheet1")
IdCol = "B"
FirstRow = 2 ' Headings in row 1
LastRowA = shA.Range(IdCol & Rows.Count).End(xlUp).Row
LastRowB = shB.Range(IdCol & Rows.Count).End(xlUp).Row
Set IdRangeA = shA.Range(IdCol & FirstRow, IdCol & LastRowA)
Set IdRangeB = shB.Range(IdCol & FirstRow, IdCol & LastRowB)
For Each ID In IdRangeB
Set F = IdRangeA.Find(ID.Value, After:=shA.Range(IdCol & 2), _
LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlNext)
If Not F Is Nothing Then
ID.Offset(0, 10).Resize(1, 1).Copy Destination:=F.Offset(0, 13)
Else
msg = MsgBox("Id " & ID.Value & " was not found in " & _
ActiveWorkbook.Name & vbLf & vbLf & _
"Click OK to continue", vbInformation, "Warning!")
End If
Next
wbB.Close
Range("O:O").Select
Selection.ClearFormats
Columns("O:O").EntireColumn.AutoFit
Range("O1").Select
wbA.Save
End Sub
Thank you for your help.
Max
He wrote this code for me that works very well. It is much appreciated.
This code compares two files for corresponding numbers in column B.
But I did not realise that it would cause a problem that I did not expect.
When a number does not show in one file a pop-up shows up. This is fine but
one of the files has more numbers than the other and this causes this pop-up
to show up more that i want it too.
Here is a possible solution. Insert a combo box into this code so that you
can select the number in either file to start with. Could you help with the
code for this insert or perhaps an alternative suggestion, but not to disable
the pop-up.
Here is the code. (the scrapy bit at the end was me).
Sub MergeData()
Dim wbA As Workbook
Dim wbB As Workbook
Dim shA As Worksheet
Dim shB As Worksheet
Dim IdRangeA As Range
Dim IdRangeB As Range
Dim IdCol As String
Dim FirstRow As Long, LastRowA As Long, LastRowB As Long
Set wbA = ThisWorkbook
Set wbB = Workbooks.Open(Application.GetOpenFilename)
Set shA = wbA.Worksheets("Sheet1")
Set shB = wbB.Worksheets("Sheet1")
IdCol = "B"
FirstRow = 2 ' Headings in row 1
LastRowA = shA.Range(IdCol & Rows.Count).End(xlUp).Row
LastRowB = shB.Range(IdCol & Rows.Count).End(xlUp).Row
Set IdRangeA = shA.Range(IdCol & FirstRow, IdCol & LastRowA)
Set IdRangeB = shB.Range(IdCol & FirstRow, IdCol & LastRowB)
For Each ID In IdRangeB
Set F = IdRangeA.Find(ID.Value, After:=shA.Range(IdCol & 2), _
LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlNext)
If Not F Is Nothing Then
ID.Offset(0, 10).Resize(1, 1).Copy Destination:=F.Offset(0, 13)
Else
msg = MsgBox("Id " & ID.Value & " was not found in " & _
ActiveWorkbook.Name & vbLf & vbLf & _
"Click OK to continue", vbInformation, "Warning!")
End If
Next
wbB.Close
Range("O:O").Select
Selection.ClearFormats
Columns("O:O").EntireColumn.AutoFit
Range("O1").Select
wbA.Save
End Sub
Thank you for your help.
Max