How to Map 2 different columns in from 2 different excel files? [Closed]

Report
Posts
5
Registration date
Thursday February 11, 2016
Status
Member
Last seen
February 25, 2016
-
Posts
5
Registration date
Thursday February 11, 2016
Status
Member
Last seen
February 25, 2016
-
Dear All,

I am trying to Map values from 2 different columns from 2 different excel files. I am unsure as how to invoke the sheets and print the result(Yes/No) in either of the workbook. Could any of you please assist me with the start code as to assign and invoke the sheets and workbooks.

I browsed various tutorials, but they are not much useful. Looking forward to your help.

Anjana

1 reply

Posts
5
Registration date
Thursday February 11, 2016
Status
Member
Last seen
February 25, 2016

I have come up with the below code. But this works for a sheet with less number of rows. For a big file, the excel crashes and freezes. Though after doing some research, used and yet it freezes and goes to "Not Responding" in some time.

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Can someone please check and assist me on this ?

-----------------------------------------------------------------------------------
Option Compare Text
Dim Sheet1ID, Sheet2ID, sheet1FName, sheet2FName, sheet1Reltype, sheet2Reltype, sheet1sex, sheet2sex, sheet1salcode, sheet2salcode As String
Dim sheet1mname, sheet2mname, sheet1scode, sheet2scode, sheet1fullname, sheet2fullname, Sheet1DOB, Sheet2DOB As String
Dim wks As Worksheet, wksb As Worksheet, wks3 As Worksheet
Dim cell As Range
Dim rng, rngsheet1salcode, rngsheet2Fname, rngsheet1mname, rngNew, rngSheet1Name, rngSheet2Name, rngSheet1DOB, rngSheet2DOB, rngsheet1reltype, rngsheet2reltype As Range
Dim rngsheet2salcode, rngsheet1fname, rngsheet2mname, rngsheet1scode, rngsheet2scode, rngsheet2sex, rngsheet1fullname, rngsheet2fullname, rngsheet1sex As Range
Dim intCount As Integer, intCount1 As Integer
Dim n As Integer, m As Integer, i As Integer, j As Integer, K As Integer, intSheet1IDRow As Integer, intSheet1IDCol As Integer
Dim intSheet1DOB As Integer, intSheet2DOB, intsheet1reltype, intsheet2reltype, intsheet1sex, intsheet2sex, intsheet1salcode, intSheet2IDRow, intsheet2salcode, intSheet2IDCol As Integer
Dim intsheet1fname, intsheet2fname, intsheet1mname, intsheet2mname, intsheet1scode, intsheet2scode, intsheet1fullname, intsheet2fullname As Integer

Sub Mapping()

'With Excel.Application
' .ScreenUpdating = False
' .Calculation = Excel.xlCalculationManual
' .EnableEvents = False
'End With

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


Set wks = Worksheets("sheet1")
Set wksb = Worksheets("sheet2")
Set wks3 = Worksheets("sheet3")

Sheet1ID = wks3.Range("B2").Value
Sheet2ID = wks3.Range("C2").Value
sheet1Reltype = wks3.Range("B3").Value
sheet2Reltype = wks3.Range("C3").Value
sheet1sex = wks3.Range("B4").Value
sheet2sex = wks3.Range("C4").Value
sheet1salcode = wks3.Range("B5").Value
sheet2salcode = wks3.Range("C5").Value
sheet1FName = wks3.Range("B6").Value
sheet2FName = wks3.Range("C6").Value
sheet1mname = wks3.Range("B7").Value
sheet2mname = wks3.Range("C7").Value
sheet1scode = wks3.Range("B8").Value
sheet2scode = wks3.Range("C8").Value
sheet1fullname = wks3.Range("B9").Value
sheet2fullname = wks3.Range("C9").Value
Sheet1DOB = wks3.Range("B10").Value
Sheet2DOB = wks3.Range("C10").Value

