Testing Posts, Internet, Forum Software. German Telekom

[DocAElstein]

Test test

German Telekom around July 2019

.
Making a WLAN connection.
Stupidly we never had tried this in the 10 years we have had the router. Instead we had lots of LAN cables, as well as an extra purchased Hub switch to give us more ports in addition to the original 4
With WLAN, we need no cable and seem to be able to have infinite number of computers connected at the same time over the WLAN !!
Often all the 5 green lights on the front of the Router ( German Telekom Speedport W504V Typ A ) are on except the one for WLAN. This needs to be on.
On the back is a switch, ( which does not look like a switch. It looks like a set of simple slits such as normal airing slits. ) Pressing this should make the green WLAN light come on, which indicates that WLAN is activated.

Also, on the back of the router is some info which include unique identifying numbers
Geräte passwort ( not needed ) 83860973
WLAN-Name: (SSID) WLAN-C1EA31
WLAN-Schlüssel: (WPA/WPA2) 3716565511074969
371 656 551 107 4969
3716 5655 1107 4969

It appears that the "WLAN-C1EA31" is what comes up in any list of available connections shown by a computer . The number or password or secret code usually required appears to be that key number, in our case, 3716565511074969. So 3716565511074969 is likely to be needed at some point to be typed in.

Vista
These are one possible set of steps to get the WLAN working on a computer with Vista Operating System.
MicrosoftSymbol VerbindungHerstellen.jpg : https://imgur.com/CLLZPAj
VerbindungMitDrahtlos.jpg : https://imgur.com/VpcPx9Y
Verbindung herstellen.jpg : https://imgur.com/I7sc5Us , https://imgur.com/vbvZI0U

