Code:
Sub GetNSEData()
Dim varFilteredSource() As Variant
Dim rngSourceRange As Range
Dim lngRow As Long, lngCol As Long
Set rngSourceRange = Workbooks("1.xlsx").Worksheets("Sheet1").Cells(1).CurrentRegion
lngRow = rngSourceRange.Rows.Count
ReDim varFilteredSource(1 To lngRow, 1 To 2)
For lngRow = 2 To lngRow
varFilteredSource(lngRow, 1) = rngSourceRange.Cells(lngRow, 1).Value
varFilteredSource(lngRow, 2) = rngSourceRange.Cells(lngRow, 2).Value
For lngCol = 2 To rngSourceRange.Columns.Count - 1
If rngSourceRange.Cells(lngRow, lngCol).Interior.ColorIndex <> -4142 Then
varFilteredSource(lngRow, 2) = rngSourceRange.Cells(lngRow, lngCol + 1).Value
Exit For
End If
Next lngCol
Next lngRow
With Workbooks("2.xlsx").Worksheets("Sheet1")
.Range("M1").Resize(lngRow - 1, 2).Value = varFilteredSource
.Range("L2").Formula = "=IFERROR(VLOOKUP(B2,$M$2:$N$" & lngRow - 1 & ",2,0),"""")"
.Range("L2").AutoFill Destination:=.Range("L2:L" & .Cells(Rows.Count, 1).End(xlUp).Row)
With .Range("L2:L" & .Cells(Rows.Count, 1).End(xlUp).Row)
.Value = .Value
End With
.Range("M1").Resize(lngRow - 1, lngCol - 1).ClearContents
End With
Erase varFilteredSource
Set rngSourceRange = Nothing
End Sub
Reply With Quote
Bookmarks