Testing Posts, Internet, Forum Software

Printable View

09-16-2015, 09:08 PM
DocAElstein

1 Attachment(s)

Testing Posts, Internet, Forum Software

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/
09-16-2015, 09:09 PM
DocAElstein

4 Attachment(s)

Hi Jeff,
Why have you deleted this Thread_...
http://imgur.com/m17fPbU ( http://www.excelforum.com/hello-intr...-thoughts.html )
_.. which you started?, as well as my Reply:

Quote:

Originally Posted by jeffreybrown

I guess I know the answer, but doesn't mean I actually want to do it, especially to a member with so many years, posts, and the title of Forum Expert.
http://www.excelforum.com/excel-gene...la-needed.html
I know there shouldn't be any exceptions, but I suppose infractions are warranted!....

Hi Jeff, :)
I do not quite understand what you are asking?
( The OP said he was unable to change the Title. http://www.excelforum.com/excel-gene...ml#post4591186 )

Are you suggesting that Special-K should be given an infractions for continuing after your Moderator's message?

If so I think the issue that needed to be addressed first is with the OP and why he is / was unable to change the Title. Just my Opinion.. I think you should have followed that up. http://www.excelforum.com/excel-gene...ml#post4591186 Just my Opinion.. ***

But I do agree that a good title is very important. That is one rule that I do feel is very important.
_.....

***
_ Possibly the OP was posting at a time when there were software problems making editing difficult ( he mentions the dreaded spinning wheel that we all hate so much ).
Or
_ He is using one of the Forum styles other than default. Sometimes not all options are available in all styles:......
ForumStyles.JPG http://imgur.com/hKhyE8M
http://www.excelforum.com/the-water-...ml#post4543964
http://www.excelforum.com/the-water-...ml#post4543988
http://www.excelforum.com/the-water-...ml#post4544280
_Also as a new OP -he may not yet be able to Edit posts!
duckersjNewOpIsMatey.JPG http://imgur.com/tTubchA
( I beleive such things may be restricted for new users to help prevent spammers joining and making a mess )

Alan

P.s. Do you realise that you are posting this http://www.excelforum.com/hello-intr...-thoughts.html in the Sub Forum (Off Topic) Hello..Introduce yourself ?
JeffHelloIntroduceInfractionsWonk.JPG http://imgur.com/m17fPbU
I do not think that is the appropriate Sub Forum. ( Probably Water Cooler or Suggestions for Improvement or Feedback / Comments / Suggestions etc. But just my opinion. I do not think you should receive an infraction for incorrect posting.. Lol... – Possibly just a negative reputation would do .. Lol... :) --- seeking anyone's thoughts ?? :)
09-16-2015, 09:21 PM
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
09-16-2015, 09:22 PM
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.
09-16-2015, 09:23 PM
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…_

Code:

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

Code:

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

Code:

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

Quote:

Originally Posted by Rick Rothstein

.... 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

Code:

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

_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

Pop Up User InputBox with range selection alternative with API User 32 dll Programs

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"",TR IM(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…

Quote:

Originally Posted by Rick Rothstein

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

Code:

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):

Code:

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:A4 0&" "&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 )

Code:

' 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

Code:

' 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 )

Code:

' 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

Code:

' 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

Code:

' 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

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

_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

_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

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

01-25-2017, 10:11 PM
DocAElstein

ExtendingInsensibility into Code modules. Copy table contents to VBIDE VB Editor code modules

Full demo code to accompany last post:

Code:

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,""))
02-05-2017, 06:44 PM
DocAElstein

Communication regarding my use of Test Forum at Excel Fox

( P.M. )

Quote:

Originally Posted by Alan

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.

Quote:

Originally Posted by Alan

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

Quote:

Originally Posted by Admin

No issues :)

( Per PM: )

Quote:

Originally Posted by Admin

No restriction in Test Area :)
02-06-2017, 01:33 PM
Admin

Hi Alan,

Why don't you post the codes in the original thread ? Or are you testing the code over here ?
02-06-2017, 02:18 PM
DocAElstein

Hi Admin
Yes, just testing it here.
I often edit and re use the post.
I sometimes re use, edit a bit etc.
So just testing in this Thread.
Another Thread I use here for the codes I use in answering Threads. That helps to keep the Thread less clutered for longer codes.
I post most in the original Threads.
Alan
02-06-2017, 04:03 PM
Admin

Quote:

