Vlookup Error

[GirishDhruva]

Hi Everyone,

Here i am trying with Vlookup for a dynamic range of values , but if i run the below code i would only get the value for that particular 'A4' and remaining all values would be in #N/A.

And i am trying the lookup_Value and column_index_number also in a dynamic way(means col_index_number might vary for every month so i need to find that column through column names and from that column names i should find the column number and place that in the formula) , and for lookup_value it should increment by 1 after every loop, can this be done ?????????

where from formula highlighted ,A4 is lookup_value and 3 is the column_index_number

Sub Vlookup_Dynamic_Range()
    Dim myRow, LastRow As Long, myRng As Range
    With Sheet2


        myRow = 4
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row


        Do While myRow <= LastRow
        
            Worksheets("Sheet2").Activate
            Set myRng = Range(Cells(myRow, 1), Cells(myRow, 9))
            Worksheets("Sheet1").Activate
            ActiveCell.Offset(myRow - 4, 0).Formula = "=VLOOKUP([COLOR=#ff0000]A4[/COLOR], " & myRng.Address(external:=True) & ", [COLOR=#ff0000]3[/COLOR], false)"
            myRow = myRow + 1


        Loop
    End With


End Sub

Thank you in prior

 

[GirishDhruva]

How can i make my table to absolute reference so that my table values wouldn't be changed and i wouldn't get the error #N/A for the above code

Thank you in advance