VBA Vlookup function with dynamic ranges and double loop

radsok8199

New Member
Joined
Dec 4, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile
Dear VBA Masters

I wrote code to use Vlookup. It seems like there is still some issue and I cannot get what is wrong.
So on Sheet1 I have a Table2. Column C of it is my primary key ( lookup value).
On Sheet2 I have Table1. Column A contains primary key ( lookup value) but in different order. This table contains amount of columns with values

So below code should take look up values from Table2 and search row for with it in Table 1. Get value from this row for each column ( column number index will change with loop) and place this value in requested location. Once all rows are done it will do same for another column.

However it loops only for 11 rows and place values 2 columns forward then comes with Run time error 1004.

It should start placing values 2 column ahead from table1.
What i am doing wrong here ?

VBA Code:
Sub loopTest()

Dim shtE As Worksheet
Dim shtS As Worksheet
Dim c As Long
Dim r As Long
Dim lrowS As Long
Dim lcolS As Long
Dim lcolE As Long
Dim Incol As Long


Set shtS = ThisWorkbook.Worksheets("Sheet1")
Set shtE = ThisWorkbook.Worksheets("Sheet2")
lrowS = shtS.Cells(shtS.Rows.Count, "C").End(xlUp).Row
lcolS = shtS.ListObjects("Table2").Range.Columns.Count
lcolE = shtE.ListObjects("Table1").Range.Columns.Count


For c = 2 To lcolE
For r = 2 To lrowS Step 1

Incol = lcolS + c
shtS.Cells(r, Incol + c).Value = WorksheetFunction.VLookup(shtS.Cells(r, 3).Value, shtE.Range("Table1"), c, 0)

Next r
Next c

End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

radsok8199

New Member
Joined
Dec 4, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile
Dear VBA Masters

I wrote code to use Vlookup. It seems like there is still some issue and I cannot get what is wrong.
So on Sheet1 I have a Table2. Column C of it is my primary key ( lookup value).
On Sheet2 I have Table1. Column A contains primary key ( lookup value) but in different order. This table contains amount of columns with values

So below code should take look up values from Table2 and search row for with it in Table 1. Get value from this row for each column ( column number index will change with loop) and place this value in requested location. Once all rows are done it will do same for another column.

However it loops only for 11 rows and place values 2 columns forward then comes with Run time error 1004.

It should start placing values 2 column ahead from table1.
What i am doing wrong here ?

VBA Code:
Sub loopTest()

Dim shtE As Worksheet
Dim shtS As Worksheet
Dim c As Long
Dim r As Long
Dim lrowS As Long
Dim lcolS As Long
Dim lcolE As Long
Dim Incol As Long


Set shtS = ThisWorkbook.Worksheets("Sheet1")
Set shtE = ThisWorkbook.Worksheets("Sheet2")
lrowS = shtS.Cells(shtS.Rows.Count, "C").End(xlUp).Row
lcolS = shtS.ListObjects("Table2").Range.Columns.Count
lcolE = shtE.ListObjects("Table1").Range.Columns.Count


For c = 2 To lcolE
For r = 2 To lrowS Step 1

Incol = lcolS + c
shtS.Cells(r, Incol + c).Value = WorksheetFunction.VLookup(shtS.Cells(r, 3).Value, shtE.Range("Table1"), c, 0)

Next r
Next c

End Sub
Ok so i mannage to spot my mistake , and after correction values are placed in correct column. However still loop goes through only 11 rows and that i dont see why.
VBA Code:
Sub loopTest()

Dim shtE As Worksheet
Dim shtS As Worksheet
Dim c As Long
Dim r As Long
Dim lrowS As Long
Dim lcolS As Long
Dim lcolE As Long


Set shtS = ThisWorkbook.Worksheets("Sheet1")
Set shtE = ThisWorkbook.Worksheets("Sheet2")
lrowS = shtS.Cells(shtS.Rows.Count, "C").End(xlUp).Row
lcolS = shtS.ListObjects("Table2").Range.Columns.Count
lcolE = shtE.ListObjects("Table1").Range.Columns.Count