Originally Posted by DocAElstein

Hi Admin
Yes, just testing it here.
I often edit and re use the post.
I sometimes re use, edit a bit etc.
So just testing in this Thread.
Another Thread I use here for the codes I use in answering Threads. That helps to keep the Thread less clutered for longer codes.
I post most in the original Threads.
Alan

No issues :)
02-13-2018, 12:30 PM
DocAElstein

1 Attachment(s)

Excel VBA comma point thousand decimal separator number problem.

Inserting code lines
The line number of the code to which I am talking about here is , as defined by, or rather as held internally by, and accessed in code coding by, a sequential integer starting at 1 at the top of the code window and counting by +1 for every successive line/row. In other words , VBA holds somewhere in memory a set of numbers like pseudo the 1 2 3 and 4 here:
1 Option Explicit
2 Sub MyCode()
3 ‘Code
4 End Sub
These numbers we do not see and they are independent of any code lines which we may add: ( We can in addition , use any line numbers or labels as we choose. ( We can choose to use the same line numbers as those held internally, which I do in some places below, just to aid in the demonstration. Therefore those numbers which I use can be regarded , for demonstration purposes, as those held internally ) )

Single lines
Important to note here is that a virgin code module has no code lines. It cannot be thought of in terms of a spreadsheet of rows waiting to be filed in. You cannot reference any row in the code module until either
_ typed lines are present
or
_ at least the rows “exist” as for example , by hitting the Enter key.
However, if you try to insert lines/ rows above the current maximum row, using coding, then the coding will not error: it will add a new line at one line above the last used line.
If you insert lines/rows at up to and including the last used row, then all existing lines get shifted down
Because of these facts, it is easy to get disorientated in coding that inserts lines.
Here an example: we insert lines at arbitrary non existent line numbers well above the last line, here the code before and after running the code:
Before:

Code:

' Line 1 Note: I add these numbers just for demonstration to represent the numbers held internally by VBA for referring to lines by number in a code module: We may use any numbers in any order. But they have nothing to do with the internally held numbers. I choose to use the same value as the internally held corresponding numbers for the line here just for demonstration purposes Sub Insuerts() ' Line 2 3 Dim CodMod As Object: Set CodMod = ThisWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule 4 CodMod.insertlines Line:=2000, String:="' Line 9" 5 CodMod.insertlines Line:=15, String:="' Line 10" 6 CodMod.insertlines Line:=159, String:="' Line " & CodMod.countoflines + 1 ' CodMod.countoflines evaluates to 10 after lines 9 and 10 were added in the last two lines , and then this line is added at the end so becomes line 11 End Sub ' Line 7 ' line 8 This is the last line used Before the code is run

After:

Code:

' Line 1 Sub Insuerts() 3 Dim CodMod As Object: Set CodMod = ThisWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule 4 CodMod.insertlines Line:=2000, String:="' Line 9" 5 CodMod.insertlines Line:=15, String:="' Line 10" 6 CodMod.insertlines Line:=159, String:="' Line " & CodMod.countoflines + 1 ' CodMod.countoflines evaluates to 10 after lines 9 and 10 were added in the last two lines , and then this line is added at the end so becomes line 11 End Sub ' line 8 ' Line 9 ' Line 10 ' Line 11

The above shows us that “inserting” above the last existing code line will actually add a single code line at the next line/row above the last existing line. Looping to add lines at the end of a code module is therefore somewhat redundant as a code line such as the following would ensure that lines are added sequentially
__.insertlines Line:=__ .countoflines + 1, String:=" ' This will be at the next free line. "
In that code line, any number >=1 can be used in place of 1
Care must be taken when “adding” code lines using insertlines . For example if the first insertlines from the last ( After ) , code is modified to insert/add at the last line such:…_

Code:

' Line 1 Sub Insuerts() 3 Dim CodMod As Object: Set CodMod = ThisWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule 4 CodMod.insertlines Line:=CodMod.countoflines, String:="' New Line" ' this will insert at line 11 – currently CodMod.countoflines=11 '5 CodMod.insertlines Line:=15, String:="' Line 10" '6 CodMod.insertlines Line:=159, String:="' Line " & CodMod.countoflines + 1 11 End Sub ' line 8 ' Line 9 ' Line 10 ' Line 11

_.....then the results after running that above code will be as follows…._

Code:

