#### AusSteelMan

##### Board Regular

- Joined
- Sep 4, 2009

- Messages
- 208

Thanks for reading my question.

Here is some background:

I have some code that uses a formula to write a formula then its result into a selected range of cells that are in a table.

It works fine for a formula such as:

Code:

`rng.Formula = "=COUNTIF([Material Number],[@[Material Number]])"`

However it does not work for this one:

Code:

`rng.Formula = "=COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value]," & " > " & "&[@[Current Stock Value]])+COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value],[@[Current Stock Value]])"`

I know (well, I'm pretty sure) that it is due to the

Code:

`" > "`

If I was to enter directly into the table (not using VBA) the formula is:

Code:

`=COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value],">"&[@[Current Stock Value]])+COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value],[@[Current Stock Value]])`

My issue with this however is that my table is A1:BO64230. So filling 1 column with this formula and it calculating can take many hours. It is worse when the desired formula calls on a Sub, such as:

Code:

`=concatif([Material Number],[@[Material Number]],[No_at_Loc],CHAR(10))`

By using the rng.formula code, I can fill a column with results, not formulae, in 8 minutes (I know it is still a long time). I do not need to recalculate these answers after initial calculation.

**So my question is really:**

How do I write code such that I can populate cells with a formula that has an operator (such a > (greater than) or <=(less than or equal too)) as part of the formula, especially when it needs to be inside "" marks?

How do I write code such that I can populate cells with a formula that has an operator (such a > (greater than) or <=(less than or equal too)) as part of the formula, especially when it needs to be inside "" marks?

Many thanks for any help you can provide.

For reference, here is my code:

Code:

```
Sub LoopThroughRange_FormulaResult()
Dim rng As Range
Dim cell As Range
Dim StartTime As Double
Dim EndTime As Double
Dim EndTimeMinutes As Double
Dim SecondsPerCell As Double
Dim NumberOfCellsInRange As Double
Dim MyFormula As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
StartTime = Timer
Set rng = Selection ''I am selecting just 3 of the 64320 cells in the desired column so I can see it progressing but doesn't take to long
NumberOfCellsInRange = rng.Cells.Count
' rng.Formula = "=concatif([Material Number],[@[Material Number]],[No_at_Loc],CHAR(10))"
' rng.Formula = "=COUNTIF([Material Number],[@[Material Number]])" ' this took just over 8 minutes to populate 64,319 cells
' rng.Formula = "=[@[Cumulative stock value]]/SUM([Current Stock Value])" ' this took about 4.8 minutes to populate 64,319 cells
'' rng.Formula = "=COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value]," & " > " & "&[@[Current Stock Value]])+COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value],[@[Current Stock Value]])" ' returns TRUE instead of a number
'' rng.Formula = "=SUMIF([Overall Current Stock Rank]," <= "&[@[Overall Current Stock Rank]],[@[Current Stock Value]])" ' returns FALSE instead of a number
rng.Value = rng.Value
''' With all the rng.Formula lines above commented out, the following is an attempt to write an actual formula to a cell
For Each cell In rng
'' cell = "=COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value]," > "&[@[Current Stock Value]])+COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value],[@[Current Stock Value]])" '' This returns result of "TRUE" when it should be a string of text
'' cell.Value = "=COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value]," > "&[@[Current Stock Value]])+COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value],[@[Current Stock Value]])" '' This returns result of "TRUE" when it should be a string of text
'' cell.Formula = "=COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value]," > "&[@[Current Stock Value]])+COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value],[@[Current Stock Value]])" '' This returns result of "TRUE" when it should be a string of text
''' or build a formula a bit at a time
' MyFormula = "=COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value],"
' MsgBox (MyFormula)
' MyFormula = MyFormula & " > "
' MsgBox (MyFormula)
' MyFormula = MyFormula & "&[@[Current Stock Value]])+COUNTIFS([Asset Manager],[@[Asset Manager]],[Current Stock Value],[@[Current Stock Value]])"
' MsgBox (MyFormula) ''this does not return the correct formula since the "" are missing from around the > (greater than sign)
'MsgBox (" > ") '' returns > with no "" around it
'MsgBox ("" > "") '' returns FALSE
' cell = MyFormula
Next cell
EndTime = Timer - StartTime
EndTimeMinutes = EndTime / 60
SecondsPerCell = EndTime / NumberOfCellsInRange
MsgBox ("Calculation of " & NumberOfCellsInRange & " rows" & vbNewLine & "took: " & EndTime & " seconds" & vbNewLine & EndTimeMinutes & " minutes" & vbNewLine & "Execution Rate: " & SecondsPerCell & " seconds per cell")
' Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
```

Notes:

I comment out the lines I am not using. Then only have the live lines for the particular column I am using.

When I was having problems with the last 2 rng.Formula lines, I tried a ForEach/Next loop with other ways of trying to build the desired formula. Even tried just outputting to a Msgbox to see if I could get it to work.

I have a timer so I could differentiate between methods, and kept it since I have a bit of data junkie in me.

Best regards,

Darren