Vlookup Error

G

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
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

Code: 
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
something like this?
Code: 
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
 
Upvote 0
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??????

bobsan42 said:
something like this?
Code: 
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
Click to expand...

Thank you in advance
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Slno[/TD]
[TD]Name[/TD]
[TD]PF[/TD]
[TD]Cost Center[/TD]
[TD]ESI[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]abcd[/TD]
[TD]12343[/TD]
[TD]123[/TD]
[TD]3465354[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]bcde[/TD]
[TD]13424[/TD]
[TD]124[/TD]
[TD]1342153[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]cdef[/TD]
[TD]563546[/TD]
[TD]125[/TD]
[TD]36554[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]defg[/TD]
[TD]324435[/TD]
[TD]126[/TD]
[TD]34563[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]efgh[/TD]
[TD]456544[/TD]
[TD]127[/TD]
[TD]3456[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]fghi[/TD]
[TD]246534[/TD]
[TD]128[/TD]
[TD]345767[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Slno[/TD]
[TD]Name[/TD]
[TD]PF[/TD]
[TD]Cost Center[/TD]
[TD]ESIC[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]abcd[/TD]
[TD]23454[/TD]
[TD]=vlookup[/TD]
[TD]3415245[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]bcde[/TD]
[TD]24354[/TD]
[TD]=vlookup[/TD]
[TD]2435[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]cdef[/TD]
[TD]524[/TD]
[TD]=vlookup[/TD]
[TD]4353244[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]defg[/TD]
[TD]5234[/TD]
[TD]=vlookup[/TD]
[TD]2452435[/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD]efgh[/TD]
[TD]5234[/TD]
[TD]=vlookup[/TD]
[TD]2345[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]fghi[/TD]
[TD]523[/TD]
[TD]=vlookup[/TD]
[TD]245[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance
 
Upvote 0
Are these columns from A to E?
You said that column index number may vary for each month??? What am I missing?
 
Upvote 0
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

Code: 
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
 
Upvote 0
Thank You @bobsan42 for your help and finally i found my solution, Cheers
Code: 
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
 
Upvote 0
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
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Slno[/TD]
[TD]Name[/TD]
[TD]PF[/TD]
[TD]Cost Center[/TD]
[TD]ESI[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]abcd[/TD]
[TD]12343[/TD]
[TD]123[/TD]
[TD]3465354[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]bcde[/TD]
[TD]13424[/TD]
[TD]124[/TD]
[TD]1342153[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]cdef[/TD]
[TD]563546[/TD]
[TD]125[/TD]
[TD]36554[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]defg[/TD]
[TD]324435[/TD]
[TD]126[/TD]
[TD]34563[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]efgh[/TD]
[TD]456544[/TD]
[TD]127[/TD]
[TD]3456[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]fghi[/TD]
[TD]246534[/TD]
[TD]128[/TD]
[TD]345767[/TD]
[/TR]
</tbody>[/TABLE]


Sheet1(After performing Vlookup i would get the error in Cost Center like below)
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Slno[/TD]
[TD]Name[/TD]
[TD]PF[/TD]
[TD]Cost Center[/TD]
[TD]ESIC[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]abcd[/TD]
[TD]23454[/TD]
[TD]123[/TD]
[TD]3415245[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]bcde[/TD]
[TD]24354[/TD]
[TD]124[/TD]
[TD]2435[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]cdef[/TD]
[TD]524[/TD]
[TD]125[/TD]
[TD]4353244[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]defg[/TD]
[TD]5234[/TD]
[TD]#N/A[/TD]
[TD]2452435[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]efgh[/TD]
[TD]5234[/TD]
[TD]#N/A[/TD]
[TD]2345[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]fghi[/TD]
[TD]523[/TD]
[TD]#N/A[/TD]
[TD]245[/TD]
[/TR]
</tbody>[/TABLE]


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
 
Upvote 0
You must log in or register to reply here.

Similar threads

rjplante
  • Solved
Loop Count Math issue
Replies
6
Views
344
Akuini
Akuini
B
  • Solved
Use Sheet1:D5 to find matching cell in Sheet2 Column A:A, and select cell (or row)
Replies
1
Views
429
brianv
B
L
  • Solved
Concat Rows if Column A matches
Replies
3
Views
705
Peter_SSs
Peter_SSs
A
  • Solved
Target.address and VLOOKUP with a range of cells
Replies
8
Views
398
RoryA
RoryA
A
  • Solved
setting a range from Active cell to last cell when Active cell change columns
2
Replies
12
Views
608
jolivanes
jolivanes

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top