' Line 1 Sub Insuerts() 3 Dim CodMod As Object: Set CodMod = ThisWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule 4 CodMod.insertlines Line:=CodMod.countoflines, String:="' New Line" '5 CodMod.insertlines Line:=15, String:="' Line 10" '6 CodMod.insertlines Line:=159, String:="' Line " & CodMod.countoflines + 1 ' CodMod.countoflines evaluates to 10 , and then this line is added at the end so beacomes line 11 End Sub ' line 8 ' Line 9 ' Line 10 ' New Line ' Line 11

_.... as we see the code at ( and above if there had been ) the insert line is shifted down. In this case we inserted at the last line, rather than, as previously , trying to insert above the last line

Multi line inserting. In one go. Single string
Like many seemingly “page” type things in computers, the “page text” is actually held in a single long string. By analysing the string in detail , ( SLLHkjsjhfkJHFhfsajkhfaskjfhakjfhkjfhkjfhskjfhs ) , we often find that we have a “character” or characters of this form : vbCr & vbLf . This is a throw back to old computer printer things and this forces a carriage return and line feed. In other word it “makes a new line. We find that these constants are in the string held for a spreadsheet multi line range as well as that for the text in a code module. It can therefore be convenient to use a string of that form in a single string insert line to copy a multi line range into a code module. http://www.eileenslounge.com/viewtop...=31395#p242941

For this Thread we will insert/add single lines at a time, to allow some formatting of each line.

Multi line inserting. Via looping.
If lines are inserted in a loop, then things occur in a simple way, (We are considering here that each loop inserts one line ): a line is inserted exactly as indicated in the argument line:=. All existing lines are shifted upwards. So finally all lines above the code section inserted by the looping will be at a line number equall to its original number + the number of loops done.
Inserting, or rather adding, lines beyond the current last line is a bit more subtle to understand.
As noted previously, we can’t actually directly add a line or insert above the last existing line. If we try to add/insert above the last line then a new line is added. This means that the actual line number given will be ignored, and the added code section will be directly above the previous coding. However, if we want our given line number to “pseudo” define the line number finally of the added lines, then we can do that if we give the start line number that of one more than the current last line number.
Here a demo example
Before:

Code:

'line 1 Note: I add these numbers just for demonstration to represent the numbers held internally by VBA for referring to lines by number in a code module: We may use any numbers in any order. But they have nothing to do with the internally held numbers. I choose to use the same value as the internally held corresponding numbers for the line here just for demonstration purposes 'Line 2 Sub LoopYinLinesTiddlyHiFoo() 4 Dim VBIDEVBAProj As Object 5 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.codemodule 6 Dim LineNo As Long, StartLine As Long, StopLine As Long 7 Let StartLine = VBIDEVBAProj.countoflines + 1: Let StopLine = StartLine + 4 8 For LineNo = StartLine To StopLine 9 VBIDEVBAProj.insertlines Line:=LineNo, String:="'Line " & LineNo 10 Next LineNo End Sub

After:

Code:

'line 1 'Line 2 Sub LoopYinLinesTiddlyHiFoo() 4 Dim VBIDEVBAProj As Object 5 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.codemodule 6 Dim LineNo As Long, StartLine As Long, StopLine As Long 7 Let StartLine = VBIDEVBAProj.countoflines + 1: Let StopLine = StartLine + 4 8 For LineNo = StartLine To StopLine 9 VBIDEVBAProj.insertlines Line:=LineNo, String:="'Line " & LineNo 10 Next LineNo End Sub 'Line 12 'Line 13 'Line 14 'Line 15 'Line 16

The important point to note here is that we have not defined where the lines go directly in terms of the .insertlines Line:= given. We have simply adjusted the numbers used in the loop so that it appears that way.
Just to help make that point clear. Consider the same experiment again, with just one change. This time in the Before we are attempting to insert lines way down in the code module, by adjusting the number given in .insertlines Line:= by 100
Before:

Code:

'line 1 'Line 2 Sub LoopYinLinesTiddlyHiFoo() 4 Dim VBIDEVBAProj As Object 5 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.codemodule 6 Dim LineNo As Long, StartLine As Long, StopLine As Long 7 Let StartLine = VBIDEVBAProj.countoflines + 1: Let StopLine = StartLine + 4 8 For LineNo = StartLine To StopLine 9 VBIDEVBAProj.insertlines Line:=LineNo + 100, String:="'Line " & LineNo 10 Next LineNo End Sub

