parth007
01-21-2015, 06:12 AM
HELLO aLL BELOW IS THE CODE.. I TRIED AND IT CLICKED...
Sub Update()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rngNames As Range
Dim NameCell As Range
Dim rngFound As Range
Dim arrResults() As Variant
Dim ResultIndex As Long
Dim strFirst As String
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set rngNames = ws1.Range("B2", ws1.Cells(Rows.Count, "B").End(xlUp))
If rngNames.Row < 2 Then Exit Sub 'No data
ReDim arrResults(1 To 51, 1 To 65000)
ResultIndex = 1
arrResults(1, ResultIndex) = "Column1"
arrResults(2, ResultIndex) = "Column2"
arrResults(3, ResultIndex) = "Column3"
arrResults(4, ResultIndex) = "Column4"
arrResults(5, ResultIndex) = "Column5"
arrResults(6, ResultIndex) = "Column6"
arrResults(7, ResultIndex) = "Column7"
arrResults(8, ResultIndex) = "Column8"
arrResults(9, ResultIndex) = "Column9"
arrResults(10, ResultIndex) = "Column10"
arrResults(11, ResultIndex) = "Column11"
arrResults(12, ResultIndex) = "Column12"
arrResults(13, ResultIndex) = "Column13"
arrResults(14, ResultIndex) = "Column14"
arrResults(15, ResultIndex) = "Column15"
arrResults(16, ResultIndex) = "Column16"
arrResults(17, ResultIndex) = "Column17"
arrResults(18, ResultIndex) = "Column18"
arrResults(19, ResultIndex) = "Column19"
arrResults(20, ResultIndex) = "Column20"
arrResults(21, ResultIndex) = "Column21"
arrResults(22, ResultIndex) = "Column22"
arrResults(23, ResultIndex) = "Column23"
arrResults(24, ResultIndex) = "Column24"
arrResults(25, ResultIndex) = "Column25"
arrResults(26, ResultIndex) = "Column26"
arrResults(27, ResultIndex) = "Column27"
arrResults(28, ResultIndex) = "Column28"
arrResults(29, ResultIndex) = "Column29"
arrResults(30, ResultIndex) = "Column30"
arrResults(31, ResultIndex) = "Column31"
arrResults(32, ResultIndex) = "Column32"
arrResults(33, ResultIndex) = "Column33"
arrResults(34, ResultIndex) = "Column34"
arrResults(35, ResultIndex) = "Column35"
arrResults(36, ResultIndex) = "Column36"
arrResults(37, ResultIndex) = "Column37"
arrResults(38, ResultIndex) = "Column38"
arrResults(39, ResultIndex) = "Column39"
arrResults(40, ResultIndex) = "Column40"
arrResults(41, ResultIndex) = "Column41"
arrResults(42, ResultIndex) = "Column42"
arrResults(43, ResultIndex) = "Column43"
arrResults(44, ResultIndex) = "Column44"
arrResults(45, ResultIndex) = "Column45"
arrResults(46, ResultIndex) = "Column46"
arrResults(47, ResultIndex) = "Column47"
arrResults(48, ResultIndex) = "Column48"
arrResults(49, ResultIndex) = "Column49"
arrResults(50, ResultIndex) = "Column50"
arrResults(51, ResultIndex) = "Column51"
For Each NameCell In rngNames.Cells
Set rngFound = ws2.Columns("A").Find(NameCell.Value, ws2.Cells(Rows.Count, "A"), xlValues, xlWhole)
If Not rngFound Is Nothing Then
strFirst = rngFound.Address
Do
If ws2.Cells(rngFound.Row, "A").Value = ws1.Cells(NameCell.Row, "B").Value _
And ws2.Cells(rngFound.Row, "B").Value = ws1.Cells(NameCell.Row, "O").Value _
And ws2.Cells(rngFound.Row, "H").Value = ws1.Cells(NameCell.Row, "J").Value _
And ws2.Cells(rngFound.Row, "W").Value = ws1.Cells(NameCell.Row, "G").Value Then
ResultIndex = ResultIndex + 1
arrResults(1, ResultIndex) = NameCell.Value
arrResults(2, ResultIndex) = ws2.Cells(rngFound.Row, "B").Value
arrResults(3, ResultIndex) = ws2.Cells(rngFound.Row, "C").Value
arrResults(4, ResultIndex) = ws2.Cells(rngFound.Row, "D").Value
arrResults(5, ResultIndex) = ws2.Cells(rngFound.Row, "E").Value
arrResults(6, ResultIndex) = ws2.Cells(rngFound.Row, "F").Value
arrResults(7, ResultIndex) = ws2.Cells(rngFound.Row, "G").Value
arrResults(8, ResultIndex) = ws2.Cells(rngFound.Row, "H").Value
arrResults(9, ResultIndex) = ws2.Cells(rngFound.Row, "I").Value
arrResults(10, ResultIndex) = ws2.Cells(rngFound.Row, "J").Value
arrResults(11, ResultIndex) = ws2.Cells(rngFound.Row, "K").Value
arrResults(12, ResultIndex) = ws2.Cells(rngFound.Row, "L").Value
arrResults(13, ResultIndex) = ws2.Cells(rngFound.Row, "M").Value
arrResults(14, ResultIndex) = ws2.Cells(rngFound.Row, "N").Value
arrResults(15, ResultIndex) = ws2.Cells(rngFound.Row, "O").Value
arrResults(16, ResultIndex) = ws2.Cells(rngFound.Row, "P").Value
arrResults(17, ResultIndex) = ws2.Cells(rngFound.Row, "Q").Value
arrResults(18, ResultIndex) = ws2.Cells(rngFound.Row, "R").Value
arrResults(19, ResultIndex) = ws2.Cells(rngFound.Row, "S").Value
arrResults(20, ResultIndex) = ws2.Cells(rngFound.Row, "T").Value
arrResults(21, ResultIndex) = ws2.Cells(rngFound.Row, "U").Value
arrResults(22, ResultIndex) = ws2.Cells(rngFound.Row, "V").Value
arrResults(23, ResultIndex) = ws2.Cells(rngFound.Row, "W").Value
arrResults(24, ResultIndex) = ws2.Cells(rngFound.Row, "X").Value
arrResults(25, ResultIndex) = ws2.Cells(rngFound.Row, "Y").Value
arrResults(26, ResultIndex) = ws2.Cells(rngFound.Row, "Z").Value
arrResults(27, ResultIndex) = ws2.Cells(rngFound.Row, "AA").Value
arrResults(28, ResultIndex) = ws2.Cells(rngFound.Row, "AB").Value
arrResults(29, ResultIndex) = ws2.Cells(rngFound.Row, "AC").Value
arrResults(30, ResultIndex) = ws2.Cells(rngFound.Row, "AD").Value
arrResults(31, ResultIndex) = ws2.Cells(rngFound.Row, "AE").Value
arrResults(32, ResultIndex) = ws2.Cells(rngFound.Row, "AF").Value
arrResults(33, ResultIndex) = ws2.Cells(rngFound.Row, "AG").Value
arrResults(34, ResultIndex) = ws2.Cells(rngFound.Row, "AH").Value
arrResults(35, ResultIndex) = ws2.Cells(rngFound.Row, "AI").Value
arrResults(36, ResultIndex) = ws2.Cells(rngFound.Row, "AJ").Value
arrResults(37, ResultIndex) = ws2.Cells(rngFound.Row, "AK").Value
arrResults(38, ResultIndex) = ws2.Cells(rngFound.Row, "AL").Value
arrResults(39, ResultIndex) = ws2.Cells(rngFound.Row, "AM").Value
arrResults(40, ResultIndex) = ws2.Cells(rngFound.Row, "AN").Value
arrResults(41, ResultIndex) = ws2.Cells(rngFound.Row, "AO").Value
arrResults(42, ResultIndex) = ws2.Cells(rngFound.Row, "AP").Value
arrResults(43, ResultIndex) = ws2.Cells(rngFound.Row, "AQ").Value
arrResults(44, ResultIndex) = ws2.Cells(rngFound.Row, "AR").Value
arrResults(45, ResultIndex) = ws2.Cells(rngFound.Row, "AS").Value
arrResults(46, ResultIndex) = ws2.Cells(rngFound.Row, "AT").Value
arrResults(47, ResultIndex) = ws2.Cells(rngFound.Row, "AU").Value
arrResults(48, ResultIndex) = ws2.Cells(rngFound.Row, "AV").Value
arrResults(49, ResultIndex) = ws2.Cells(rngFound.Row, "AW").Value
arrResults(50, ResultIndex) = ws2.Cells(rngFound.Row, "AX").Value
arrResults(51, ResultIndex) = ws2.Cells(rngFound.Row, "AY").Value
End If
Set rngFound = ws2.Columns("A").Find(NameCell.Value, rngFound, xlValues, xlWhole)
Loop While rngFound.Address <> strFirst
End If
Next NameCell
If ResultIndex > 1 Then
ReDim Preserve arrResults(1 To 51, 1 To ResultIndex)
'To override existing data
Sheets("Sheet4").UsedRange.ClearContents
Sheets("Sheet4").Range("A1").Resize(UBound(arrResults, 2), UBound(arrResults, 1)).Value = Application.Transpose(arrResults)
'If you instead want to add data to the bottom of existing results, uncomment next line
'Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(UBound(arrResults, 2), UBound(arrResults, 1)).Value = Application.Transpose(arrResults)
Sheets("Sheet4").Select
MsgBox ResultIndex - 1 & " matches found."
Else
MsgBox "No matches found"
End If
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.