VBA Vlookup function with dynamic ranges and double loop

[radsok8199]

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 ?

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

 

[radsok8199]

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 ?

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.

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]

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.

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]

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

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