0

I am currently working on a macro that needs to copy a INDEX function from a cell, that has varying information and run it. Eg. the function can be either "=INDEKSI(voy!A1:E4;1;0)" or "=INDEKSI(kto!A1:E4;1;0)". But when I run this macro it only uses either one of those two. Here is the code:

    Range("N1").Select
    Selection.Copy
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A3:E3").Select
    Application.CutCopyMode = False
    Selection.FormulaArray = "=INDEX(kto!R[-2]C:R[1]C[4],1,0)"
    Selection.AutoFill Destination:=Range("A3:E14"), Type:=xlFillDefault
    Range("A3:E14").Select
End Sub

Is there any way to fix this line:

Selection.FormulaArray = "=INDEX(kto!R[-2]C:R[1]C[4],1,0)"

Edit:

This macros point is to pull vehicle information from another tab. The INDEX function varies based on what the user picks from the drop down menu, with this: "=IFS(A1="voy";"=INDEKSI(voy!A1:E4;1;0)";A1="kto";"=INDEKSI(kto!A1:E4;1;0)")". This "IFS" function is located in N1. Then it needs to copy only the values form that cell, paste them, select 5 cells (A3-E3), click the formula bar and press CTRL + SHIFT + ENTER.

I'm really beginner with these, and I'm not sure if this can even work this way.

Mehumies
  • 1
  • 2
  • 2
    What logic is applied to determine which formula should be used? As you have it now, the formula is static. If you need to copy the formula from a cell, you should store the `.formula` property of the cell into a `string` instead, and then write that to your cell. – Plutian Jan 16 '20 at 10:47
  • 1
    Welcome to SO. *the function can be either "=INDEKSI(voy!A1:E4;1;0)" or "=INDEKSI(kto!A1:E4;1;0)"* What makes use one or another? Add that condition to yout code and it should work. Right now, because you got `Selection.FormulaArray = "=INDEX(kto!R[-2]C:R[1]C[4],1,0)"` it will be always like that. Also, you may benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Foxfire And Burns And Burns Jan 16 '20 at 10:48
  • 1
    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ᴇʜ Jan 16 '20 at 10:58

1 Answers1

0

You can use the "If" in the vba like so:

If range("A1").value = "voy" then
Selection.FormulaArray = "=INDEX(voy!R[-2]C:R[1]C[4],1,0)"
else
Selection.FormulaArray = "=INDEX(kto!R[-2]C:R[1]C[4],1,0)"
end if

Please also read up on the links shared in the comments by Foxfire and Burns And Burns & Peh as it would save you from a lot of future issues

Xlsx
  • 165
  • 1
  • 12