Repeat VBA for each column

[Jonnny]

Hello,

I am using vlookup to return values in each column and I try to have a VBA set-up.
Currently with below VBA, I need to change "E" and 2 in each column.
e.g) I want to return values in column E and the column index in another sheet is 2. Next column, I change column "F" and the column index in another sheet is 3. Next, column "G" and column index 4.......
Is there a way to repeat it using VBA?

authorWs.Range("E" & x).Value = Application.WorksheetFunction.vlookup( _
authorWs.Range("A" & x).Value, dataRng, 2, False)

Sub Vlookup_Entire_Sheet()

Dim authorWs As Worksheet, detailsWs As Worksheet
Dim authorsLastRow As Long, detailsLastRow As Long, x As Long
Dim dataRng As Range

Set authorWs = ThisWorkbook.Worksheets("Entire At once")
Set detailsWs = ThisWorkbook.Worksheets("Details")

authorsLastRow = authorWs.Range("A" & Rows.Count).End(xlUp).Row
detailsLastRow = detailsWs.Range("A" & Rows.Count).End(xlUp).Row

Set dataRng = detailsWs.Range("A3:CI" & detailsLastRow)

For x = 2 To authorsLastRow
On Error Resume Next
authorWs.Range("E" & x).Value = Application.WorksheetFunction.vlookup( _
authorWs.Range("A" & x).Value, dataRng, 2, False)
Next x

End Sub

 

[offthelip]

try this changing this:

For x = 2 To authorsLastRow
On Error Resume Next
authorWs.Range("E" & x).Value = Application.WorksheetFunction.vlookup( _
authorWs.Range("A" & x).Value, dataRng, 2, False)
Next x

to:

For x = 2 To authorsLastRow
For y = 1 To 4 ' assuming repeat for 4 columns
On Error Resume Next
authorws.Range(Cells(x, 4 + y), Cells(x, 4 + y)).Value = Application.WorksheetFunction.VLookup( _
authorws.Range("A" & x).Value, dataRng, (1 + y), False)
Next y
Next x

 

[Jonnny]

try this changing this:

For x = 2 To authorsLastRow
On Error Resume Next
authorWs.Range("E" & x).Value = Application.WorksheetFunction.vlookup( _
authorWs.Range("A" & x).Value, dataRng, 2, False)
Next x

to:

For x = 2 To authorsLastRow
For y = 1 To 4 ' assuming repeat for 4 columns
On Error Resume Next
authorws.Range(Cells(x, 4 + y), Cells(x, 4 + y)).Value = Application.WorksheetFunction.VLookup( _
authorws.Range("A" & x).Value, dataRng, (1 + y), False)
Next y
Next x

Thanks a lot. It is working.

Is it also possible to loop specific columns?
e.g) I want to return values in column E and the column index in another sheet is 2. Next column, I change column "G" and the column index in another sheet is 5. Next, column "I" and column index 15. Next column "Z", column Index 27, etc

 

[offthelip]

yes that is very easily done: since there is a "columns" loop already we just change the referencing to use two arrays to lookup the correct column numbers:

' E , G , I ,Z
targetcols = Array(5, 7, 9, 26)
srccols = Array(2, 5, 15, 27)
For x = 2 To authorsLastRow
For y = 0 To 3 ' assuming repeat for 4 columns
On Error Resume Next
authorWs.Range(Cells(x, targetcols(y)), Cells(x, targetcols(y))).Value = Application.WorksheetFunction.VLookup( _
authorWs.Range("A" & x).Value, dataRng, srccols(y), False)
Next y
Next x

 

[Jonnny]

Thanks a lot. It is working well.
Would it be even possible to have this function only for filtered range? or even from not opened workbook from a certain folder?