Formula Copy/Fill-Down Until Last Row

[HansV]

I'll leave it to Alan to respond to that.

[Doc.AElstein]

Hello menajaro,
I never forget Threads, and always reply, ( probably not quickly in the warmer half of the year! , since I am not near a computer with Excel on it very often , ),
I am happy to continue to do so, here, if you are in no rush. I can't reply quickly.
But, my guess is that nothing I can offer will give you any noticeable improvement, if any at all.
I think you have already closest to the best any of us here can offer.

_._____
The offering from my last post would not, I expect be a major improvement.
Helping you to implement it with not having access to your actual formulas may be very difficult. And there is no guarantee that it will work., It may require a lot of work on your side to understand what is going on

But we can make some attempt if you wish. It sounds to me that you do wish to do so. So I will attempt to explain a bit more...
_._________________________

...How to modify these two lines in both code

The last two macros I gave are basically exactly the same macro. One has just been shortened to the minimum number of code lines.
( It is easier in development to use a macro including some extra steps,extra code lines, which we can remove later, if we get the macro doing anything useful for you, so lets concentrate on the full not shortened macro for now. ( Sub EvaluateRangeFormulas() )
If this works, then it is very simple to modify it to the simplified shortened macro, Sub EvaluateRangeFormulasScrollIt() )
_._______________

You are correct, this is the important line

Code: Select all

 Replace(strEval, "G6", "G8:G" & lRow & "", 1, -1, vbBinaryCompare) 

We can , for now, simplify it to

Code: Select all

 Replace(strEval, "G6", "G8:G" & lRow & "") 

G6 is a reference in the formula from H6. It is also in all the formulas in row 6 ( for the test formulas of yours and mine so far). So in my last macros it is being needed to be applied to each formula
If you did not have any G6 inside any formula, then this line would do no harm. It would not error. It would have no effect. So if some formulas had G6 and some did not, you could use the code line on all formulas. That would not be a problem .

For the first of your formulas, you need G8. For your last formula, you need G&The last row. In VBA , you should already know that in your macros that last row will be got at in a code bit like similar to G & lRow

The Evaluate Range technique that I am using sometimes works. Sometimes it doesn’t. Nobody completely understands how it works.
If it does work , then it works similar to how array formulas, ( that CSE stuff ) work:
A formula that works in one cell can be made to work across a range of cells. If , in a particular formula we can get CSE or Evaluate range techniques to work, , then instead of a single value being returned, a range of values is returned.
In your example, it was doing this sort of thing , all at the same time ( say you do up to row 982 )
=IF(G8="eileenslounge";1000;IF(G8="eileenslounge1";1000;IF(G8="eileenslounge2";1000;"")))
=IF(G9="eileenslounge";1000;IF(G9="eileenslounge1";1000;IF(G9="eileenslounge2";1000;"")))
=IF(G10="eileenslounge";1000;IF(G10="eileenslounge1";1000;IF(G10="eileenslounge2";1000;"")))
_....etc….
_....
=IF(G982="eileenslounge";1000;IF(G982="eileenslounge1";1000;IF(G982="eileenslounge2";1000;"")))


One of the things that we need to do to make this happen is that, we need to give the formula in this form:
=IF(G8:G982="eileenslounge";1000;IF(G8:G982="eileenslounge1";1000;IF(G8:G982="eileenslounge2";1000;"")))

That is what all that Replace( ) stuff is about. It basically changes G6 to G8:G982 in the formula from row 6
_._______

To help explain further, let me give a formula test data range , similar to what you gave, but a bit different so it that better demonstrates the technique that I am trying to explain
In the uploaded file, Converting formulas to valuesC.xlsm , and also Here I have just two formulas in row 6 in columns H and J
Column H =IF(G6="eileenslounge",1000,F7*E8)
Column J =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))

For my next code offering ( given in the next post ) , we don’t need yet to check the time it takes. We are just trying to see if it works, and if you can understand enough to apply to your formulas. Once / if its working we can make a shortened “ScrollIt” simplified version.

The main point that I am trying to demonstrate is that it may be possible to give you a solution that is dynamic / flexible to apply to a row full of any formulas. I stress may….
As I have previously tried to point out, nobody really understand fully these Evaluate Range techniques, and they do not work in all situations. ( I told you already that my technique is not working in my Excel ( 2002-2010 versions ) for your original test formulas. ( I am still curious as to what Excel version you or anybody has and what results they get for running my last two macros with your original formulas. ) ).

