0

I'm working for my internship and have to make a macro in Excel VBA.

Theres one column with vlookups generated by the macro and the column next to it is what it uses to lookup what it needs from another sheet.

But the thing is, it repeats itself with the same vlookup over and over and I don't know how or what I can do against it to fix it. So I hoped I could get help from here.

I'm not experienced at all and am basically teaching myself what I know at the moment from VBA Excel.

The code:

Range("H22").Select
Sheets("EplSheet").Select
Range("D3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VERT.ZOEKEN(C3;Hulpblad!F:I;4;0)"
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
Range("D3").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=477
Range("D3:D500").Select
ActiveSheet.Paste
Range("E4").Select
Application.CutCopyMode = False

I know it will most likely be done with a simple loop, like for each time I paste this I add 1 up to the C3 so it goes to C4, C5, C6, etc. but I dont know and can't find out how for 30 min already and don't want to be stuck on this part.

enter image description here

As you can see in this screenshot it repeats itself while the A3 should be going up at the same rate as the left column so every row down it should go 1 up.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Dylano_Student
  • 1
  • 3
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Nov 18 '20 at 07:01
  • Somehow a desciption of what your actual goal is, is missing. You can eg. post a screenshot of your data to explain what you want to do with your data, as this part is pretty unclear, since the code does not do what. See [mcve] and try to provide example input and desired output data. – Pᴇʜ Nov 18 '20 at 07:08
  • 1
    Also note that `.FormulaR1C1` only accepts the english formulas with the `,` as separator. If you want to use a localized formula like `=VERT.ZOEKEN(C3;Hulpblad!F:I;4;0)` you need to use `FormulaR1C1Local` instead. – Pᴇʜ Nov 18 '20 at 07:11
  • 1
    I think that because you are using `FormulaR1C1` notation, it should be something like `ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Hulpblad!C4:C9,4,FALSE)"` – Foxfire And Burns And Burns Nov 18 '20 at 08:18

0 Answers0