VBA to write a formula into a range of cells, but the formula needs a ">"

[AusSteelMan]

Hi everyone,

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:

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

However it does not work for this one:

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

" > "

in the middle, but I can't figure out how to adjust the code.

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

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

and it works just fine. I need the > inside "" for it to work.

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:

=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?

Many thanks for any help you can provide.

For reference, here is my 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

 

[JoeMo]

Try putting that > sign between double quotes otherwise VBA does not recognize it as a string. So it would be "">"" ..................

 

[AusSteelMan]

Thanks for your reply JoeMo.

Unfortunately this doesn't work in this case but I don't know why. I tried it again just now but to no avail. the line of code looks like:

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

but just returns TRUE as the result.

Any other ideas or issues you can see here?

 

[AusSteelMan]

OK fixed it.

I needed more " marks like this:

    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 actually want ">" as the string, so I tried putting a double" before and after ">" to read """>""" so that ">" was the result.

So the other line needs to include """<=""" and it works too.

Thanks

 

[Rick Rothstein]

Does this work...

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

 

[AusSteelMan]

Thanks for your reply too Rick.

I realised that I needed the string quotes "" then the actual string ">" and the closing string quotes "" as per the post I squeezed in just before yours.

It seems quite obvious to me now, but had me baffled for a while.

Thanks for your interest and suggestion though. That is what keeps this great board going.

Cheers,
Darren

 

[Rick Rothstein]

Thanks for your reply too Rick.

I realised that I needed the string quotes "" then the actual string ">" and the closing string quotes "" as per the post I squeezed in just before yours.

You did not need to do the physical concatenation of the greater than symbol into the two text string parts the way you did it... you can simply embed it within the overall text with double quotes around it (so that they become single quotes in the output). Take a look at the suggested formula I posted to see what I mean... I think constructing the formula without the physical concatenations (like I did) is easier to visualize. Here is how I created my version... I took the formula you got from the cell, put quotes at the beginning and end, doubled up all internal quotes, and then assigned it to rng.Formula... quick and simple requiring virtually no thinking.

 

[AusSteelMan]

Thanks Rick.

I see that subtle difference and how I made it hard for myself. I guess the upside is I have learnt 2 new methods. 3 really. My way, your way, and the ease of adding quotes at beginning & end, before doubling all those in the middle for future work. Thanks for teaching me how to fish.

Cheers,
Darren

 

[AusSteelMan]

oops. Error post.
I re-ran a column and it was quicker than previously. I attributed it to the alternate method. However, this column did not use an operator that required the alternate method.