From here you can decide whether to
use a configuration switch on the router if available (EinstellenWLAN-C1EA31Abrufen.JPG : https://imgur.com/ibHOmeH ) ,
or
give the Netzwerkschlüssel/Passphrase. I use here the Schlüssel way:
ChooseSchluesselWay.jpg : https://imgur.com/FMTiOoq
GiveScluessel(GivenOnBackOfRouter).jpg : https://imgur.com/UqykYK3
Verbinden.jpg : https://imgur.com/iQ72gfp
Usually it then connects very quickly
VerbindungMit.WLAN-C1EA31Wirdhergestellt.jpg : https://imgur.com/beP65X6
VerbindungErfolgreich.jpg : https://imgur.com/OX2Jyqx
ThereMayBeAProblem.jpg https://imgur.com/v8RdyA5


Quirky WLAN not working?… - "Repair"… Reset?
A few times, for no reason yet clear to me, WLAN has not worked on a computer. In most cases an apparent failed attempt to "repair" has then resulted in WLAN working.
In one case an unusual pop up told me of an unusual problem and gave me various ways to do a repair. In another case I followed a route something along these lines
RightClickOptionsBottomLeftForWLAN.jpg https://imgur.com/WnAlHkO
or
Repair WLAN.JPG : https://imgur.com/izeoLfp
or
Repair WLAN.JPG : https://imgur.com/8W8Ii5B
Etc…..
At the end I was told it was not successful. But after this WLAN suddenly started working normally.
My best guess is that something is "reset" as part of a "repairing" process. This may not have been originally identified as a source of problem and/ or realised as likely to cure one.





German Telekom Changes 5-6 July 2019
German Telekom introduced some changes… See sketch or 1-6 Steps below. ( Step 1 told us that the new system would be enabled on Friday 5th July. On Friday 5th July, approximately at Midday, they told us the new system was turned on. )
20190707_W504V Typ A WLAN.jpg : https://imgur.com/ijqKN7A
20190707_ W504V Typ A WLAN.jpg : https://imgur.com/GSdjNXD

Your router needs to be "IP capable"…
Speedport W504V Typ A is OK ( https://www.telekom.de/hilfe/geraete...amChecked=true )
Speedport W504V OK.jpg : https://imgur.com/WvAIfOf

On Friday 5th July in the afternoon the old system was still working.
I unplugged the system.
I changed the cable arrangement as indicated in the steps 2-3
20190707_ W504V Typ A WLAN.jpg : https://imgur.com/GSdjNXD
Steps 2 - 3.JPG : https://imgur.com/ldThPjP , https://imgur.com/FGn9fEC ,
Attachment 2339

I restarted the router. First all green lights flashed. Finally only the Power and WLAN lights remained green. ( previously on restart, all flashed , then power was green and DSL blinked. Then telephone came on. Then DSL came on constantly. So finally all but WLAN were on ). I restarted again. The same occurred except that also now finally WLAN was not on. So at this point only power is shown as green light on.
After some time, DSL and WLAN came on.
Internet appears still to be working!!

In my Google Chrome browser, I typed in the URL address bar
www.telekom.de/einrichten
After a few seconds of
EinrichtenVersuche.JPG: https://imgur.com/h8udCIW ,
I got an error
ErrorAfterEinrichtenVersuche.JPG : https://imgur.com/92u6XYi
On a second attempt, I got
EinrichtenSuccess.JPG : https://imgur.com/Iw24RRe
Einrichten Success.JPG : https://imgur.com/K7P1mJk
(Warum benötige ich keine Zugangsdaten: https://einrichten.telekom-dienste.d.../help/bng-line
Wir haben den Zugang zum Internet für Sie einfacher gemacht:
Früher waren Zugangsdaten nötig, um den Anschluss einzurichten.
Jetzt identifizieren wir Ihren Anschluss sicher über Ihre gebuchte Leitung. Ihr WLAN ist unabhängig davon mit einem Passwort geschützt (Werkseinstellungen des Routers).
Wünschen Sie die automatische Erkennung Ihres Anschlusses nicht, kann diese im Kundencenter (unter Anschluss & Tarif > Internet-Einstellung > EasyLogin) deaktiviert werden. Tragen Sie danach die Zugangsdaten in Ihren Router ein.
Why do I need no access data: https://einrichten.telekom-dienste.d.../help/bng-line
We've made access to the Internet easier for you:
Previously, access data was needed to set up the connection.
Now we can reliably identify your connection via your booked line. Your Wi-Fi is independently password protected (factory settings of the router).
If you do not want automatic detection of your connection, it can be deactivated in the Customer Center (under Connection & Tariff> Internet Settings> EasyLogin). Then enter the access data in your router. )


Einrichten Success Anpassen.JPG : https://imgur.com/qj0nsq7

( https://einrichten.telekom-dienste.de/eafn/#/dashboard
WLAN
WLAN-NAME
WLAN-C1EA31
WLAN-Name oder
WLAN-Schlüssel ändern§
Geräte per WLAN verbinden§
Weitere Einstellungen ändern§
Telefonie
IHRE AKTIVEN RUFNUMMERN
+49-9281-143107
+49-9281-8397776
+49-9281-8397787
Rufnummern einzelnen Telefonen zuordnen§
Weitere Telefone verbinden§
Anrufe umleiten§
SprachBox
SPRACHBOX-STATUS
Aktiv
Nachrichten anhören,
Aus-/ Einschalten und mehr§
Per App steuern§
Alle Infos und Einstellungen )




ref
https://bits-meet-bytes.de/windows-x...n-einschalten/

[DocAElstein]

in support of these posts
https://www.dsl-forum.de/threads/251...line-verbunden
https://telekomhilft.telekom.de/t5/T...s/td-p/5342695

---

Hi
Dieser Beitrag hier, was ich jetzt schreib, soll nur eines der kleines Geheimnisse lösen, aber nur eines der kleineren Rätsel.

Zurück zu meinem allerersten Beitrag. Um mich zu zitieren …… „Wie kann ich unser ausgefallenes Hausfestnetz melden? „ …..und weiter ….. Ich habe … versucht, mehrmals , mein Problem über das Internet zu melden. ….Aber …., ich komme immer wieder auf dieselbe Mauer zurück:
Ich bekomme irgendwann dieses Fenster:
https://i.postimg.cc/fyrK2Dgg/Backsteinmauer-1.jpg
Also habe ich versucht, mit vielen verschiedenen E-Mail-Konten wie folgt vorzugehen:
https://i.postimg.cc/wvFFNh5z/Backsteinmauer-2.jpg
Dann treffe ich auf die letzte Wand, an der ich nicht vorbeikomme:
https://i.postimg.cc/QMQf8LHH/Backsteinmauer-3.jpg


Also die Lösung, um die letzte Mauer zu überwinden schreibe ich in folgenderer : Es ist in zwei Teilen. ( Die Kurzgeschichte ist : Wenn ich über das Internet etwas mit Fehlersuche an meinem Deutsche Telekom Festnetz mache , habe ich viel bessere Erfolgschancen , wenn ich mich in einen EMail - Account einlogge , den die Telekom vor 10 Jahren für mich erstellt hat . Bis heute hatte ich nie bemerkt, dass ich dieses E-Mail-Konto habe!)

Teil 1: Der von der Telekom für Sie erstellte E-Mail-Account
In den Original geliefert papierkram ( bei mir aus 2011 ) ist ua geschrieben sowas …..
Es gibt eine Zugangsnummer (Es sollte auch ein begleitendes zugehörige persönliches Passwort vorhanden sein, das möglicherweise zweimal geschrieben wird))
https://i.postimg.cc/C1fDfCSN/Zugang...d-Kennwort.jpg https://i.postimg.cc/mrnCDpfk/Zugang...d-Kennwort.jpg
Die Telekom hat zuvor einen EMail-Account erstellt und dafür gesorgt, dass Sie sich mit der Zugangsnummer in diesen Account einloggen können: So gehen Sie vor:

Notieren Sie sich zuerst diese drei Dinge
_(i) die Zugangsnummer und
_(ii) Das zugehörige Passwort
( Das sind die beiden Dinge, über die ich oben gerade gesprochen habe )
_(iii) Ihre Kundennummer (diese finden Sie normalerweise an mehreren Stellen auf jedem Papierkram, einschließlich monatlicher Rechnungen)


Im Internet gehen Sie zu:
www.t-online.de/email-nutzen
Zur Zeit werden Sie möglicherweise*** hierher geführt:
https://i.postimg.cc/QxLL5jkk/To-Get...Brick-Wall.jpg
Nehmen Sie also das Login wie im letzten Screenshot gezeigt

Wenn Sie, wie ich, ein EMail-Konto bei t-online.de haben, werden Sie möglicherweise bereits aufgefordert, Ihren Namen und Ihr Passwort zu verwenden.
https://i.postimg.cc/sxYT5Csw/To-Get...Brick-Wall.jpg
In diesem Fall müssen Sie auf „Nicht ihr Benutzername?“ klicken.
https://i.postimg.cc/5NjHHqgF/To-Get...Brick-Wall.jpg

*** Falls www.t-online.de/email-nutzen bringt etwas Seltsames hervor, wie es manchmal bei mir der Fall ist, dann probiere es stattdessen aus
https://www.t-online.de/?top#
danach klick auf
"E-Mail-login" - https://i.postimg.cc/pVshX1fH/To-Get...Brick-Wall.jpg
Dann evtl. nochmal müssen Sie auf „Nicht ihr Benutzername?“ klicken.
https://i.postimg.cc/5NjHHqgF/To-Get...Brick-Wall.jpg

Möglicherweise kommen sie hier, bei was immer eine weg: https://i.postimg.cc/d3Vhj8Kp/To-Get...Brick-Wall.jpg
Wo Sie hingeschickt werden und wo Sie etwas finden, ist so unvorhersehbar und zufällig und variabel wie jede der Hilfequellen der Deutschen Telekom, aber bevor Sie weitermachen, müssen Sie versuchen, hierher zu gelangen
https://i.postimg.cc/d3Vhj8Kp/To-Get...Brick-Wall.jpg https://i.postimg.cc/NjnGW3vN/EMail-log-in.jpg

Jetzt wird es etwas kompliziert und es kann leicht schief gehen, also befolgen Sie alle Schritte sorgfältig:

_ Entfernen Sie alle zuvor geschriebenen Details, damit Sie keine Einträge mehr haben. (Es ist möglich, dass in Ihrem Fall, wenn Sie t-online.de Freemail noch nie verwendet haben, zunächst keine Einträge vorhanden sind)
In jedem Fall müssen Sie sich in dieser Situation befinden:
https://i.postimg.cc/NjnGW3vN/EMail-log-in.jpg
Erst wenn Sie in diese Situation geraten, sollten Sie nicht auf "Andere Anmeldeoptionen" klicken.
https://i.postimg.cc/DZgWGxR1/To-Get...Brick-Wall.jpg

Also wählen Sie jetzt Zugangsnummer
https://i.postimg.cc/3rfqCYXb/Zugansnummer-option.jpg

Und so wie angegeben.... Bitte geben Sie hier eine Zugangsnummer ein, mit der wir Sie erkennen können. Sie finden diese in den Einrichtungsunterlagen für Ihren Telekom Festnetz-Anschluss……
https://i.postimg.cc/tJcmXNRH/Zugansnummer-option.jpg
- Mit anderen Worten - Sie sollten die Zugangsnummer angeben, die Sie in den Originalvertragspapieren finden sollten _(i) von oben wie besprochen

Geben sie das Zugangsnummer https://i.postimg.cc/L55bKbpT/Zugansnummer.jpg

Dann brauchen sie das beiliegende persönliche Kennwort /Passwort . https://i.postimg.cc/Pqc4v5sm/Passwort-verwenden.jpg _(ii) von oben wie besprochen



Hoffentlich wird der Account gefunden, den die Telekom für Sie erstellt hat. https://i.postimg.cc/QMpt1fv0/Bestaetigung.jpg
Sie können also weiter gehen. https://i.postimg.cc/ZqknWGsW/Bestae-tigung-Weiter.jpg

Dann musst du deine Kundennummer angeben: _(iii) von oben wie besprochen https://i.postimg.cc/4NDsPz6M/Kundennummer.jpg


Möglicherweise werden Sie dann aufgefordert oder Ihnen angeboten, die Sicherheitsmaßnahmen zu verbessern. Sie können wählen, ob Sie dies jetzt oder später tun möchten: https://i.postimg.cc/FsyG7vH6/Schuetzen.jpg
(Hier habe ich mich dafür entschieden, es später zu tun )


Sie müssen eine neue E-Mail-Adresse Namen angeben https://i.postimg.cc/PrTYVwd6/Customer-EMail-name.jpg
https://i.postimg.cc/jqzyhXYw/Customer-EMail-name.jpg


Sie müssen eine neue Password geben, und wiederholen https://i.postimg.cc/7Yncj00b/Password-geben.jpg

https://i.postimg.cc/ZnF3KTxF/Password-geben.jpg

Evtl. Sie müssen Sicherheit Dinge tun. Sie sollen alles was sie geben - https://i.postimg.cc/kXL9XxDs/Sicherheit-Dinge.jpg
Machen sie, und Notieren Sie sich alle Angaben an einem sicheren Ort, falls Sie sie später brauchen : https://i.postimg.cc/CK3mRxkW/Sicherheit-Dinge.jpg


Sie sollten eine Zusammenfassung aller Details erhalten. https://i.postimg.cc/CK3mRxkW/Sicherheit-Dinge.jpg
Noch einmal, es wäre ratsam, alle Informationen, die Sie geben, zu notieren und alle Informationen, die Sie erhalten, an einem sicheren Ort aufzubewahren, falls Sie sie später benötigen

Möglicherweise werden sie jetzt oder wann immer anders extra Sicherheit Maßnahme gefordert zu machen. Beispielweise war ich gefragt um eine anderer EMail Adresse um eine nummer Code zu schicken. Das Nummer musste dann rein füllen um weiter zumachen. https://i.postimg.cc/zvVB48PG/EMail-erhalten.jpg

Die nächsten Schritte sind optional. Ich würde empfehlen, sie zu nehmen: Um zu erklären, worum es geht. Die deutsche Telekom hat zwei Passwörter für jeden t-online.de Freemail Konto.
_ Das übliche Normale wurde bereits besprochen.
_ Eine zweite ist zu verwenden, wenn Sie programmgesteuert EMails versenden können. Es lohnt sich, ein solches Passwort zu erstellen, es irgendwo sicher aufzuschreiben und sich daran zu erinnern, dass es so etwas gibt. Sonst könnten dir später noch längere und noch langweiligere Geschichten einfallen: Zum Beispiel: https://telekomhilft.telekom.de/t5/D.../1727582#M1968
Sie können also die Wahl treffen, ein zweites Passwort zu erstellen und es dann zu machen
https://i.postimg.cc/JzfkJ642/Asked-...e-Password.jpg
https://i.postimg.cc/q72CGsNM/Programme-Password.jpg
https://i.postimg.cc/sgQWMdyW/Programme-Password.jpg

Wir sind jetzt größtenteils fertig. Sie haben noch einmal die Möglichkeit, alle von Ihnen gemachten Angaben zu speichern : https://i.postimg.cc/6QTBWtXs/Final-Details.jpg



Als ich das alles heute gemacht habe, wurde ich nicht endgültig zum EMail-Konto geführt. Stattessen es ist abgestürzt!!!
Aber dann loggte ich mich von https://www.t-online.de/?top#
mit dem neuen E-Mail-Adresse Namen und Passwort ein , dann zum Email-Center https://i.postimg.cc/zGzJQT2G/Zum-E-mail-center.jpg , und fand die E-Mails der letzten 10 Jahre, von denen ich nie wusste, dass ich sie hatte!!

Damit sind wir mit Teil 1 fertig

_._____________________________________________

Teil 2
Um uns noch einmal an das Problem zu erinnern:
Das Problem war, dass, als Teil des Versuchs, mein Hauptproblem zu lösen, dass ich versuchte, meine Festnetz Telefon- / Internetleitung zu überprüfen, und irgendwann wurde ich aufgefordert, mich einzuloggen.
Aber ich bin gegen die Wand gestoßen, dass keiner meiner (damals) mir bekannten E-Mail-Konten funktionierte

Vielleicht errätst du jetzt die Antwort / Lösung für dieses Problem: Ja!, - Ich muss das EMail-Konto verwenden, von dem ich bis heute nicht wusste, dass ich es hatte - Dieses EMail-Konto wurde in Teil 1 oben besprochen.

Ich habe nicht in allen Situationen umfangreiche Tests durchgeführt, aber bisher habe ich festgestellt, dass wenn ich mich mit diesem neu gefundenen E-Mail-Konto anmelde, bevor ich etwas mache, um entweder ein fehlgeschlagenes Festnetz zu melden oder bevor ich etwas damit zu tun habe teste mein Festnetz via Internet, und benutze es auch wenn ich jemals aufgefordert werde mich einzuloggen, dann scheine ich weiter zu kommen...




_.___________________________________-

Das war’s , eine klein teil das Problem gelost. Damit ist ein kleiner Teil des Rätsels gelöst.
( Ich denke, es könnte Einstein gewesen sein, der so etwas gesagt hat wie - Ich kann die Geheimnisse der zirkulierenden Planeten im Weltraum erklären, aber die Geheimnisse der Navigation durch die Deutsche Telekom im Internet sind nicht gelöst... oder so ähnlich
)


Alan

[DocAElstein]

Testing ( http://www.excelfox.com/forum/showth...ll=1#post11366 )

Notes in support of these excelfox Threads and posts:
http://www.excelfox.com/forum/showth...ista-and-Excel
http://www.excelfox.com/forum/showth...r-CMS-Software

Cloud testing Way

Tests Friday, 7th June 2019.

OK I make Today two tries on one computer : Computer Acer Aspire 4810TZG Vista Operating System
_1 Try one: My computer is connected to the internet using the same router as that to which the Sannce 1080N Receiver is successfully connected . (German Telekom Speedport W504V Router LAN RJ45 Internet connection)
Delete a desktop "Deinstaller CMS" icon

[DocAElstein]

Testing… ( http://www.excelfox.com/forum/showth...ll=1#post11367 )

I am using the same computer in two situations
First:
I am connected to the Internet via a USB "Dongle" connected to the computer, ( I have the dongle from, and have a contract for internet use with it , with German Telekom ) . So my computer could be anywhere, or at least anywhere in Germany.
I double click my CMS desktop icon, then allow the software to run, then log in , then double click on Test
If I get a successful connection , then I get the drop down list of 8 CAMeras
DongleSucess.JPG : https://imgur.com/IKLtKmC
If I right click on the Test , then I see just the one connection option, Extra Stream
OnlyConnectAllVideoExtraStream.JPG : https://imgur.com/1Ied0dT
If I take that option, then I get to see all… https://imgur.com/EJxlwgO , https://imgur.com/xfVkZV6

So Now:
I disconnect and remove my dongle. ( I am at home )
I notice that the cameras go blank and the drop down list vanishes
CamerasVanish.JPG : https://imgur.com/0IEDJaw
Drop Down List Gone.jpg : https://imgur.com/0IEDJaw
If I now connect to my home router ( either with a LAN cable of through the WLAN option on the router ( ) ) , then the cameras come back on…
If I now right click on the Test , then I still have the one option to connect.

However, if I close, then restart the CMS , then I see two options:
Two Connect Options.JPG : https://imgur.com/NHTof17


Main Stream
So it appears that the option (MainStream) is something similar to the "IP way". it somehow connects through the Router, ( my "Internet receiving box thing" at home ). So it is somehow a loca" thing. It is only available if the Receiver and the computer which you are using for the CMS software are connected to the same "Internet receiving box thing". Possibly different "Internet receiving box things" may sometimes work differently, so this may not always work. I have a German Telekom Speedport W504V Typ A box which is connected to my House Telephone line( http://www.excelfox.com/forum/showth...ll=1#post11364 ) .
My Sancce receiver ( SANNCE 8CH 1080N 5-IN-1 DVR Video Überwachungskamera Nachtsicht IP66 Fernzugriff … https://imgur.com/iSFrh8S , https://imgur.com/3mY5l2f , https://www.ebay.de/itm/323782698418 ) is connected to my router via a LAN (RJ45) cable.
I can connect my computers to the router with either a LAN (RJ45) cable or via the WLAN . ( For the WLAN , I must switch it on at the router. The router then appears to have a function which sends out a signal which most of my computers appear capable of receiving. This connection then replaces the fixed line LAN cable connection.)

Extra Stream
This appears to be the true "internet option". It only appears to work**** if you have
_ Set up using the "Cloud way", http://www.excelfox.com/forum/showth...ll=1#post11366
_ Your computer is connected to the internet. This can be either via the same connection used for the receiver or any other anywhere

**** At this point I am not 100% sure yet how the systems work and interact so the following statements I may modify later. …
Assume I set up initially on a first computer using the "IP way" ( Main Stream ) . I will only be able to see my cameras on this computer with the CMS software if I have the computer connected to the same "Internet receiving box thing" / Router. So I can only use the "local way" - I must be at home.
Now I set up using the "Cloud way" on a second computer.
Now I restart my first computer , having it connected to the internet by any way independent from my "Internet receiving box thing" / Router. So, for example I might be away from home, but using some way to connect to the internet.
I now see the two options. But neither option works!. So there maybe some communication that the receiver is available for "on air" use through the internet. But not enough to allow a computer set up using the "IP way" to work the "Cloud way". This last point I will recheck more as I add the camera software to more computers.

[DocAElstein]

Simple recursion example 2

In simple general terms a recursion routine can be an efficient way to do a sort of looping while looking for something. Sometimes the idea of “tunnelling down” or digging down” can describe the situation well. Sometimes a standard looping routine of the Do Loop While type form can replace a recursion routine more efficiently ( https://www.excelforum.com/tips-and-...omparison.html )
The characteristic that possibly distinguishes recursion routines is the ability to seemingly intelligently go up and down: Compare it to the situation of digging down , whereby from time to time you come back up a bit, then dig down again in a slightly different direction. That is best illustrated by using a recursion routine for one of its most common practical uses, that of searching through Folders and sub Folders in a Directory. That will be done in the over next post.
As a pre requisite to that we will demonstrate how a much simpler recursion routine may dig down until a condition is met, and then, thereafter it comes back up, level for level , i.e. copy for copy. This usage is very similar to the standard looping routine of the Do Loop While type form except that you have a last coming back up the levels, or coming back up the copies, which you would not have with a simple Do Loop While type form ( https://www.excelforum.com/developme...ml#post4221234 )

We did not experience this coming back up in the last code because we Stoped. In general use of a recursion process, we do not have a simple way to end with like If ____ Then End Sub. Such a solution would be difficult to implement in a recursion process, as we would be ending the current copy with the End Sub.
So generally a recursion process ends by somehow “coming back up levels” or ending each copy one after the other, either
in sequence for a simple routine,
or
after going back and forth / up and down in a more complicated implementation of a recursion process.

In most cases the coming back up is rarely experienced. We have specifically a message box at that point to show when a copy of the routine is ended. In practical uses what happens at this point is ether nothing, or for more complex implementation of recursion, we may be in a Loop at that point which would determine if we “go back down” again: Some controlled looping at this point is what triggers the possibility to “go back down” , pseudo…

Sub Sub2( CpyNo , ______ )
CurrentCopyNumber=CpyNo
‘
‘
__Do
___Call Sub2(CurrentCopyNumber+1 , ______ )
__Loop While__
MsgBox Prompt:="You are Ending Sub2 , copy " & CurrentCopyNumber
End Sub

This will be discussed in the over next post.
For now we look at the simple case

One immediate way to stop us going further than say the second copy, would be to change our last coding pair from…_

Sub Sub1()
Dim StrtCpyNo As Long ' To count copy number of code instructions being run
 Let StrtCpyNo = 1
 
 
 Call Sub2(CpyNo:=StrtCpyNo, Msg:="Initial Message")
 
 MsgBox Prompt:="Ending main procedure"
End Sub
' Code above is Main calling procedure '____________________________________________________________________

' Code below is called procedure
Sub Sub2(ByVal CpyNo As Long, ByVal Msg As String)
Dim CopyNo As Long ' This is to indicate which copy of Sub2 is currently running
 Let CopyNo = CpyNo
 MsgBox Prompt:="You are Starting Sub2 , copy  " & CopyNo
 
Call Sub2(CpyNo:=CopyNo + 1, Msg:="Message coming from Sub2, copy  " & CopyNo & "")
 MsgBox Prompt:="You are Ending Sub2 , copy  " & CopyNo
End Sub

_... to

Sub Sub1()
Dim StrtCpyNo As Long ' To count copy number of code instructions being run
 Let StrtCpyNo = 1
 
 
 Call Sub2(CpyNo:=StrtCpyNo, Msg:="Initial Message")
 
 MsgBox Prompt:="Ending main procedure"
End Sub
' Code above is Main calling procedure '_____________________________________________________________________________

' Code below is called procedure
Sub Sub2(ByVal CpyNo As Long, ByVal Msg As String)
Dim CopyNo As Long ' This is to indicate which copy of Sub2 is currently running
 Let CopyNo = CpyNo
 MsgBox Prompt:="You are Starting Sub2 , copy  " & CopyNo

     If CopyNo < 2 Then Call Sub2(CpyNo:=CopyNo + 1, Msg:="Message coming from Sub2, copy  " & CopyNo & "")
 MsgBox Prompt:="You are Ending Sub2 , copy  " & CopyNo
End Sub

You can safely run the above coding, ( by running Sub1 ) , in normal mode, as it will no longer try to go on for ever. But it is probably more demonstrative to use debug F8 mode


Here is an attempt to show the last run as Excel VBA actually experienced it, - running Sub1 followed by two separate copies of Sub2, or rather
Start Sub1
_Start Sub2Copy1
___Start Sub2Copy2
___End Sub2Copy2
_End Sub2Copy2
End Sub1

Sub Sub1Sub2Sub2()
Dim StrtCpyNo As Long ' To count copy number of code instructions being run
 Let StrtCpyNo = 1


'Call Sub2(CpyNo:=StrtCpyNo, Msg:="Initial Message")
' Sub2 Copy 1
Dim CpyNo As Long: Let CpyNo = StrtCpyNo: Dim Msg As String: Let Msg = "Initial Message"
Dim CopyNo As Long ' This is to indicate which copy of Sub2 is currently running
 Let CopyNo = CpyNo
 MsgBox Prompt:="You are Starting Sub2 , copy  " & CopyNo

    If CopyNo < 2 Then
     'Call Sub2(CpyNo:=CopyNo+1, Msg:="Message coming from Sub2, copy  " & CopyNo & "")
     ' Sub2 Copy 2
    Dim CpyNo_ As Long: Let CpyNo_ = CopyNo + 1: Dim Msg_ As String: Let Msg_ = "Message coming from Sub2, copy  " & CopyNo & ""
    Dim CopyNo_ As Long ' This is to indicate which copy of Sub2 is currently running
     Let CopyNo_ = CpyNo_
     MsgBox Prompt:="You are Starting Sub2 , copy  " & CopyNo_
    End If
    If CopyNo_ < 2 Then
    Else
     MsgBox Prompt:="You are Ending Sub2 , copy  " & CopyNo_
    End If
    'End Sub2 ' End Copy 2 of Sub2
 MsgBox Prompt:="You are Ending Sub2 , copy  " & CopyNo
'End Sub2 ' End Copy 1 of Sub2

 MsgBox Prompt:="Ending main procedure"
End Sub

[DocAElstein]

.... this non-looping macro should also work...

I sometimes wonder whether we fool ourselves into thinking that these things are non looping. I don’t think any of us is privy to exactly what Excel is doing behind the scenes when these things work. The lack of clear documentation to these “array” type workings and the little fiddles you have to do sometimes to get these to work, makes me wonder if anyone knows exactly what is going wrong. Here is one of not many theories about how these sort of things might work.. http://www.excelfox.com/forum/showth...on-and-VLookUp That might suggest that what happens with these Evaluate Range things is similar to how maybe the CSE Array Entry formulas work in spreadsheets:
It suggests that we maybe “tap in” to something that gets done anyway whether we want to or not, and as such we don’t add too much extra. This something could be a “along the row, down a column, along a row…” type thing like a raster across an old telly screen which updates a complete screen/ worksheet. When we define the area in the initial selection of the spreadsheet area before adding our formula in a type 2 CSE Array Entry we may simply be exposing a large area rather than the usual single cell in a complete screen update. In other words the extra information for a multi cell range is there anyway and all we do is open up a bigger window so that we see it at once.
As far as I know, Evaluate was / is something to let you construct a string using both Excel spreadsheet and VBA stuff and then evaluate that string as if you wrote it manually in a spreadsheet cell.
Whether or not it was planned that it could be used in the way that people like Rick do as a sort of hidden type 2 CSE Array Entry formula , or whether that is an accident, I don’t know.

I don’t think I have ever noticed that these things wont work as a type 2 CSE Array Entry , ( occasionally they don’t work in evaluate and we have to coerce them into working ) , so taking Ricks last masterpiece and putting it in a spreadsheet like this will confirm that it works

Dim LastRow As Long, strEval As String
 Let LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 Let strEval = Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow)
 Range("B1:B" & LastRow).FormulaArray = "=" & strEval
 Debug.Print strEval 'IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A1," ",""),",","")),IF(LEFT(A1:A0,4)="2018",TRIM(A1:A0&" "&A2:A1),""),IF(LEFT(A1:A0,4)="2018",A1:A0,""))

_____ Workbook: NormanOrrinFilter.xlsm ( Using Excel 2007 32 bit )

Row\Col	B
1
2
3
4
5
6
7
8
9
10
11	2018, 1, 90515, 10024515, G9, SBlabla (HQ), CHE, BLABLA, blabla, 10012098, 12003.5
12	2018, 1, 90629, 10022334, P3, BLABLA blabla (blablabla), CHE, BLABLA,blabla, 10033609, 13941.72
13	2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah, 10006098, 15392.64
14
15	2018, 1, 90765, 10012123, P4, Ch of Blabla(Blabla of Blabla), CHE, BLA-BLA,Bla Blabla, 10005678, 16231.7
16
17	2018, 1, 90712, 10022908, P4, Snr BLA Off (Strat BLa, BLA), CHE, BLABLA,Bla BLabla, 10023234 14900.28
18
19	2018, 1, 90919, 10020984, P2, Ass BLA Balbla, CHE, BLA,Blabla, 10033098, 10486.33
20	2018, 1, 95706, 10023098, NB, Assc BLA Blabal (LatBLAa), BLA, BLABLABLA,Blabla, 10034318, 7566.31
21
22
23
24
25
26
27
28
29	2018, 1, 95716, 10018763, NA, Asst BLA Off (Blabla & Multi-BLa), BLA, BLA,Bla, 10097776, 8607.96
30	2018, 1, 99716, 10026132, G5, Snr BLA Asst (Bla Blabla), BLA, BLABLA,bla BLa, 18767043, 5477.44
31	2018, 1, 99716, 10016545, G6, Blabla Blabla (BLA), BLA, BLABLABLA,Blabla, 1097029, 5325.3
32
33

Worksheet: Rick
_.________________________________--

I would guess that whoever wrote the .SpecialCells did that quite efficiently. Who knows, they may even have used some of this “hidden array “ techniques to do it.
So a combination of .SpecialCells and evaluate range one liners sounds an attractive combination , in my opinion.

_._________
Coming back to the solution from Rick… I was interested to work through it.. so I did. I put my workings here, as the OP or anyone else viewing this thread might be interested.

Alan

[DocAElstein]

Post to support this Thread:
http://www.excelfox.com/forum/showth...0888#post10888
_1) This part of Rick’s solution
Evaluate(Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow))

I have seen something similar to this before, but it is lost to mankind hidden down in the comment section of a Blog site, Allen Wyatt’s I think…… so its nice that something like this has seen the light of day here…

If I am not mistaken, this non-looping macro should also work...

Sub ThisShouldWork()
 Dim LastRow As Long
 LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 Range("A1:A" & LastRow) = Evaluate(Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow))
 Range("A1:A" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

To help simplify the explanation, lets take it that we know our range , ( http://www.excelfox.com/forum/showth...-row#post10870 ) so we have LastRow = 40
Two arbitrarily chosen characters, @ and # , are being used to enter into the main formula the LastRow or LastRow +1
Pseudo like we are doing this sort of thing
Replace( “A#” , “#” , “40” ) in order to end up with like “A40”
By inspection of the main formula, and with a bit of eye straining you can probably see where you replace those @ and # with 40 and 41
Just to be sure , running this will get you a nice copy able version of the main formula in the immediate window , ( after running you Hit Ctrl+g from the VB Editor to get the immediate window up):

Sub ThisShouldWork()
Dim LastRow As Long, strEval As String
 Let LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 Let strEval = Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow)
 'Range("B1:B" & LastRow).FormulaArray = "=" & strEval
 Debug.Print strEval  'IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A41," ",""),",","")),IF(LEFT(A1:A40,4)="2018",TRIM(A1:A40&" "&A2:A41),""),IF(LEFT(A1:A40,4)="2018",A1:A40,""))

That did work.JPG : https://imgur.com/01sQ91X

_._______________________-
Before moving on a useful note: It is always useful when developing these formulas to view the string in the Immediate window: That can help with tricky syntaxes : The formula seen on the Immediate window must look like a formula in the same syntax as you would manually type it into a cell. So you can see immediately if you get something wrong , such as an error in the finally seen quotes.
_.__________________________
So we have our final formula:
IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A41," ",""),",","")),IF(LEFT(A1:A40,4)="2018",TRIM(A1:A40&" "&A2:A41),""),IF(LEFT(A1:A40,4)="2018",A1:A40,""))
The way these formulas appear to work within the Evaluate(“ “) appears to be tapping into an along the columns , down a row, then along the columns… type updating raster to update a worksheet. The available output then seems to be that which encompasses the deepest and widest ranges. It is a ,little bit more complicated than that ( http://www.excelfox.com/forum/showth...on-and-VLookUp ) , but for our formula we have nice regular equally sized ranges so we are expecting an output of 1 “wide” and 40 “deep”. So for analysis purposes, we can reduce the formula to 40 similar ones.

Lets take the example of the formula for the 13th “down” output ..
IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13&" "&A14),""),IF(LEFT(A13,4)="2018",A13,""))
Clearly we need to look at this data to see what that formula will do, because this data is used in that formula
_____ Workbook: NormanOrrinRickFilter.xlsm ( Using Excel 2007 32 bit )

