Home / Questions / 65853
Accepted
Revolucion for Monica
Asked: 2020-04-28 01:20:46 +0800 CST 2020-04-28 01:20:46 +0800 CST

How to write in excel cells references instead of values ​​with a macro?

  • 772

I want to use the cells reference (as a pointer in C++) and not their absolute values ​​in VBA in cells that vary based on one another's value.

I have done the following with Cells(x,y).Valuebut it gives me the absolute value when I want the equivalent of =$A$1$eg.

Cells(8 + i, 6 + nbrVille).Value = Worksheets("Sheet1").Cells(2, 2).Value * Worksheets("Sheet1").Cells(7, 4 + j_Distances).Value

i = 0
For i_Distancia = 1 To nbrSitio
    For j_Distancia = 1 To nbrCiudad
        Cells(8 + i, 7 + nbrCiudad).Value = ">="
        Cells(8 + i, 8 + nbrCiudad).Value = Sheets("Distancia").Cells(i_Distancia, j_Distancia ).Value - 2
        Cells(8 + i, 6 + nbrCiudad).Value = Worksheets("Sheet1").Cells(2, 2).Value * Worksheets("Sheet1").Cells(7, 4 + j_Distancia).Value
        i = i + 1

    Next j_Distancia
Next i_Distancia

That is necessary for doing dynamic constraints for a solver.

At the moment the macro gives me:

enter image description here

But I want :

enter image description here

excel

1 Answers

  1. Best Answer

    Replace this

    Cells(8 + i, 6 + nbrCiudad).Value = Worksheets("Sheet1").Cells(2, 2).Value
                                  * Worksheets("Sheet1").Cells(7, 4 + j_Distancia).Value

    For this

    Cells(8 + i, 6 + nbrCiudad).Formula = "=" & Worksheets("Sheet1").Cells(2, 2).Address &
                                      "*" & Worksheets("Sheet1").Cells(7, 4 + j_Distancia).Address
    • 7