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

[DocAElstein]

This is post https://www.excelfox.com/forum/showt...ge61#post24134
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page61#post24134
https://www.excelfox.com/forum/showt...ll=1#post24134
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24134&viewfull=1#post24134

---

Some notes in support of this main forum post
https://eileenslounge.com/viewtopic....316419#p316419

Good Lord, WTF is this… a punishment, I think

 Sub M_snb() '     https://eileenslounge.com/viewtopic.php?p=316419#p316419
Dim c00 As String
  c00 = "Mon, 04 Mrz 2024 08:48:08 GMT"
  MsgBox CDate(Replace(Mid(c00, 6, 20), Mid(c00, 8, 5), "-" & Application.Match(Mid(c00, 9, 3), [transpose(text(30*row(1:12),"[$-407]mmm"))], 0) & "-"))
End Sub

Let’s first break it down / open it up a little bit

 CDate(Replace(Mid(c00, 6, 20), Mid(c00, 8, 5), "-" & Application.Match(Mid(c00, 9, 3), [transpose(text(30*row(1:12),"[$-407]mmm"))], 0) & "-"))
CDate(

Replace(Mid(c00, 6, 20), Mid(c00, 8, 5), "-" & Application.Match(Mid(c00, 9, 3), [transpose(text(30*row(1:12),"[$-407]mmm"))], 0) & "-")
)

Replace(
Mid(c00, 6, 20), Mid(c00, 8, 5), "-" & Application.Match(Mid(c00, 9, 3), [transpose(text(30*row(1:12),"[$-407]mmm"))], 0) & "-"
Expression     , find          , replace                                                                                                                , start,  count,  compare
)

Replace(expression, find, replace, start, count, compare ) https://learn.microsoft.com/en-us/of...place-function

So let’s look at this first Application.Match(Mid(c00, 9, 3), [transpose(text(30*row(1:12),"[$-407]mmm"))], 0)


The array, [transpose(text(30*row(1:12),"[$-407]mmm"))]
The final thing it ends up with is a 1 D array , ( as we need for the .Match ) , which looks like
{"Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"}
The main thing there is the Excel TEXT function, which tries to return a text out of …._
_.... = TEXT( a number , the second argument is some recognised format )
For now I will have to believe that [$-407] means German dates and the mmm is likely what it always is in these date things: The month in 3 letters. The numbers generated are { 30, 60, 90 …. 360 }, so that is a nice way to get a day number for each month, so like we finally have, as example the second array element gives pseudo like
TEXT(60, [$-407]) = "Feb"

I might do the array a bit differently…

 Let vTemp = Evaluate("30*row(1:12)") '  {30; 60; 90; 120; 150; 180; 210; 240; 270; 300; 330; "360"}
 
 Let vTemp = Evaluate("text(30*row(1:12),""[$-407]mmm"")")  ' "Jan"
 Let vTemp = [text(30*row(1:12),"[$-407]mmm")]              ' "Jan"
 Let vTemp = Evaluate("if({1},text(30*row(1:12),""[$-407]mmm""))")  ' {"Jan"; "Feb"; "Mrz"; "Apr"; "Mai"; "Jun"; "Jul"; "Aug"; "Sep"; "Okt"; "Nov"; "Dez"}
 Let vTemp = [IF({1},text(30*row(1:12),"[$-407]mmm"))]              ' {"Jan"; "Feb"; "Mrz"; "Apr"; "Mai"; "Jun"; "Jul"; "Aug"; "Sep"; "Okt"; "Nov"; "Dez"}
 Let vTemp = [TEXT(30*COLUMN(A:L),"[$-407]mmm")]  ' "Jan"
 Let vTemp = Evaluate("TEXT(30*COLUMN(A:L),""[$-407]mmm"")")  ' "Jan"
 Let vTemp = [IF({1},TEXT(30*COLUMN(A:L),"[$-407]mmm"))]  ' {"Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"}
 Let vTemp = Evaluate("IF({1},TEXT(30*COLUMN(A:L),""[$-407]mmm""))")  ' {"Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"}
' So what we got is a 1 D array of the months as Excel has them for Germany
 

Evaluate("IF({1},TEXT(30*COLUMN(A:L),""[$-407]mmm""))")