Row\Col	A
13	2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah,
14	10006098, 15392.64

Worksheet: Rick

We have some nested IFs , and I find it is always a good idea to break those down so that we can start doing them as Excel or VBA would do them, that is to say from the middle working outwards. I tend to do this in a text editor with a horizontal scroll bar, or in the VB Editor window
Formula in VB Editor as comment.JPG : https://imgur.com/3cjyqSR

So this is what we have, broken down into the constituent IF sections.
( It may be better to copy this and view in your VB Editor in a wide window. I am working from the bottom , upwards )

' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018" , TRIM(A13" "&A14) , "") , IF( LEFT(A13,4)="2018" , A13 ,"" ) ) 
' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),"") , IF(LEFT(A13,4)="2018",A13,"") )
' IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),""),IF(LEFT(A13,4)="2018",A13,""))

Examining the first line , I can evaluate the two innermost IFs and reduce the formula to

' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), TRIM(A13" "&A14) , A13 ) 
' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018" , TRIM(A13" "&A14) , "") , IF( LEFT(A13,4)="2018" , A13 ,"" ) ) 

I will now evaluate some of those SUBSTITUTEs
( Excel Substitute, seems to work similarly to VBA Replace )

' IF( ISNUMBER(0+1000609815392.64), TRIM(A13" "&A14) , A13 ) 
' IF( ISNUMBER(0+SUBSTITUTE(10006098,15392.64),",","")), TRIM(A13" "&A14) , A13 ) 

