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, If I want to return values in column E and the column index in another sheet is 2, I just change the referencing to use two arrays to lookup the column numbers.
However, instead of using two arrays, is it possible I give the column numbers from certain cells in excel sheet?
e.g)
AS-IS Need return values in column E, F, G, Z (in targetcols array it is 5,7,9,26) in another sheet column index 2, 5, 15, 27.
TO-BE Cell A2 - mentioned 5, Cell A3 - mentioned 7, Cell A4-9, Cell A5-26 / In Cell B2 - mentioned 2, Cell B3 - mentioned 5, ....15....27.

Does VBA have such funtion?

Sub Vlookup_Entire_Author_Sheet()

Dim AuthorWs As Worksheet, DetailsWs As Worksheet
Dim AuthorLastRow As Long, DetailsLastRow As Long, x As Long
Dim dataRng As Range

Set AuthorWs = ThisWorkbook.Worksheets("Author")
Set DetailsWs = ThisWorkbook.Worksheets("Details")

AuthorLastRow = AuthorWs.Range("A" & Rows.Count).End(xlUp).Row
DetailsLastRow = DetailsWs.Range("A" & Rows.Count).End(xlUp).Row

Set dataRng = DetailsWs.Range("A2:DU" & DetailsLastRow)

' E , G, I, Z / 2,5,15, 27
targetcols = Array(5, 7, 9, 26)
srccols = Array(2, 5, 15, 27)
For x = 2 To AuthorLastRow
For y = 0 To 3
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

End Sub

 

[PeteWright]

I'm not sure, if it is possible, because your array values don't seem to follow a pattern.
e.g. if your Array was like A={1,3,5,7,9} and B={2,4,6,8,10} and your counter variable (lets say) i changes steps of 1 starting at 0, then the pattern to replace the array could be:
A(i) = 2*i +1 or B(i) = 1 + 2*i + 1

But your data - targetcols = {5, 7, 9, 26} and srccols = {2, 5, 15, 27} - doesn't seem to have anything in common.
At least nothing I can see...

VBA offers many functions, but they all follow some logic or rules. If you can tell me the logic of your data or a rule it follows, maybe something will work.

 

[Jonnny]

Author Tab

A	B	C	D	E	F	G	H	I	J	K
Row1		age			weight			height		cost
Row2

Details Tab

A	B	C	D	E	F	G	H	I	J	K
Row1	age		weight				height			cost
Row2	35​		70​				170​			50​

Unfortunately, there is no specific pattern. The order of columns in Author tab always changes. So far, I modified manually the column number in Author tab.
But I am wondering if it is possible instead of modifying the column number every time, I put the column number in cells and VBA follow the column number from the cells.


Aligning Tab

A	B	C	D	E	F	G	H	I	J	K
Row1	3​		6​				9​			11​
Row2	age		weight				height			cost
Row3	35​		70​				170​			50​

As above, I have entered 3, 6, 9, 11 in the aligning tab and is it possible VBA repeat the value in Author tab?

Sub Vlookup_Entire_Author_Sheet()

Dim AuthorWs As Worksheet, DetailsWs As Worksheet
Dim AuthorLastRow As Long, DetailsLastRow As Long, x As Long
Dim dataRng As Range

Set AuthorWs = ThisWorkbook.Worksheets("Author")
Set DetailsWs = ThisWorkbook.Worksheets("Details")

AuthorLastRow = AuthorWs.Range("A" & Rows.Count).End(xlUp).Row
DetailsLastRow = DetailsWs.Range("A" & Rows.Count).End(xlUp).Row

Set dataRng = DetailsWs.Range("A2:DU" & DetailsLastRow)

' C, F, I, K / 2,4,8,11
targetcols = Array(3, 6, 9, 11)
srccols = Array(2, 4, 8, 11)
For x = 2 To AuthorLastRow
For y = 0 To 3
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

End Sub

 

[Alex Blakenburg]

Is there any reason you can't use match using the headings to work out the column number ?