The corresponding After , in terms of the added lines is exactly the same as before:

Code:

'line 1 'Line 2 Sub LoopYinLinesTiddlyHiFoo() 4 Dim VBIDEVBAProj As Object 5 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.codemodule 6 Dim LineNo As Long, StartLine As Long, StopLine As Long 7 Let StartLine = VBIDEVBAProj.countoflines + 1: Let StopLine = StartLine + 4 8 For LineNo = StartLine To StopLine 9 VBIDEVBAProj.insertlines Line:=LineNo + 100, String:="'Line " & LineNo 10 Next LineNo End Sub 'Line 12 'Line 13 'Line 14 'Line 15 'Line 16

The point may still not be clear, so just to put in words again the issue:
Because we give a line number in the argument .insertlines Line:= of greater than the current last line number, then that actual number given bears no relation to the actual line number of the code line at which it will be added. ( The line number of the code I am talking about here is , as defined by, or rather as held internally by, and accessed in code coding, by a sequential integer starting at 1 at the top of the code window and counting by +1 for every successive line/row )
Because we give a line number in the argument .insertlines Line:= of greater than the current last line number, then lines will always be added at the next free line, that is to say one line above the last used line. The actual number we give is irrelevant, for numbers we give which are greater than that of the current last used line in the code module.
By choosing carefully the number of, in particular the variable StartLine , I am, however, able to predict the actual line number at which each code line is added.
03-20-2018, 08:42 PM
DocAElstein

Test

Example
Post #3 Copy table contents to VBE module

The idea of these codes are to allow for a temporary storage of spreadsheet values to a code module.
_ One code will add a table of values to the end of a code module, and an extra start and end line will be added which contains range and date info. This code uses the selected range. So a range must be selected before running this code.
and
_ a second code can be used to paste those values back to the same range.
Optionally a date of entry can be given to search for, otherwise all table values are pasted in

The codes are somewhat detailed and inefficient. They work in many places “line by line”. There are some more simpler and more efficient codes here. http://www.eileenslounge.com/viewtop...=31395#p243002

Sub PubProliferous_Let_RngAsString__()
This code puts a selected range of values into the code module in which the code at the position of just after this coding. The table data values will be added to ( or taken from in following codes ) the module in which the code is run.
Here a brief walk through the code. There are more details in the 'comments
Rem 0 Sets for convenience, a variable to the code module in which the code is placed/ run from
Rem 1
A code module used for storing a table will be given the extension to its code name of "_txt" No special reason for doing this, I just thought it might be useful for later reference to know that the code module is being used in such a way. ( If no more table data is in the code module, then the extension , "_txt" , will be removed. This will be done , for example in following codes after the data is removed.)
Rem 2
Before running the code, a spreadsheet range should be selected. The range is copied to the clipboard, and the text of that put in a string variable, strIn
Rem 3
The string format is changes slightly to allow better display in this code ( and to aid in manipulation in the codes which re copy the data back to the spreadsheet, http://www.eileenslounge.com/viewtop...=31395#p242941
Rem 4
This adds an “extra line” to the start of the string with range information and current date, and an extra line at the end of the string with the same date. (I use date format of 10 digits as DD MM YYYY. ( This needs to be added to the data retrieving codes if looking for data from a specific date ) )
As demo example, say I copied a 2 row x 2 cell range, B2:C3 to the clipboard. Say the range looked like this
A B
C D
At this point in the routine, after Rem 4 , I will have in strIn , something of this approximate/pseudo form, ( say for a date of 12th December, 2018 ):

Code:

= " '_-20 12 2018 Worksheets("Tabelle2").Range("B2:C3") " & vbCr & vbLf & “|” & “A” & “|” & “B” & vbCr & vbLf & “|” & “C” & “|” & “D” & vbCr & vbLf & “ '_-20 12 2018 “

Rem 5
The above string is converted to an array by splitting by the & vbCr & vbLf & . So effectively , I would end up with something of this form , for the above example , a 4 element, 1 Dimensional array:
{ SpltRws(0) , SpltRws(1) , SpltRws(2) , SpltRws(3) }
where
SpltRws(0) = " '_-20 12 2018 Worksheets("Tabelle2").Range("B2:C3") "
SpltRws(1) = “|” & “A” & “|” & “B”
SpltRws(2) = “|” & “C” & “|” & “D”
SpltRws(3) = “ '_-20 12 2018 “

Alan
12-21-2018, 09:21 PM
DocAElstein

Testing

test test

askjfhsakjhfaskfhah

Example
Post #3 Copy table contents to VBE module (Post#4 Re Paste back to spreadsheet)
3) Copy table contents to VBE module