( I am guessing that 0+ will ensure that a number will not be mistaken as a text )

For the case of the 13th “down” formula the final steps in the evaluation go as follows

' 2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah, 10006098, 15392.64
' TRIM(A13" "&A14)

' IF( True , TRIM(A13" "&A14) , A13 )

Here are all the steps together again

' 2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah, 10006098, 15392.64
' TRIM(A13" "&A14)

' IF( True , TRIM(A13" "&A14) , A13 ) 


' IF( ISNUMBER(0+1000609815392.64), TRIM(A13" "&A14) , A13 ) 
' IF( ISNUMBER(0+SUBSTITUTE(10006098,15392.64),",","")), TRIM(A13" "&A14) , A13 ) 

' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), TRIM(A13" "&A14) , A13 ) 
' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018" , TRIM(A13" "&A14) , "") , IF( LEFT(A13,4)="2018" , A13 ,"" ) ) 


' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018" , TRIM(A13" "&A14) , "") , IF( LEFT(A13,4)="2018" , A13 ,"" ) ) 
' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),"") , IF(LEFT(A13,4)="2018",A13,"") )
' IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),""),IF(LEFT(A13,4)="2018",A13,""))

The final result will appear in the 13th down position of the 40 “deep” array final results for the entire formula evaluation.
If you can view that last summary on a wide window, it should be able to see how the differing results for the other 39 results are achieved from the formula
Just to make clear once again what seems to go on in these sort of Evaluate formulas, in the next post is a table showing the actual Evaluateions done by VBA