For c = 2 To lcolE
For r = 2 To lrowS Step 1

shtS.Cells(r, lcolS + c).Value = WorksheetFunction.VLookup(shtS.Cells(r, 3).Value, shtE.Range("Table1"), c, 0)

Next r
Next c

End Sub
 

radsok8199

New Member
Joined
Dec 4, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile
Ok so i mannage to spot my mistake , and after correction values are placed in correct column. However still loop goes through only 11 rows and that i dont see why.
VBA Code:
Sub loopTest()

Dim shtE As Worksheet
Dim shtS As Worksheet
Dim c As Long
Dim r As Long
Dim lrowS As Long
Dim lcolS As Long
Dim lcolE As Long


Set shtS = ThisWorkbook.Worksheets("Sheet1")
Set shtE = ThisWorkbook.Worksheets("Sheet2")
lrowS = shtS.Cells(shtS.Rows.Count, "C").End(xlUp).Row
lcolS = shtS.ListObjects("Table2").Range.Columns.Count
lcolE = shtE.ListObjects("Table1").Range.Columns.Count


For c = 2 To lcolE
For r = 2 To lrowS Step 1

shtS.Cells(r, lcolS + c).Value = WorksheetFunction.VLookup(shtS.Cells(r, 3).Value, shtE.Range("Table1"), c, 0)

Next r
Next c

End Sub
A bit more to above
Ok so I managed to spot my mistake , and after correction values are placed in correct column. However still loop goes through only 11 rows and that I don't see why. I believe it might have been caused by lookup values. As I am 100% sure lookup values from lookup column and lookup values from table it looks through are identical. However I know there might be some values in lookup column that are not exist in lookup value column of the searched table. For those rows vlookup should give value #N/A . And Vice Versa column with values we look up for in searched Table1, have some values that are not present in look up values column. Those wont be used and shown. Is this might be an issue why it works only through 11 rows ?
 

radsok8199

New Member
Joined
Dec 4, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile
A bit more to above
Ok so I managed to spot my mistake , and after correction values are placed in correct column. However still loop goes through only 11 rows and that I don't see why. I believe it might have been caused by lookup values. As I am 100% sure lookup values from lookup column and lookup values from table it looks through are identical. However I know there might be some values in lookup column that are not exist in lookup value column of the searched table. For those rows vlookup should give value #N/A . And Vice Versa column with values we look up for in searched Table1, have some values that are not present in look up values column. Those wont be used and shown. Is this might be an issue why it works only through 11 rows ?
Solved :)

Admin please remove this thread as I conversation wit my self :) and should be ashamed :)

VBA Code:
Sub loopTestVlookup()

Dim shtE As Worksheet
Dim shtS As Worksheet
Dim c As Long
Dim r As Long
Dim lrowS As Long
Dim lcolS As Long
Dim lcolE As Long
Dim x As Variant
Dim Lvalue As String

Set shtS = ThisWorkbook.Worksheets("Sheet1")
Set shtE = ThisWorkbook.Worksheets("Sheet2")
lrowS = shtS.Cells(shtS.Rows.Count, "C").End(xlUp).Row
lcolS = shtS.ListObjects("Table2").Range.Columns.Count
lcolE = shtE.ListObjects("Table1").Range.Columns.Count


For c = 2 To lcolE
For r = 2 To lrowS Step 1

Lvalue = shtS.Cells(r, 3).Value

x = Application.VLookup(Lvalue, shtE.Range("Table1"), c, 0)

If IsError(Lvalue) Then
shtS.Cells(r, lcolS + c).Value = x = "#N/A"
Else
shtS.Cells(r, lcolS + c).Value = x
End If

Next r
Next c

End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,931
Messages
5,639,064
Members
417,067
Latest member
rohitbabshet

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
Top