VBA Loop of Vlookup with dynamic colums

Question

For a small project i want to use a vlookup or match in VBA to derive data from another sheet. I found it a difficult task even with the help of google I couldn't find the solution.

I divided the project into 3 phases:

First phase is creating a working Vlookup in VBA (can't get it working)
Search for the range where it needs to be executed (dynamic) (I think I managed)
Loop through all the cells in the table (totally stuck on "For each" statement)

I managed to fetch the dynamic range #Firstcell and #Lastcell

But I'm really stuck at the loop and vlookup. I want to create the vlookup in such way that for each cell the X will be A & rownumber and Y will be Columnletter & "4". The vlookup needs to be executed from Firstcell to Lastcell.

Sub Match_Values()
' Variables

Dim X As Integer
Dim Y As Integer
Dim Firstcell As Integer
Dim Lastcell As Integer

' Range determination
    With ActiveSheet
        Range("A3").Select
        Selection.End(xlToRight).Select
        Firstcell = ActiveCell.Offset(1, 1).Range("A1").Select
    End With

    With ActiveSheet
        Lastcell = ActiveCell.SpecialCells(xlLastCell).Select
    End With

' For each cell create a vlookup with on rowindex en on y the columnindex loop statement


' Vlookup
With WorksheetFunction
    c04 = .VLookup(X, [Pivot!A4:CC99], .Match(Y, [Sheet1!A4:CC4], 0), False)
End With
MsgBox c04

End Sub

Thanks in advance, if i need to provide additional feedback please let me know.

Edit, Thanks for the feedback I uploaded the example file: https://ufile.io/48evu (i'm sorry didn't see how to disclose in stackoverflow)

Picture 1

Picture 2

What is the "vloop" -- is it a typo for "loop" or a typo for "vlookup"? Details matter. — teylyn, Aug 18 '17 at 09:17
Step back. Post a data sample and explain what you want to achieve. You are all set on a certain formula to achieve your goal. Maybe that formula is the wrong approach. Maybe this can be done without VBA. Maybe you are asking an [XY question](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). — teylyn, Aug 18 '17 at 09:20
Just a side note: Your first 2 `With` statements don't work if you don't use them with a leading `.` before `Range` etc. And you should avoid using `.Select`. Also [always use `Long` instead of `Integer`](https://stackoverflow.com/a/26409520/3219613). Also `Firstcell = ActiveCell.Offset(1, 1).Range("A1").Select` won't work. What do you expect `Firstcell` to be? A cell? A row number? A column number? A value? — Pᴇʜ, Aug 18 '17 at 09:36
@teylyn i included the file and printscreens. Peh: I expect the cell to be something like "X5" or something. i can only see that it moves to the correct cell. so i guessed it did the trick. — Dubblej, Aug 18 '17 at 10:00
@Dubblej no it doesn't do the trick. This line does only selects cell A1 but does not save the position or anything like that into the variable `Firstcell`. — Pᴇʜ, Aug 18 '17 at 11:37
After looking at your screenshots this should be possible without VBA and just a [VLOOKUP formula](https://www.google.de/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwjRx-zn2eDVAhXD1RQKHcHWAyEQFggnMAA&url=https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FVLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1&usg=AFQjCNF9nfydre8A57a3gCAe6MPUo4ZoUQ) — Pᴇʜ, Aug 18 '17 at 11:42
@Peh I understand, you could also use a Index(Match;Match) formula, but that is not the point, because it's part of a larger project. And i try to make it automated (so everyone can use it). This means that a manual formula won't do. — Dubblej, Aug 18 '17 at 13:23
To be honest to you, then I recommend you to have a deeper look at basic VBA programming techniques. This will take some time to learn, but this code looks far from what you like to achieve because you seem to lack basic knowledge of VBA syntax. I know this is not what you want to hear, but learning how to avoid using `ActiveSheet`, `Selection` and `.Select` in your entire code would be a good start to get on the road. — Pᴇʜ, Aug 18 '17 at 14:46

score 0 · Answer 1 · answered Aug 21 '17 at 20:01

I found a way that made my script work.

I agree with Peh that I should look more into VBA, but it works. Maybe this could be useful for someone that try's something similar and it makes the question answered.

Thanks for the support!

Private Sub Match_Values()
' Variables
Dim Firstcell As String
Dim Lastcell As String
Dim sht As Worksheet

' Range determination
    With ActiveSheet
        Range("A3").Select
        Selection.End(xlToRight).Select
        ActiveCell.Offset(1, 1).Range("A1").Select
        Firstcell = ActiveCell.Address
    End With

    With ActiveSheet
        ActiveCell.SpecialCells(xlLastCell).Select
        Lastcell = ActiveCell.Address
    End With

Dim rng As Range: Set rng = Application.Range("Overview!" & Firstcell & ":" & Lastcell)
Dim cel As Range
For Each cel In rng.Cells

    cel.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC1,Pivot!R4C1:R100C50,MATCH(Overview!R2C,Pivot!R4C1:R4C50,0),FALSE),""Error"")"
Next cel

End Sub

Greetings!

VBA Loop of Vlookup with dynamic colums

1 Answers1