_._____

_2 The final part of Rick’s solution is
Range("A1:A" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete
This uses the VBA SpecialCells Method to get at the cells with nothing in them. Those are then deleted
Explanation:
VBA SpecialCells Method ( https://www.mrexcel.com/forum/excel-...onditions.html , https://docs.microsoft.com/en-us/off...e.specialcells ) returns you a range object ( that range object must not be contiguous ( connected ) cells ) consisting of those cells meeting a specific characteristic. We can choose from a number of characteristics. Here we choose xlBlanks , which refers to the characteristic of the cell being empty. So, if we applied that .SpecialCells(xlBlanks) to this range:.._

Row\Col	B
9
10
11	2018, 1, 90515, 10024515, G9, SBlabla (HQ), CHE, BLABLA, blabla, 10012098, 12003.5
12	2018, 1, 90629, 10022334, P3, BLABLA blabla (blablabla), CHE, BLABLA,blabla, 10033609, 13941.72
13	2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah, 10006098, 15392.64
14
15	2018, 1, 90765, 10012123, P4, Ch of Blabla(Blabla of Blabla), CHE, BLA-BLA,Bla Blabla, 10005678, 16231.7

_ … then the returned range from that would be Range(“B9:B10,B14”).
If we then apply .Delete to that range then those cells are removed. If you remove a cell via .Delete then initially there is a real hole, like a “black hole” that can’t really exist in a spreadsheet. So Excel might explode or implode, or you would be sucked into that hole , never to return!!! To prevent that happening, Excel shifts all cells to close that hole, ( and adds a new virgin cell at the bottom or right side to fill the indent there caused by the shift. The default Delete option for the direction of that shift is in our case upwards. Hence after applying the .Delete after applying .SpecialCells(xlBlanks) to the above range, we will be left with

Row\Col	B
9	2018, 1, 90515, 10024515, G9, SBlabla (HQ), CHE, BLABLA, blabla, 10012098, 12003.5
10	2018, 1, 90629, 10022334, P3, BLABLA blabla (blablabla), CHE, BLABLA,blabla, 10033609, 13941.72
11	2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah, 10006098, 15392.64
12	2018, 1, 90765, 10012123, P4, Ch of Blabla(Blabla of Blabla), CHE, BLA-BLA,Bla Blabla, 10005678, 16231.7
13
14

What has happened there is the following: Those empty cells ( which were yellow ) have been removed. Other cells have been shifted up to fill up the “holes” created by the removal

_.______________________________________________

Just to make clear once again what seems to go on in these sort of Evaluate formulas, in the next post is a table showing the actual Evaluateions done by VBA

[DocAElstein]

Continued from last post

In a range evaluate type code line like the one we are considering, Excel VBA seems to do the following ( simplified )

Excel will have an output “window” ( this could be considered as an output table or output array ). The dimensions of this will be that rectangle that allows all used ranges in the formula to be fitted in,
There are some complicated ways in which Excel handles the situation of ranges of varying size, ( http://www.excelfox.com/forum/showth...on-and-VLookUp ) but for a simpler case of all ranges having the same size, ( in terms of “width” and “depth” ) , as we have, Excel VBA will “expand” its “output window” to this sort of thing:

Excel VBA will do its normal “along the columns, down a row, along the columns…” type thing, in any “Evaluation run”. In our case this will mean that it does an evaluation at each row, going down the rows. This is what Excel VBA does in order to fill that last window of cells, ( I am just showing the first 7 of 40 similar formulas as the full list is to big to fit in a forum post )

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2," ",""),",","")),IF(LEFT(A1,4)="2018",TRIM(A1&" "&A2),""),IF(LEFT(A1,4)="2018",A1,""))

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A3," ",""),",","")),IF(LEFT(A2,4)="2018",TRIM(A2&" "&A3),""),IF(LEFT(A2,4)="2018",A2,""))

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A4," ",""),",","")),IF(LEFT(A3,4)="2018",TRIM(A3&" "&A4),""),IF(LEFT(A3,4)="2018",A3,""))

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A5," ",""),",","")),IF(LEFT(A4,4)="2018",TRIM(A4&" "&A5),""),IF(LEFT(A4,4)="2018",A4,""))

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A6," ",""),",","")),IF(LEFT(A5,4)="2018",TRIM(A5&" "&A6),""),IF(LEFT(A5,4)="2018",A5,""))

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A7," ",""),",","")),IF(LEFT(A6,4)="2018",TRIM(A6&" "&A7),""),IF(LEFT(A6,4)="2018",A6,""))

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A8," ",""),",","")),IF(LEFT(A7,4)="2018",TRIM(A7&" "&A8),""),IF(LEFT(A7,4)="2018",A7,""))