The idea of these codes are to allow for a temporary storage of spreadsheet values to a code module.
_ One code will add a table of values to the end of a code module, and an extra start and end line will be added which contains range and date info. This code uses the selected range. So a range must be selected before running this code.
and
_ a second code can be used to paste those values back to the same range.
Optionally a date of entry can be given to search for, otherwise all table values are pasted in

The codes are somewhat detailed and inefficient. They work in many places “line by line”. There are some more simpler and more efficient codes here. http://www.eileenslounge.com/viewtop...=31395#p243002

Sub PubProliferous_Let_RngAsString__()
This code puts a selected range of values into the code module in which the code at the position of just after this coding. The table data values will be added to ( or taken from in following codes ) the module in which the code is run.
Here a brief walk through the code. There are more details in the 'comments
Rem 0 Sets for convenience, a variable to the code module in which the code is placed/ run from
Rem 1
A code module used for storing a table will be given the extension to its code name of "_txt" No special reason for doing this, I just thought it might be useful for later reference to know that the code module is being used in such a way. ( If no more table data is in the code module, then the extension , "_txt" , will be removed. This will be done , for example in following codes after the data is removed.)
Rem 2
Before running the code, a spreadsheet range should be selected. The range is copied to the clipboard, and the text of that put in a string variable, strIn
Rem 3
The string format is changes slightly to allow better display in this code ( and to aid in manipulation in the codes which re copy the data back to the spreadsheet, http://www.eileenslounge.com/viewtop...=31395#p242941
Rem 4
This adds an “extra line” to the start of the string with range information and current date, and an extra line at the end of the string with the same date. (I use date format of 10 digits as DD MM YYYY. ( This needs to be added to the data retrieving codes if looking for data from a specific date ) )
As demo example, say I copied a 2 row x 2 cell range, B2:C3 to the clipboard. Say the range looked like this
A B
C D
At this point in the routine, after Rem 4 , I will have in strIn , something of this approximate/pseudo form, ( say for a date of 12th December, 2018 ):

Code:

= " '_-20 12 2018 Worksheets("Tabelle2").Range("B2:C3") " & vbCr & vbLf & “|” & “A” & “|” & “B” & vbCr & vbLf & “|” & “C” & “|” & “D” & vbCr & vbLf & “ '_-20 12 2018 “

Rem 5
The above string is converted to an array by splitting by the & vbCr & vbLf & . So effectively , I would end up with something of this form , for the above example , a 4 element, 1 Dimensional array:
{ SpltRws(0) , SpltRws(1) , SpltRws(2) , SpltRws(3) }
where
SpltRws(0) = " '_-20 12 2018 Worksheets("Tabelle2").Range("B2:C3") "
SpltRws(1) = “|” & “A” & “|” & “B”
SpltRws(2) = “|” & “C” & “|” & “D”
SpltRws(3) = “ '_-20 12 2018 “
Rem 6
We examine the dimensions of the array and determine which lines will be used at the end of the code module
Rem 7
'7a) '7d) The extra start and end lines are added as simple lines.
'7b) This is the main loop for inserting/(actually adding**) data lines
'7c) this second inner loop builds up the actual line to be outputted from the columns/cells: The row is first split into each cell/column , and then a string of all cell/columns rebuilt. This is done so that we can add some formatting to each cell/column. In this code we effectively add each cell/column string into a fixed length string variable, so that in the VB editor it looks a bit neater with the cell/columns being of equal with regardless of how many characters are in each cell/column
**Note: As discussed in the last post ( ,asqhfkhhsfhakfhfh ) , we use the code line of this form:
___.insertlines Line:=____, String:=”____”
We actually use a value in the argument Line:=____ which is 1 greater than the last line. It is not possible to “Insert” a line where no line currently is. But for any value 1 or more greater than the last line, the code line appears to add** a new line. That line will then eventually / indirectly become the line number which is 1 greater than the last line number. So we achieve what we want, but are not doing it as directly as it might appear at first glance:- We can only insert lines at up to and including the last current line. If we try to insert anywhere above the last current line, then the ___.insertlines _ will add** a new line directly after the last current line.

_.___________________

