Question about using Evaluate with an array of formulas not an array formula

**Mr_Bill** · 09-19-2014, 12:53 PM

I have several lines of code that currently input a formula into several different cells. Basically these cells echo data from one sheet to the dashboard. I would like to only have the values appear if possible. Can this array of formulas be written using Evaluate?

        With .Range("C5:H5")
            .Value = Array("='" & termBox.Value & "'!P2", "='" & termBox.Value & "'!Q2", "='" & termBox.Value & "'!Q4", "='" & termBox.Value & "'!P3", "='" & termBox.Value & "'!Q3", "='" & termBox.Value & "'!P4")
            .HorizontalAlignment = xlCenter
        End With

**AlphaFrog** · 09-19-2014, 01:31 PM

This converts the formulas to values

        With .Range("C5:H5")
            .Value = Array("='" & termBox.Value & "'!P2", "='" & termBox.Value & "'!Q2", "='" & termBox.Value & "'!Q4", "='" & termBox.Value & "'!P3", "='" & termBox.Value & "'!Q3", "='" & termBox.Value & "'!P4")
            .Value = .Value
            .HorizontalAlignment = xlCenter
        End With

**Mr_Bill** · 09-19-2014, 01:58 PM

Thank you it did work but not how I planned need to rethink this one. Does this only return the value of the cell in the array once? Any thought how I could get the cells to auto update as if they were still ='Random Name'!P2 or would I need to have line like Range("D2").Value = Range("'Random Name'!P2").Value for every cell I want to do this to? These codes are automatically input in a newly generated section of the dashboard when a new sheet is generated so I am thinking I am stuck with them as is.

**AlphaFrog** · 09-19-2014, 02:16 PM

A formula is probably the best way for the cells to auto-update.

What's the problem with using a formula if it works? I don't follow what the issue is.

**Mr_Bill** · 09-19-2014, 03:26 PM

Part an OCD thing, part my mind tells me the page would work faster with less on it, and part pain to copy and paste from that page as the formulas get copied.

One more if you are up it can a sumif be done in the evaluate? I tried but it doesnt do anything and gives no error

Range("E6").Value = Evaluate("=SUMIF($D$6:D8000,"">=0"", $E$6:E8000)")

What it should do is if one of the cells in D is blank or has a - it wont add the adjacent cell in E to the total

Question about using Evaluate with an array of formulas not an array formula

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Question about using Evaluate with an array of formulas not an array formula

Re: Question about using Evaluate with an array of formulas not an array formula

Re: Question about using Evaluate with an array of formulas not an array formula

Re: Question about using Evaluate with an array of formulas not an array formula

Re: Question about using Evaluate with an array of formulas not an array formula

Thread Information

Users Browsing this Thread

Similar Threads

Evaluate an Array Formula

[SOLVED] Evaluate Named Range Array formula to VBA Array

[SOLVED] Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

[SOLVED] Using Evaluate With Array Formula

[SOLVED] Quick Array question - Copy array to another array then resize?

Bookmarks

Bookmarks

Posting Permissions