Excel VBA will effectively make 40 formulas and place in the “output window” the result of the evaluation of those formulas
The full demo code in the next post includes a code line to put in all 40 formulas in an arbitrary 40 “deep” x 1 “wide” range ("J5:J44")

[DocAElstein]

Full demo code to accompany last post:

Option Explicit
Sub ThisShouldWork()
Dim LastRow As Long, strEval As String
 Let LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 Let strEval = Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow)
 Debug.Print strEval ' Hit Ctrl+g from the VB Editor to get the Immediate window up.                                                                                              'IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A41," ",""),",","")),IF(LEFT(A1:A40,4)="2018",TRIM(A1:A40&" "&A2:A41),""),IF(LEFT(A1:A40,4)="2018",A1:A40,""))
'This is the spreadsheet equivalent to Rick's Evaluate
 Range("B1:B" & LastRow).FormulaArray = "=" & strEval
'This gives a demo of the actual formulas that Excel VBA does
 Range("J5:J44").Value = "=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2,"" "",""""),"","","""")),IF(LEFT(A1,4)=""2018"",TRIM(A1&"" ""&A2),""""),IF(LEFT(A1,4)=""2018"",A1,""""))" ' Applying the fixed vector notation (Excel instructed to do that by no $s) will result in the same relative formula. Displayed will be the actual formula ( in the relative form, but that is not important)
  
