# Looping through one million rows and corresponding columns

I'm trying to find some name, go to the corresponding column, and then search and go back to corresponding name and paste in a different worksheet. This code basically first finds the name then goes to the corresponding column and stores the value. It searches for that value in another column and goes to the corresponding column after the search and copies the corresponding column values and pastes in another worksheet.

Is it possible to make this code execute in less time? I want to it run for less than 10 secs. I am working on around max rows and columns.

  Dim mRange As Range, lastrow As Long, coresponC as String, wsheet1 as Worksheet, wsheet2 as Worksheet
Dim xoffset As Object, searchIndex As Object, connData as Variant, aData as Variant, alldata2 as Variant, cRange As Range

Set cRange = wsheet2.Range(wsheet2.Range("C1"), wsheet2.Cells(Rows.Count, 1).End(xlUp))

Set mRange = wsheet2.Range(wsheet2.Range("A1"), wsheet2.Cells(Rows.Count, 1).End(xlUp))
alldata2 = indexRange.Value
lastrow = wsheet2.UsedRange.Rows.Count + 1

For i = 1 To UBound(aData, 1)
If wsheet2.Cells(i, 3).Value = "Mic" Or wsheet2.Cells(i, 3) = "Ram" Or wsheet2.Cells(i, 3) = "Sam" Or wsheet2.Cells(i, 3) = "Tom" Then
Set coresponC = wsheet2.Cells(i, 3)
Set xofset = coresponC.Offset(columnoffset:=7)

If InStr(1, xoffset, "1.") <> 0 Then
With wsheet2.Range("a1:a" & lastrow)
Set searchIndex = .Find(wsheet2.Range("J" & i).Value, LookIn:=xlValues)

If Not searchIndex Is Nothing Then

Do
searchIndex.Offset(columnoffset:=3).Copy
wsheet1.Range("B" & x).PasteSpecial

• As a VBA reviewer I like seeing a whole scope, as opposed to a little snippet. Are these variables declared anywhere? Is the rest of the procedure 500 lines? You haven't even posted the entire loop body, that For loop doesn't have a Next statement; that makes it rather hard to help improve a loop, if we don't even know everything it does. – Mathieu Guindon Jul 8 '17 at 22:27
• No problem, thanks for editing. Does the code look like this in the VBE? Is this the whole procedure? How is it invoked, does it have a meaningful name? Are wsSheet1 and wsSheet2 parameters or globals? That's what I mean with I prefer seeing a whole scope - it's easier to see the big picture, and you get the best value out of this site, because reviewers will pick up on everything... including the indentation ;-) – Mathieu Guindon Jul 9 '17 at 3:04