In my workbook , Converting formulas to valuesC.xlsm , you will also see that I have CSE array formulas in column L and N which are working in a similar way to the two formulas as used in my Evaluate range macro. That helps you to see what formula I am finally using, as does the Debug.Print code lines

[Doc.AElstein]

( make sure you read the last post before reading this one )

Run this macro on the uploaded file. It should give you the results like Here

Code: Select all

 Sub EvaluateRangeFormulasC() '     https://eileenslounge.com/viewtopic.php?p=268537#p268537
Dim Ws As Worksheet, Rng As Range, Clm As Range, lRow As Long
Const fRow As Long = 6: Const sRow As Long = 8
 Set Ws = ThisWorkbook.Worksheets("data")
' Let lRow = Ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 Let lRow = Ws.Range("G" & Ws.Rows.Count & "").End(xlUp).Row    '   '   http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
  On Error Resume Next
 Set Rng = Ws.Rows(fRow).SpecialCells(xlCellTypeFormulas)
 On Error GoTo 0
    If Rng Is Nothing Then MsgBox "No formulas!": Exit Sub

  Let Application.ScreenUpdating = False
    For Each Clm In Rng
    Dim strEval As String '                                                                     '   Formula in column H                                    Formula in column J
     Let strEval = Clm.Formula: Debug.Print strEval                                             '  =IF(G6="eileenslounge",1000,F7*E8)                     =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
     ' modifications to make first formula work in  CSE / Range Evaluate  sort of a way
     Let strEval = Replace(strEval, "G6", "G8:G" & lRow & ""): Debug.Print strEval              '  =IF(G8:G15="eileenslounge",1000,F7*E8)                 =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
     Let strEval = Replace(strEval, "F7*E8", "F9:F16*E10:E17" & lRow & ""): Debug.Print strEval '  =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715)      =IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
     Debug.Print ' just to make an emty line in the Immediate window
     ' modifications required for second  formula work in  CSE / Range Evaluate  sort of a way
     Let strEval = Replace(strEval, "E7", "E8:E15" & lRow & ""): Debug.Print strEval            '  =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715)      =IF(E8:E1515="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
     Let strEval = Replace(strEval, "F8", "F8:F15" & lRow & ""): Debug.Print strEval            '  =IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715)      =IF(E8:E1515="","Got one or more missing numbers",IF(F8:F1515="","Got missing number in column F",""))
    Let Clm.Offset(sRow - fRow).Resize(lRow - sRow + 1).Value = Evaluate(strEval)
    Debug.Print ' just to make an emty line in the Immediate window
    Next Clm
 
 Let Application.ScreenUpdating = True
End Sub 

When in the VB Editor, after running the macro, you can hit keys Ctrl+g to see the following in the Immediate window. It shows the build up of the formulas in a full run

Code: Select all

 =IF(G6="eileenslounge",1000,F7*E8)
=IF(G8:G15="eileenslounge",1000,F7*E8)
=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715)

=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715)
=IF(G8:G15="eileenslounge",1000,F9:F16*E10:E1715)

=IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
=IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
=IF(E7="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))

=IF(E8:E1515="","Got one or more missing numbers",IF(F8="","Got missing number in column F",""))
=IF(E8:E1515="","Got one or more missing numbers",IF(F8:F1515="","Got missing number in column F",""))



 

When the formula is not changed at the next line it is because the Replace( ) thing was doing something needed on the other formula in the next or previous loop, so it had no effect on the current formula
_.______________________________________
_._________________________________________

I think you have already closest to the best any of us here can offer.
I think no one would object if you try your luck at other Forums now, if you wish.
( Remember to tell us and them of all other Forums where you posted the same question, ( and don’t bother with excelfox.com as I am the only one answering any questions there currently ) )

I will always reply further to you here, if you wish , no problem, but I think we are unlikely to finally get any significant improvement in performance,
( and just now I can’t reply too fast – I started this reply over a day ago, but only just got around to finish it…)

I can possibly post a more suitable demo if you can at least give me some hint of a typical formula.

As always I will post back if in the meantime, if I come up with any other ideas. But I am out of ideas on this one at the moment. So don’t rely on it

Alan

[menajaro]

Hello Mr. Alan
It's my great pleasure to know a person like you and Once more thank you for all your help
Generally using Evaluate Ranges is more elegant. Because I discovered the power of Evaluate.
For that I think I need more time to understand this fascinating techniques Because it's very fast.
Till we meet again in another impossible mission, I have to say goodbye. Have a nice day.
Best regards for all of you