dynamic reference in VBA Index function

Question

I would like to dynamically apply below INDEX function for two ranges where one range should be dependent on an iterator i.

Could anyone help how to write that down instead of my code example (only the Index part with the reference, please)?

With ActiveSheet
For i = 1 to 5
    .Range("c" & i & ":I" & i & "") = [INDEX(EURbased!C5:I5 * EURbased!C4:I4,0)]
Next
End With

I would like to use iterator i instead of the "5" in EURbased!C5:I5

@iDevlop, could you pls show a short example how in this case? — Al_Iskander
– Al_Iskander, Commented Feb 6, 2017 at 10:02
I can't give you an example because I don't understand what exactly you want to do. Looks like you want to copy 15 cells into A3:C3 — Patrick Honorez
– Patrick Honorez, Commented Feb 6, 2017 at 10:12
@Al_Iskander - can you put a screenshot of what you would like to achieve? — Vityata
– Vityata, Commented Feb 6, 2017 at 10:21
[] is short for Evaluate("") msdn.microsoft.com/en-us/library/office/ff193019.aspx — Slai
– Slai, Commented Feb 6, 2017 at 12:07

Slai · Accepted Answer · 2017-02-06 11:27:01Z

1

Try it like this:

With ActiveSheet
For i = 1 to 5
    .Range("c" & i & ":I" & i & "").formula = "=INDEX(EURbased!C" & i & ":I" & i & " * EURbased!C4:I4,0)"
Next
End With

I have not tried it myself, but as far as you mentioned I would like to use iterator i instead of the "5" in EURbased!C5:I5 I think it should work.

edited Feb 6, 2017 at 11:27

Slai

23k5 gold badges49 silver badges55 bronze badges

answered Feb 6, 2017 at 11:13

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Slai Over a year ago

typing comment on a phone is a bit frustrating for me so I hoped you wouldn't mind :]

Vityata Over a year ago

actually I am impressed by the people who can put value into SO through a phone! :) +50 :)

Collectives™ on Stack Overflow

dynamic reference in VBA Index function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related