' Final solution  Rick : http://www.excelfox.com/forum/showth...0888#post10888
  Range("A1:A" & LastRow) = Evaluate(Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow))
'  Range("A1:A" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete ' This will mess up now due to my .FormulaArray  as you can't delete bits of that
End Sub



'          2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah, 10006098, 15392.64
'                        TRIM(A13" "&A14)

'   IF(      True        ,   TRIM(A13" "&A14)        ,       A13       )


'   IF(       ISNUMBER(0+1000609815392.64),   TRIM(A13" "&A14)        ,       A13       )
'   IF(       ISNUMBER(0+SUBSTITUTE(10006098,15392.64),",","")),   TRIM(A13" "&A14)        ,       A13       )

'   IF(       ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),   TRIM(A13" "&A14)        ,       A13       )           )
'   IF(       ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018"  ,  TRIM(A13" "&A14)  ,  "")      ,     IF( LEFT(A13,4)="2018"  ,  A13  ,"" )     )


'   IF(       ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018"  ,  TRIM(A13" "&A14)  ,  "")      ,     IF( LEFT(A13,4)="2018"  ,  A13  ,"" )     )
'      IF(       ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),"")      ,     IF(LEFT(A13,4)="2018",A13,"")     )
'            IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),""),IF(LEFT(A13,4)="2018",A13,""))

