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

 

[bobsan42]

something like this?

Sub Vlookup_Dynamic_Range()
    Dim myRow, LastRow As Long, myRng As Range
    With Sheet2
        myRow = 4
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row
        Worksheets("Sheet1").Activate


        Do While myRow <= LastRow
            Set myRng = Worksheets("Sheet2").Range(Cells(myRow, 1), Cells(myRow, 9))
            ActiveCell.Offset(myRow - 4, 0).Formula = "=VLOOKUP(A"& myrow & "," & myRng.Address(external:=True) & ", 3, false)"
            myRow = myRow + 1
        Loop
    End With
End Sub

 

[GirishDhruva]

Thank you @bobsan42 but it satisfied one condition of mine, another condition was to change my Column_index_number (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)

Can this condition be done??????

something like this?

Sub Vlookup_Dynamic_Range()
    Dim myRow, LastRow As Long, myRng As Range
    With Sheet2
        myRow = 4
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row
        Worksheets("Sheet1").Activate


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

Thank you in advance

 

[bobsan42]

Yes it can be done but the information is insufficient.
the column index number can be obtained in a lot of was - in code or even in the formula itself using Index or Hlookup.
Basically I think all you need is an INDEX+MATCH type of formula rather than VLOOKUP. And do you really need code to populate a formula. Careful constructing of the formula will allow flawless Fill-down.

 

[GirishDhruva]

Ya sorry for not providing sufficient data

I have two sheets in a workbook from which 2nd worksheet is the master one(where i would be having all the details), and from that sheet i should perform a VLOOKUP to get the column of "Cost Center" in sheet1, below are my tables and for that table i am trying to do vlookup

Master-sheet

Slno	Name	PF	Cost Center	ESI
1	abcd	12343	123	3465354
2	bcde	13424	124	1342153
3	cdef	563546	125	36554
4	defg	324435	126	34563
5	efgh	456544	127	3456
6	fghi	246534	128	345767

<tbody>
</tbody>

Sheet1

Slno	Name	PF	Cost Center	ESIC
9	abcd	23454	=vlookup	3415245
8	bcde	24354	=vlookup	2435
7	cdef	524	=vlookup	4353244
61	defg	5234	=vlookup	2452435
52	efgh	5234	=vlookup	2345
43	fghi	523	=vlookup	245

<tbody>
</tbody>

Thank you in advance

 

[bobsan42]

Are these columns from A to E?
You said that column index number may vary for each month??? What am I missing?

 

[GirishDhruva]

Yes they are from A but columns are many just for your understanding i gave this table example.

 

[GirishDhruva]

I think i am almost near but from the below code i am not getting the value of Cost Center, instead i am the values of next column of that Cost Center,
Can you help me with this

Sub Vlookup_Dynamic_Range()
    Dim myRow, LastRow As Long, myRng As Range
    Dim strSearch As String
    Dim aCell As Range
    
    strSearch = "Cost Center"
    Set aCell = Sheet1.Rows(3).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        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(A" & myRow & ", " & myRng.Address(external:=True) & ", " & aCell.Column & ", false)"
                myRow = myRow + 1


            Loop
        End With
    Else
        MsgBox ("Couldn't find Cost Center")
    Exit Sub
    End If


End Sub

Thank you in advance

 

[GirishDhruva]

Thank You @bobsan42 for your help and finally i found my solution, Cheers

Sub Vlookup_Dynamic_Range()
    Dim myRow, LastRow As Long, myRng As Range
    Dim strSearch As String
    Dim aCell As Range
    
    strSearch = "Cost Center"
    Set aCell = Sheet1.Rows(3).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        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(A" & myRow & ", " & myRng.Address(external:=True) & ", " & aCell.Column - 1 & ", false)"
                myRow = myRow + 1


            Loop
        End With
    Else
        MsgBox ("Couldn't find Cost Center")
    Exit Sub

Any modifications from the code would be greatfull

 

[GirishDhruva]

From my code i found an error that if the value is misplaced(means if the value is not in same row in both the tables) then i would get an error in vlookup(#N/A)
Like in below tables

Master-sheet

Slno	Name	PF	Cost Center	ESI
1	abcd	12343	123	3465354
2	bcde	13424	124	1342153
3	cdef	563546	125	36554
4	defg	324435	126	34563
5	efgh	456544	127	3456
6	fghi	246534	128	345767

<tbody>
</tbody>

Sheet1(After performing Vlookup i would get the error in Cost Center like below)

Slno	Name	PF	Cost Center	ESIC
1	abcd	23454	123	3415245
2	bcde	24354	124	2435
3	cdef	524	125	4353244
5	defg	5234	#N/A	2452435
6	efgh	5234	#N/A	2345
7	fghi	523	#N/A	245

<tbody>
</tbody>

If one value is misplaced then remaining all values i would get like #N/A itself

Can anyone help me how to solve this
Thank you in advance