1
\$\begingroup\$

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))
aData = cRange.Value

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
                 firstAddress = searchIndex.Address

              Do
                  searchIndex.Offset(columnoffset:=3).Copy
                  wsheet1.Range("B" & x).PasteSpecial
              Loop While Not searchIndex Is Nothing And searchIndex.Address <> firstAddress
             x = x + 1
           End If
           End With
           End If
           End If
   next i 
\$\endgroup\$
  • 4
    \$\begingroup\$ Just a friendly reminder: codereview.stackexchange.com/help/be-nice. Lashing out at people that help improve your post isn't going to get you anywhere. \$\endgroup\$ – Mathieu Guindon Jul 8 '17 at 22:19
  • 5
    \$\begingroup\$ 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. \$\endgroup\$ – Mathieu Guindon Jul 8 '17 at 22:27
  • \$\begingroup\$ @Mat'sMug I am sorry if I was being rude and disrespectful to anybody. I didn't mean it. I was frustrated with my work and can't figure out how to make it fast. I added all the variable and I am trying to use variant and going through loop and searching object. \$\endgroup\$ – user3795861 Jul 9 '17 at 2:54
  • \$\begingroup\$ 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 ;-) \$\endgroup\$ – Mathieu Guindon Jul 9 '17 at 3:04
  • \$\begingroup\$ @Mat'sMug Ya I just added. Basically wsheet1 and wsheet2 as Worksheets. Yes, I do get that a lot about indentation. I have to learn about it. \$\endgroup\$ – user3795861 Jul 9 '17 at 5:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.