Results 1 to 20 of 24

Thread: Speed up Macro

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,970
    …but it's not your current code, is it? No mention of Sheet2 at all!
    Try:
    Sub GetValues2()
    Sheet1LastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Sheet2LastRow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    With Worksheets("Sheet1").Range("B1:B" & Sheet1LastRow)
      .FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R1C1:R" & Sheet2LastRow & "C2,2,FALSE)"
      .SpecialCells(xlCellTypeFormulas, 16).ClearContents
      .Value = .Value
    End With
    End Sub
    If it's still too slow then we can try again.
    Perhaps:
    Sub GetValues3()
    Set rng1 = Range(Worksheets("Sheet1").Range("A1"), Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
    Set rng2 = Range(Worksheets("Sheet2").Range("A1"), Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp)).Resize(, 2)
    cc = Application.VLookup(rng1, rng2, 2, False)
    For i = 1 To UBound(cc)
      If IsError(cc(i, 1)) Then cc(i, 1) = Empty
    Next i
    rng1.Offset(, 1).Value = cc
    End Sub
    Last edited by p45cal; 09-30-2018 at 11:37 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •