# [VBA] Evaluating formulas in a Range expression?



## MEUserII (Dec 27, 2022)

I am trying to evaluate formulas in a range expression in VBA in Excel.

For example, consider the following sample VBA codes:

```
Sub Macro1()
Columns(("$AA") & (":") & (Application.Evaluate("SUBSTITUTE((ADDRESS( (1), ((SUM(COLUMN(INDIRECT((""AA"")&(1) ) ) ) )+((2)-(1) ) ), (4), (1) ) ), (""1""), ("""") )"))).Select
End Sub
```

OR


```
Sub Macro1()
Columns(("$AA") & (":") & (Range("=SUBSTITUTE((ADDRESS((1), ((COLUMN(INDIRECT((""AA"")&(1) ) ) )+((2)-(1) ) ), (4), (1) ) ), (""1""), ("""") )").Value)).Select
End Sub
```


----------



## MEUserII (Dec 27, 2022)

I forgot to add that the issue listed above is the code samples I've provided do not work/error in trying to return the evaluation version the formula embedded in the VBA Range() expression which should simplify to the following VBA code:

```
Sub Macro1()
Columns(("$AA") & (":") & ("AB") ).Select
End Sub
```

Any ideas on correcting my above code in my first post to get it to work?


----------



## MEUserII (Dec 28, 2022)

Hmm, I am still stuck on this; are there any suggestions?


----------



## MEUserII (Dec 28, 2022)

I am still stuck on this. For reference, I've tried looking at the following Excel VBA resources via Google searching on the VBA commands: 'Application.Evaluate()', Application.Evaluate().Value', 'Evaluate()', and [] (literally just brackets); with no solution in sight. Any help would be appreciated as so far no one seems to have an idea on troubleshooting this question...?

Links:
1) Excel Cells & Ranges - Evaluate Method
2) Application.Evaluate method (Excel)
3) VBA Evaluate Function - Automate Excel
4) Excel VBA Evaluate Function | Computergaga


----------

