Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

[DocAElstein]

This is post 23187
https://www.excelfox.com/forum/showt...ll=1#post23187
https://www.excelfox.com/forum/showt...ge59#post23187

---

The new solution
Having done all the donkey work, and now, knowing probably better than anyone what it is all about we can go straight into some solutions.

 Sub SimplerSolutionDevelopment()    '     https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=23187&viewfull=1#post23187
Rem 0 test data range
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim Rng As Range
 Set Rng = Worksheets.Item("Sheet1").Range("A1:F7")
Rem 1
Rng.Offset(0, Rng.Columns.Count + 1).Clear

'1a These do nothing, and we know why now   https://www.excelfox.com/forum/showthread.php/2918-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly?p=23192&viewfull=1#post23192
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Range("A1:F7")
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("A1:F7")
'1b(i) a trick will do it
' Let Rng.Offset(0, Rng.Columns.Count + 1) = Range("IF({1},A1:F7)") ' Run time error  '1004'
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("IF({1},A1:F7)")
 'Watch : - : Evaluate("IF({1},A1:F7)") :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
'                              
'1b(ii)
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")
' Watch : + : Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))") :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
'                              

'1b(iii)
 Rng.Offset(0, Rng.Columns.Count + 1).Clear
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("IF(A1:F7="""","""",A1:F7)")
' Watch : + : Evaluate("IF(A1:F7="""","""",A1:F7)") :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
'                              


'1c)
 Rng.Offset(0, Rng.Columns.Count + 1).Clear
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Range("A1:F7").Value(RangeValueDataType:=xlRangeValueDefault)
 ' Watch : + : Range("A1:F7").Value :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
 '                             
 Rng.Offset(0, Rng.Columns.Count + 1).Clear
 Let Rng.Offset(0, Rng.Columns.Count + 1) = Evaluate("A1:F7").Value(RangeValueDataType:=xlRangeValueDefault)
 ' Watch : + : Evaluate("A1:F7").Value :  : Variant/Variant(1 to 7, 1 to 6) : Sheet1.SimplerSolutionDevelopment
 '                             
End Sub

Conclusions / A final solution(s)

In the coding above, we do a very simple trick in '1b(i) to get the array of values passed to the LHS, this sort of thing
Range2 = Evaluate("IF({1},A1:F7)")

.
.
.
.
..
.
.
.
.
..
.
.
.
.
..
.
.
.

That is OK, it almost gets what we want, but we see that we have the problem of just an empty cell returning 0 in the tweaked Evaluate(" ") solution, https://i.postimg.cc/4NF4tH97/proble...d-Evaluate.jpg
: Evaluate("IF({1},A1:F7)")(2,6) : 0 : Variant/Double : Sheet1.SimplerSolutionDevelopment
: Evaluate("IF({1},A1:F7)")(3,6) : 0 : Variant/Double : Sheet1.SimplerSolutionDevelopment

We had this problem in the original solution as well, https://eileenslounge.com/viewtopic....309137#p309137 ,….. If you have any cells in the selection that are empty, then you had better use this next version instead, or else it will change empty cells into a cell with a 0 in it …..
This is perhaps understandable because we are doing mathematical calculations in VBA in Variants, and I am taking a guess that VBA calculations default to 0. Or some similar explanation.
As in the original solution, we can remove that problem by a modification, as in '1b(ii) , so we have like
Range2 = Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")
In fact, as we often find, the extra bit does the trick, so we can remove the IF({1},____) and so simplify the solution to like as in '1b(iii)
Range2 = Evaluate("IF(A1:F7="""","""",A1:F7)")
( One small thing to note with '1b(ii) and '1b(iii) is that the Empty is replace by / shown as "" , a zero length String ""
: Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")(2,6) : "" : Variant/String : Sheet1.SimplerSolutionDevelopment
: Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")(3,6) : "" : Variant/String : Sheet1.SimplerSolutionDevelopment
: Evaluate("IF(A1:F7="""","""",A1:F7)")(2,6) : "" : Variant/String : Sheet1.SimplerSolutionDevelopment
: Evaluate("IF(A1:F7="""","""",A1:F7)")(3,6) : "" : Variant/String : Sheet1.SimplerSolutionDevelopment )

 Evaluate("IF(A1:F7="""","""",IF({1},A1:F7))")                   Evaluate("IF(A1:F7="""","""",A1:F7)")
 
.
.
..
.
.
.
.
.
.
..
.
.
.
.
.
.
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=jdPeMPT98QU
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg.9fxrOrrvTln9g9wr8mv2CS
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX4t
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g7lhoX-ar5
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl 
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg.9g9wJCunNRa9gJGhDZ4RI2
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

_.______________________

Well, we got as far as we did before with the original solution, but done it correctly this time.
But we can do another offering:
See next post