Example of code run.
Consider that this spreadsheet range is highlighted,…._
_____ Workbook: Uebersicht Aktuelle.xls ( Using Excel 2007 32 bit )

_Row\^Col
A

B

C

D

E

F

2492
24 11 2018 GH GT 2136.25 30 30.04

2493
25 11 2018 GH GT

2494
26 11 2018 GH GT 2150.17 51 51.1

2495
27 11 2018 GH GT 2146.47 31 31

Worksheet: Tabelle1
_... and then run the code Sub PubProliferous_Let_RngAsString__()
After doing this, you should see this towards the end of the code module in which the code is placed

Code:

'7d) End row VBIDEVBAProj.insertlines Line:=CdTblStp, String:=SpltRws(UBound(SpltRws())) ' Note: this line would not go further than last line, so it must be done here *** End Sub '_-21 12 2018 Worksheets("Tabelle1").Range("$A$2492:$F$2495") '_-24 11 201 | GH | GT | 2136.25 | 30 | 30.04 '_-25 11 201 | GH | GT | | | '_-26 11 201 | GH | GT | 2150.17 | 51 | 51.1 '_-27 11 201 | GH | GT | 2146.47 | 31 | 31 '_-21 12 2018

You will note that you have lost a digit in the column A dates – the 8 in 2018 is missing. This is because the formatting is set to 9 characters by this variable
__TabulatorSyncrenator = "123456789" ' any lengthed string will do
You can overcome that problem by increasing the character length of that variable by 1 digit. Any character will do, for example
__TabulatorSyncrenator = "1234W56789"

Re run the code on the same range and you will now see this:
Why Tabulator SinkCranartor.JPG : https://imgur.com/i6VsFRP
( TabulatorSyncrenator = "12345Y6789" )

Code:

End Sub '_-21 12 2018 Worksheets("Tabelle1").Range("$A$2492:$F$2495") '_-24 11 201 | GH | GT | 2136.25 | 30 | 30.04 '_-25 11 201 | GH | GT | | | '_-26 11 201 | GH | GT | 2150.17 | 51 | 51.1 '_-27 11 201 | GH | GT | 2146.47 | 31 | 31 '_-21 12 2018 '_-21 12 2018 Worksheets("Tabelle1").Range("$A$2492:$F$2495") '_-24 11 2018 | GH | GT | 2136.25 | 30 | 30.04 '_-25 11 2018 | GH | GT | | | '_-26 11 2018 | GH | GT | 2150.17 | 51 | 51.1 '_-27 11 2018 | GH | GT | 2146.47 | 31 | 31 '_-21 12 2018

Codes are here:
Well they will be, you betcha

_._____________________-

In the next posts we consider ways to Get at that data range in a code module, and re paste it into the spreadsheet at the original range
ext posts we consider ways to Get at that data range in a code module, and re paste it into the spreadsheet at the original range
12-21-2018, 09:35 PM
DocAElstein

1 Attachment(s)

<strike> scratch left testie </strike>

<marquee behavior="scroll" direction="left">Scrolling testie...</marquee>

Example
Copy table contents to VBE module Post#4 Re Paste back to spreadsheet
4) Paste range values held in VBE module back to

So the last code, having been run when a range, such as this was selected, …._ _____ Workbook: Uebersicht Aktuelle.xls ( Using Excel 2007 32 bit )

_Row\^Col
G

H

2503
223.38 FH

2504
194.67 FH

Worksheet: Tabelle1_... would result in the '_-commented lines like these appearing at the last rows of the code module in which that code was run

Code:

End Sub '_-23 12 2018 Worksheets("Tabelle1").Range("$G$2503:$H$2504") '_-223.38 | FH '_-194.67 | FH '_- EOF 23 12 2018

In this post a code is presented for re pasting the original data values back into the original spreadsheet range. ( The code also removes the data from the code module )

