Appendix Thread. 3 TEST COPY

**DocAElstein** · 05-13-2020, 01:15 PM

Macro accomnpanying last post

Code:

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

Running the above macro on the test data in uploade file will give these results:

_____ Workbook: Converting formulas to valuesC.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	E	F	G	H	I	J	L	N
5							CSE equivalent	CSE equivalent
6				#VALUE!		Got missing number in column F
7	Title 5	Title 6	Title 7	Title 8	Title 9	Title 10
8			eileenslounge	1000.00		Got one or more missing numbers	1000	Got one or more missing numbers
9		1	eileenslounge1	4.00		Got one or more missing numbers	4	Got one or more missing numbers
10	1	2	eileenslounge2	9.00			9
11	2	3	Others	16.00			16
12	3	4	eileenslounge	1000.00			1000
13	4	5	eileenslounge1	36.00			36
14	5	6	eileenslounge2	49.00			49
15	6	7	Others	64.00			64
16	7	8
17	8
18

Worksheet: data

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:

=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",""))