[DocAElstein]

( P.M. )

Hi Sam,
I have quick question. I am asking if I may post codes , Tables, etc. in , for example your Test Area, and reference those Posts from other Forums.

_.....The idea is basically similar to something I do already:...You may have noticed:..._..

I current use this thread in your Test Area Sub Forum.
http://www.excelfox.com/forum/showth...L-Tables-etc-)
I reference codes or table or extended explanations there from other posts.
I do this so as to have less clutter in the main Threads from which these “appendix” posts are referenced.

I also do this over at ExcelForum, where these “appendix” Threads appear very popular
http://www.excelforum.com/showthread.php?t=1101544
http://www.excelforum.com/showthread.php?t=1148621
http://www.excelforum.com/showthread.php?t=1147913
http://www.excelforum.com/showthread.php?t=1154829
Those are mainly used by me for “appendix” posts referenced by one or more reply posts I do when answering Threads at ExcelForum.
Occasionally, I also reference those posts from other Forums.

There is a disadvantage for me in using those Threads at ExcelForum.
_ You must be logged in to view the Test Area at ExcelForum, so people who are not registered cannot vie them.
_ You cannot use HTML anywhere at ExcelForum ( you have that enabled in your Test Sub Forum )
_ ExcelForum is increasingly unavailable for long periods due to Software problems
_ Many other software problems at ExcelForum , including viruses, are making people more and more reluctant to use the site.

So, if I may, I would like to use in addition ( or possibly instead ) ExcelFox for some of my “appendix” Posts
_......

So, if may, I would like to post codes and the such that may not ( at least initially ) be relevant to Threads at ExcelFox. I will then post a link to those posts as appropriate in other Forums.
I do not think it is appropriate for me necessarily to post in these in the Excel Tips and Tricks Sub Forum as the content may not be “stand alone”, that is too say the content may not be so meaningful on its own.
( I am only a part time “Exceller”, and a Novice at that – so I would never get anywhere near the Heights of the likes of Rick Rothstein oder Rajan Verma. – So there would never be the requirement for me to have my own “corner” )
_.......

( By the way, I do this partly already: In my signature both at ExcelFox and ExcelForum
http://www.excelfox.com/forum/showth...=9821#post9821
I assumed that was OK as I am just referencing some Forum Posting Tools that I use in both Forums )



Thanks
Alan Elston.

Hi Admin,
Thanks for the reply
As I mentioned, I did not want an individual Forum. Indeed even if available, I would not think it appropriate for my posts.
I was just wondering if I could post some of my stuff in the Test Area, for the reasons I mentioned:
_ Posts that are not “stand alone” Tips and tricks are less suited to Tips, Tricks. They would be for me to reference to when answering specific Posts and Blogs elsewhere.
_ In Test Forum I have HTML
_ In Test forum I have unlimited Edit time.


So the question was if, in addition to Tips and Tutorials, I may also post in the Test Area, in the fashion that I do, as indicated, at ExcelForum?
http://www.excelforum.com/developmen...te-thanks.html
http://www.excelforum.com/developmen...ly-needed.html
http://www.excelforum.com/developmen...ly-needed.html


_ The purpose of this will be following:
Codes and Information , To be referenced in this web site , Also to be referenced from other web sites
Thanks
Alan

No issues

( Per PM: )

No restriction in Test Area