intSheet1IDCol = Range(Sheet1ID).Column
intSheet1IDRow = Range(Sheet1ID).Row
intSheet2IDCol = Range(Sheet2ID).Column
intSheet2IDRow = Range(Sheet2ID).Row
intsheet1reltype = Range(sheet1Reltype).Column
intsheet2reltype = Range(sheet2Reltype).Column
intsheet1sex = Range(sheet1sex).Column
intsheet2sex = Range(sheet2sex).Column
intsheet1salcode = Range(sheet1salcode).Column
intsheet2salcode = Range(sheet2salcode).Column
intsheet1fname = Range(sheet1FName).Column
intsheet2fname = Range(sheet2FName).Column
intsheet1mname = Range(sheet1mname).Column
intsheet2mname = Range(sheet2mname).Column
intsheet1scode = Range(sheet1scode).Column
intsheet2scode = Range(sheet2scode).Column
intsheet1fullname = Range(sheet1fullname).Column
intsheet2fullname = Range(sheet2fullname).Column
intSheet1DOB = Range(Sheet1DOB).Column
intSheet2DOB = Range(Sheet2DOB).Column

Set rng = wks.Range(Sheet1ID).CurrentRegion
Set rngNew = wksb.Range(Sheet2ID).CurrentRegion
Set rngsheet1reltype = wks.Range(sheet1Reltype).CurrentRegion
Set rngsheet2reltype = wksb.Range(sheet2Reltype).CurrentRegion
Set rngsheet1sex = wks.Range(sheet1sex).CurrentRegion
Set rngsheet2sex = wksb.Range(sheet2sex).CurrentRegion
Set rngsheet1salcode = wks.Range(sheet1salcode).CurrentRegion
Set rngsheet2salcode = wksb.Range(sheet2salcode).CurrentRegion
Set rngsheet1fname = wks.Range(sheet1FName).CurrentRegion
Set rngsheet2Fname = wksb.Range(sheet2FName).CurrentRegion
Set rngsheet1mname = wks.Range(sheet1mname).CurrentRegion
Set rngsheet2mname = wksb.Range(sheet2mname).CurrentRegion
Set rngsheet1scode = wks.Range(sheet1scode).CurrentRegion
Set rngsheet2scode = wksb.Range(sheet2scode).CurrentRegion
Set rngsheet1fullname = wks.Range(sheet1fullname).CurrentRegion
Set rngsheet2fullname = wksb.Range(sheet2fullname).CurrentRegion
Set rngSheet1DOB = wks.Range(Sheet1DOB).CurrentRegion
Set rngSheet2DOB = wksb.Range(Sheet2DOB).CurrentRegion


'rngSheet1Name.IgnoreCase = True
'rngSheet2Name.IgnoreCase = True

intCount = rng.Rows.Count: intCount1 = rngNew.Rows.Count


For Each Row In rng.Rows
For Each cell In Row.Cells
For i = 2 To intCount
For j = 2 To intCount1


If (rng.EntireRow.Cells(i, intSheet1IDCol) = rngNew.EntireRow.Cells(j, intSheet2IDCol)) Then
rng.Cells(i, intSheet1IDCol).Interior.ColorIndex = 4
' cell.Value = LCase(cell.Value)

