How to optimize the vba code to search value from different sheet

Question

I have written this below code to automate search function for a value T5536 which is in A1 cell of sheet1 and compare the A1 cell value with a column from sheet2 which has n number of values. When the A1 value T5536 matches the value from Sheet2 A column then it should update the Sheet1 with Corresponding ES or IS values. If the ES value in Sheet2 has Indirect word or string then it should update IS value in sheet1.

Please find the below code for the same :-

Sub test()
Dim lrow As Long
Dim i, j As Variant
Dim ms, ws As Worksheet 
Dim num, esr, isr,x As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ms = Worksheets("sheet1")
Worksheets("Sheet2").Activate
ms.Cells(2, 3) = ""
ms.Cells(2, 2) = ""

Set ws = Worksheets("Sheet2")

num = WorksheetFunction.Match("number", Range("1:1"), 0)
esr = WorksheetFunction.Match("ES", Range("1:1"), 0)
isr = WorksheetFunction.Match("IS", Range("1:1"), 0)

x = sheet2.cells(sheet2.rows.count,"A").end(xlup).row
FoundRange = ms.Range("A1")
For i = 2 To x

If ws.Cells(i, num) = FoundRange Then
Worksheets("sheet1").Activate
ms.Cells(2, 3) = ws.Cells(i, isr)
 If ws.Cells(i, es) = "indirect" Then
    ms.Cells(2, 2) = ws.Cells(i, is)
    Else
    ms.Cells(2, 2) = ws.Cells(i, es)
End If
End If

If ms.Cells(2, 2) <> "" Then
Exit For
End If
Next i
End Sub

The following code will work and takes less time when there are only few values to match in sheet2 A column, but if there are n number of values in sheet2 then it will be difficult to go through for loop and fulfil the task, kindly help me in tweaking this code to search the value very fast and update the corresponding values.

I have attached the images which might help to analyse the query.

Is there any reason that you don't just use Excel's built-in VLookup function? You seem to be doing nothing more than implementing an inefficient version of it. — John Coleman
dim x as long then x=sheet2.cells(sheet2.rows.count,"A").end(xlup).row this will stop the code looping over 65000 times..This is assuming Column A is where all the rows are, change to the column you require. — Davesexcel
@John Coleman I have to finish the task in VBA programming only hence requested in VBA, else vlookup is very easy task to finish the same. — lifeinvba
@Davesexcel Thanks for the reply , As advised by you, I have edited the code in the post, However the code is still looping through each row and not searching directly the value and updating the cells, please confirm whether there are any more changes to be done in the code — lifeinvba

Davesexcel Davesexcel · Accepted Answer · 2015-08-14T10:24:35

Check it out. You can edit this code as you require.

    Sub loopExample()
    Dim sh As Worksheet, ws As Worksheet
    Dim LstRw As Long, Frng As Range
    Dim rng As Range, c As Range, x

    Set sh = Sheets("Sheet1")
    Set ws = Sheets("Sheet2")
    Set Frng = sh.Range("A1")

    With ws
        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A2:A" & LstRw)
    End With

    For Each c In rng.Cells
        If c = Frng Then
            x = IIf(c.Offset(0, 1) = "indirect", 2, 1)
            sh.Range("B2") = c.Offset(0, x)
        End If
    Next c

End Sub

How to optimize the vba code to search value from different sheet

1 Answers