Code description.
It is a bit difficult to give a detailed walk through description as the code goes around in circles ( it does Do Loops) a bit. So here is just a general description of the full code:
It is convenient to work backwards, deleting the lines in the VB Editor as we go: Unlike in a spreadsheet we always know in a code module that the last line is the last line of data, in other words the pseudo code module sheet has a last cell at the last data entry: We don’t have empty rows. Rows only exist as data is/ was entered
__1A Main outer Loop keeps going While we are not at the End of a routine ===============
_____Immediately within that is another Loop which keeps going While we have not reached the start line of a data section ( A start of a data section will look like this sort of form '_-21 12 2018 Worksheets("Sht_1").Range("$B$15:$D$16") )

It is not obvious from the routine layout, but the main action which is done first at every loop and initially after the routine begins is that which does the reading of the next line back from the end, in other words the current last line
____Let ReedLineIn = VBIDEVBAProj.Lines(StartLine:=VBIDEVBAProj.countof lines, Count:=1)
Immediately after that, the code will end if a routine End _____ type statement was read, or Else the line is deleted. Initially when the code starts we are directed immediately to this lower part of the code by virtue of
____If ReedLineIn = "" Then
'for an empty line we do nothing apart from having already deleted it ( for all but the first time here at the code start)
At this lower routine section, a code line is read, we will quickly leave the routine If we have read an End __ type statement. This is because the Loop While condition to keep looping is no longer met because we set the Boolean variable, EndOFSub , to True:

Code:

Let ReedLineIn = VBIDEVBAProj.Lines(StartLine:=VBIDEVBAProj.countoflines, Count:=1) If ReedLineIn = "End Sub" Or ReedLineIn = "End Function" Then Let EndOFSub = True Else ' after reading in any line, we delete it, unless it was the End of a routine VBIDEVBAProj.DeleteLines StartLine:=VBIDEVBAProj.countoflines, Count:=1 End If Loop While Not EndOFSub = True And FOB = False Loop While EndOFSub = False ' End Sub

If we do not have the ReedLineIn = "" condition then we are at the part where things are done in each inner loop, ----|

The first thing that is done is to see
____If we have got, ( as a result of looping backwards “down” the code module occupied lines), to the point of a start section of a data section which will have a code line something like '_-22 12 2018 Worksheets("Tabelle1").Range("$A$2515") If that is the case Then we are at the ' Section to prepare data for, and to do, the paste out of a data value range
Various manipulation is then done to convert the collected information so far ( in String variable arrOut ) , into a form which will paste out to a spreadsheet range from the clipboard
____The Else situation here is ( If we are not at an end line of the data range ( like '_- EOF 22 12 2018 ) ) is simply to collect all the range text as a continual string, held in variable arrOut
____ We have now arrived at the end of doing things in the inner loop, and the code goes on to reading the next line, then checking for If at an End __ of routine, Else then deleting the line and
____We are now at the point of moving to the next loop of action to analyse and possibly do something with the last copied line held now in string variable ReedLineIn

_.___________________-

4b) Multi range data
The same code will paste out many ranges held in the code module. All ranges will be pasted back to their original spreadsheet range, and be removed from the code module
So for example , with this data in the same code as the routine Sub PubProliferous_Get_Rng__AsString() , will result in the following worksheet ranges being filled, (and also the data from the code module will be removed)
Worksheet "Tabelle1" , Range F2504:I2505
Worksheet "Tabelle2" , Range B392:D394
Worksheet "Globies" , Range F24:G25

Code:

Loop While EndOFSub = False ' ================================================================ End Sub '_-23 12 2018 Worksheets("Tabelle2").Range("$B$392:$D$394") '_-gramms | Kcal | Fett '_-300g | 198Kcal | 0 '_-51g | 183,09K | 4,08 '_- EOF 23 12 2018 '_-23 12 2018 Worksheets("Tabelle1").Range("$F$2504:$I$2505") '_-41.09 | 194.67 | FH | WH '_-55.07 | 233.32 | FH | WH '_- EOF 23 12 2018 '_-23 12 2018 Worksheets("Globies").Range("$F$24:$G$25") '_-GF F | GG G '_-Eiweiß | Koh '_- EOF 23 12 2018

After running the routine, Sub PubProliferous_Get_Rng__AsString() , the end of that code module will then look like this:

Code:

Loop While EndOFSub = False ' ================================================================ End Sub

_.______________

Final routine is here:

In the next post, a slightly different routine will be presented. This routine will
not remove the range value data in the code module
and
only look for data corresponding to a given date.
12-23-2018, 04:46 PM
DocAElstein

Just testing.....

Test

مرحبا كيف حالك

Example
Copy table contents to VBE module Post#4 Re Paste back to spreadsheet
5) Paste range values held in VBE module back to spreadsheet ( without deleting values from code module and selection of data range based on date

Post #5 Sub PubeProFannyTeas__GLetner("____")

This posts differs from that in the previous post in that it searches for a data range held towards the end of a code module, based on a given date.
The date should have a 10 digit “DD MM YYYY” type format.
For example, for the 23rd December, 2018 a calling code would look like this

Code:

Sub TestCall() Call PubeProFannyTeas__GLetner("23 12 2018") End Sub

If the following data…._

Code:

Next Cnt ' next date range End Sub '_-23 12 2018 Worksheets("Tabelle1").Range("$G$2513:$H$2514") '_-91.01 | FH '_-163.73 | FH '_- EOF 23 12 2018 '_-23 12 2018 Worksheets("Tabelle1").Range("$I$2513:$J$2514") '_-WH | MH '_-WH | MH '_- EOF 23 12 2018

_.... were in the code module, ( for example after running code, Sub PubProliferous_Let_RngAsString__() ), then running the above would result in the range…___
WH MH
WH MH
_... being pasted into worksheet with name “Tabelle1” starting top left at cell I2513. In other words the result would be in the worksheet something like:
_____ Workbook: Uebersicht Aktuelle.xls ( Using Excel 2007 32 bit )

_Row\^Col
I

J

2513
WH MH

2514
WH MH

Worksheet: Tabelle1
Notes:
_ Only values are pasted, so the cells in the above example must have been previously in those text color formats
_ For more than one matching date, the range furthest down will be used by the code

Code description: (Sub PubeProFannyTeas__GLetner(ByVal strDte As String) )

Rem 0 Code module as object
As in previous codes, for convenience an object variable is set to the active code window.

Rem 1 complete data range from end of code module
'1a) This section effectively progresses “back down” the code window until an end routine is encountered and thereby counts to determine the number of data rows/lines
'1b) A similar code line is used as previously for reading single lines, but by choosing more than 1 in the second argument a long single string, strIn , is returned with a vbCr & vbLf pair which are pseudo between*** the lines of data. ( This vbCr & vbLf pair seems to be characteristically “ there “ at the next line which is not in use or counted, such that it gets “tacked on” the start of new lines***
A major characteristic of this routine is the manipulation of that single string using various VBA Strings collections functions.
At this point the string might include more than one data range.
'1c) Because we have a “line free” between data ranges held in the code window, this means that the string at these positions will have the usual pair of vbCr & vbLf pair, but because no data is on the line next part of the string will be the next vbCr & vbLf pair. Hence we have characteristically at this position vbCr & vbLf & vbCr & vbLf pair. Using vbCr & vbLf & vbCr & vbLf as separator is therefore a convenient way to split the complete string into an array where each array element is a single string of a complete data range

Rem 2 manipulation of found date range
We start here with a found range ( in a single long string format ) currently in the chosen array of ranges, and for no particular reason put that array element into a simple string variable. (There is no check for no matching date. The code will simple end after all ranges have been looped through.)
'2a) The range information at the start of the string is easy to obtain from simple string manipulation , - in the 'comments example and detail is given )
'2b)
Further manipulation brings the string into the typical for which we find is what Excel gives and takes to paste in a range from the clipboard, For example for a 2 row x 3 column range like this…_
Cell1 Cell2 Cell3
_cell4 cell5 cell6
_

_... has a simple long single string form of
“Cell1 & vbTab & Cell2 & vbTab & Cell3 & vbCr & vbLf & cell4 & vbTab & cell5 & vbTab & cell6 & vbCr & vbLf”

Rem 3 range output to worksheet range
The final prepared range string is given to a “Data object” which had a method which we use to put the string into the clipboard.
A simple Paste is then all that is needed to put the data range of values into the worksheet.

Rem Ref
' http://www.eileenslounge.com/viewtopic.php?f=30&t=31395
' http://www.cpearson.com/excel/vbe.aspx
http://www.excelfox.com/forum/showth...ing-Techniques

2513 WH MH
2514 WH MH
Worksheet: Tabelle1
Notes:
_ Only values are pasted, so the cells in the above example must have been previously in those text color formats
_ For

مرحبا كيف حالك

مرحبا كيف حالك

_Row\^Col	A	B	C	D	E	F
2492	24 11 2018	GH	GT	2136.25	30	30.04
2493	25 11 2018	GH	GT
2494	26 11 2018	GH	GT	2150.17	51	51.1
2495	27 11 2018	GH	GT	2146.47	31	31

_Row\^Col	G	H
2503	223.38	FH
2504	194.67	FH

_Row\^Col	I	J
2513	WH	MH
2514	WH	MH