If (rngsheet1fname.EntireRow.Cells(i, intsheet1fname) = rngsheet2Fname.EntireRow.Cells(j, intsheet2fname)) Then
rngsheet1fname.Cells(i, intsheet1fname).Interior.ColorIndex = 4
End If
If (rngsheet1reltype.EntireRow.Cells(i, intsheet1reltype) = rngsheet2reltype.EntireRow.Cells(j, intsheet2reltype)) Then
rngsheet1reltype.Cells(i, intsheet1reltype).Interior.ColorIndex = 4
End If
If (rngsheet1sex.EntireRow.Cells(i, intsheet1sex) = rngsheet2sex.EntireRow.Cells(j, intsheet2sex)) Then
rngsheet1sex.Cells(i, intsheet1sex).Interior.ColorIndex = 4
End If
If (rngsheet1salcode.EntireRow.Cells(i, intsheet1salcode) = rngsheet2salcode.EntireRow.Cells(j, intsheet2salcode)) Then
rngsheet1salcode.Cells(i, intsheet1salcode).Interior.ColorIndex = 4
End If
If (rngsheet1fname.EntireRow.Cells(i, intsheet1fname) = rngsheet2Fname.EntireRow.Cells(j, intsheet2fname)) Then
rngsheet1fname.Cells(i, intsheet1fname).Interior.ColorIndex = 4
End If
If (rngsheet1mname.EntireRow.Cells(i, intsheet1mname) = rngsheet2mname.EntireRow.Cells(j, intsheet2mname)) Then
rngsheet1mname.Cells(i, intsheet1mname).Interior.ColorIndex = 4
End If
If (rngsheet1scode.EntireRow.Cells(i, intsheet1scode) = rngsheet2scode.EntireRow.Cells(j, intsheet2scode)) Then
rngsheet1scode.Cells(i, intsheet1scode).Interior.ColorIndex = 4
End If
If (rngsheet1fullname.EntireRow.Cells(i, intsheet1fullname) = rngsheet2fullname.EntireRow.Cells(j, intsheet2fullname)) Then
rngsheet1fullname.Cells(i, intsheet1fullname).Interior.ColorIndex = 4
End If
If (rngSheet1DOB.EntireRow.Cells(i, intSheet1DOB) = rngSheet2DOB.EntireRow.Cells(j, intSheet2DOB)) Then
rngSheet1DOB.Cells(i, intSheet1DOB).Interior.ColorIndex = 4
End If
End If
Next
If (rng.EntireRow.Cells(i, intSheet1IDCol).Interior.ColorIndex = xlNone) Then
rng.Cells(i, intSheet1IDCol).Interior.ColorIndex = 3
End If
If (rngsheet1fname.EntireRow.Cells(i, intsheet1fname).Interior.ColorIndex = xlNone) Then
rngsheet1fname.Cells(i, intsheet1fname).Interior.ColorIndex = 3
End If
If (rngSheet1DOB.EntireRow.Cells(i, intSheet1DOB).Interior.ColorIndex = xlNone) Then
rngSheet1DOB.Cells(i, intSheet1DOB).Interior.ColorIndex = 3
End If
If (rngsheet1reltype.EntireRow.Cells(i, intsheet1reltype).Interior.ColorIndex = xlNone) Then
rngsheet1reltype.Cells(i, intsheet1reltype).Interior.ColorIndex = 3
End If
If (rngsheet1sex.EntireRow.Cells(i, intsheet1sex).Interior.ColorIndex = xlNone) Then
rngsheet1sex.Cells(i, intsheet1sex).Interior.ColorIndex = 3
End If
If (rngsheet1salcode.EntireRow.Cells(i, intsheet1salcode).Interior.ColorIndex = xlNone) Then
rngsheet1salcode.Cells(i, intsheet1salcode).Interior.ColorIndex = 3
End If
If (rngsheet1mname.EntireRow.Cells(i, intsheet1mname).Interior.ColorIndex = xlNone) Then
rngsheet1mname.Cells(i, intsheet1mname).Interior.ColorIndex = 3
End If
If (rngsheet1scode.EntireRow.Cells(i, intsheet1scode).Interior.ColorIndex = xlNone) Then

rngsheet1scode.Cells(i, intsheet1scode).Interior.ColorIndex = 3
End If
If (rngsheet1fullname.EntireRow.Cells(i, intsheet1fullname).Interior.ColorIndex = xlNone) Then
rngsheet1fullname.Cells(i, intsheet1fullname).Interior.ColorIndex = 3
End If
If (rngSheet1DOB.EntireRow.Cells(i, intSheet1DOB).Interior.ColorIndex = xlNone) Then
rngSheet1DOB.Cells(i, intSheet1DOB).Interior.ColorIndex = 3
End If

Next
Next cell
Next Row
'For Each row In rngNew.Rows
' For Each cell In row.Cells
' For i = 2 To intCount
' For j = 2 To intCount1
' cell.Value = LCase(cell.Value)
' Next
' Next
' Next cell
'Next row

Set wks = Nothing: Set wksb = Nothing: Set wks3 = Nothing
'
'With Excel.Application
' .ScreenUpdating = True
' .Calculation = Excel.xlAutomatic
' .EnableEvents = True
'End With


'End Sub

'Public Sub UserForm_Activate()
'
'Dim ws As Worksheet
'Dim rang As Range
''Set ws = Worksheets("sheet1")
'Set rang = Worksheets("sheet1").Cells
'rang.EntireRow.Interior.ColorIndex = xlNone
'
''For Each Row In wks.Rows
'' For Each cell In Row.Cells
'' .EntireRow.Cells.Interior.ColorIndex = xlNone
'' Next cell
''Next Row
'End Sub

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

End Sub