Vlookup VBA

[radicaldude2]

Using the help of another member on this forum, I was able to come up with this code:

Sub my_vlookup()
Dim answer As Integer
answer = InputBox("How many columns would you like?")
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],Sheet1!R6C1:R187C13," & answer & ",0) "
Range("G15").Select
Selection.AutoFill Destination:=Range("G15:G37"), Type:=xlFillDefault
Range("G16:G36").Select
Selection.ClearContents

End Sub


This code works fine but it requires a bit of work from the user. For example, the user has to manually click each sheet and plug in what column he wants. Ideally, I would like sheet 1 to pull column 5, sheet 2 to pull column 6, sheet 3 to pull column 7 etc. To try and accomplish this, I came up with this code:

Sub my_vlookup()
Dim I As Integer
Dim A As Integer
For A = 5 To 13
For I = 1 To 9
Worksheets(" & I & ").Range("G15").FormulaR1C1 = "=VLOOKUP(RC[-6],Sheet1!R6C1:R187C13," & A & ",0) "
Range("G15").Select
Selection.AutoFill Destination:=Range("G15:G37"), Type:=xlFillDefault
Range("G16:G36").Select
Selection.ClearContents
Next I
Next A


End Sub



However, when I try to run this code, I get a “subscript out of range” error. Any suggestions? Also, I also realize this code is not very flexible (columns 5 to 13 is very specific to my needs), any ideas on how to make this code more adaptive?

 

[Jerry Sullivan]

Hi radicaldude2

That specific error is due to trying to use the String value for the sheet index instead of the numeric index.
If I = 9 then your code is trying to reference Worksheets("9") which would only work if that was the sheet name on the Tab.

The outer looping you have isn't what you want since that will add formulas overwriting the same range 9 times and end up with column 13 referenced on all 9 sheets.

Here's a revised version for you to try...

Sub my_vlookup2()
 Dim iSheetIndex As Integer
 Dim iColumnIndex As Integer
 
 iColumnIndex = 0
 For iSheetIndex = 1 To 9
   iColumnIndex = iColumnIndex + 1
   Sheets(iSheetIndex).Range("G16:G37").FormulaR1C1 _
      = "=VLOOKUP(RC[-6],Sheet1!R6C1:R187C13," & iColumnIndex & ",0)"
 Next iSheetIndex
End Sub

As to how to make this more flexible, that really requires you to define what conditions are fixed, which are variable inputs provided through a user interface and how the user's inputs affect the desired result.

 

[radicaldude2]

Your code worked; exactly what I was looking for. Thank you for the code and detailed explanation!