IF({1},___) Index returning array [Archive] - Excel, Access, PowerPoint and Word VBA Macro Automation Help

DocAElstein

08-17-2014, 01:05 AM

fsfsf

DocAElstein

08-17-2014, 01:05 AM

fsfsf

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg.91d_Pbzklsp9zfGbIr8h gW (https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg.91d_Pbzklsp9zfGbIr8h gW)
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq (https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq)
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zciSZa95 9d (https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zciSZa95 9d)
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zckCo1tv PO (https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zckCo1tv PO)
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg (https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg.9xmkXGSciKJ9xonTti2s Ix (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg.9xmkXGSciKJ9xonTti2s Ix)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg.9xnskBhPnmb9xoq3mGxu _b (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg.9xnskBhPnmb9xoq3mGxu _b)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg.9xm_ufqOILb9xooIlv5P LY (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg.9xm_ufqOILb9xooIlv5P LY)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg.9xmt8i0IsEr9y3FT9Y9F eM (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg.9xmt8i0IsEr9y3FT9Y9F eM)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9xpn-GDkL3o (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9xpn-GDkL3o)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg.9zYoeePv8sZ9zYqog9KZ 5B (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg.9zYoeePv8sZ9zYqog9KZ 5B)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9zYlZPKdO pm (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9zYlZPKdO pm)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

Excel Fox

08-21-2014, 12:01 AM

DocAElstein (http://www.excelfox.com/forum/users/9041/), welcome to the ExcelFox community.

The text to column feature in Excel is used to split text based on a delimiter, like spaces, commas (,) etc. I am almost certain that it doesn't work the other way round.

Now, coming to your specific query about Jill referring to the Text To Column feature to convert the comma separated ball numbers for each day, in to different rows, I assure you that was just one part of the conversion. In the interest of time, and to keep that separate from the overall objective of the podcast, Jill certainly omitted to showcase the entire method he adopted to convert the data to what he was showing in that podcast video.

What he would have done is, after pasting the comma separated data from MegaMillions site, split the data in to separate columns using Text To Column, and then used some formulae or VBA to make all data in to columns. That's my reading, and a lot of other Excel experts would agree. But, with due credit to Jill, I wish and hope that I am wrong :)

Excel Fox

08-21-2014, 12:03 AM

For those interested to hear Jill's podcast which is being referred to in this thread, here's the video.

https://www.youtube.com/watch?v=PF8QpDhBQXQ

DocAElstein

08-21-2014, 12:51 AM

DocAElstein (http://www.excelfox.com/forum/users/9041/), welcome to the ExcelFox community.

The text to column feature in Excel.................

Hi,
Thanks very much for replying. Happy to be here in the Forum!.
. I was mainly interested in a reply from Rick to my specific question on a line in his code as I had asked in #15 from the MrExcel thread ( Multiple Columns into Single Column using Data, Text to Column (http://www.mrexcel.com/forum/excel-questions/797921-multiple-columns-into-single-column-using-data-text-column.html?#post3902054) ), as I had not managed to catch him there.

. But nevertheless Thanks for the interesting infomation. I expect your infomation would have been of particular interest to the initiator of that Thread. (I could possibly referrence him here, but I am not too sure about the Forum Rules about that sort of thing? Maybe you can advise me on that one?)

. I had simply replied in that MrExcel Thread in parralel with Rick to that thread and had asked him for some clarification of one line in his code. Unfortunately it is difficult to get in touch with him.
. Thanks again.
. Hope I can be active as well in the future in this forum!

Alan Elston
Bavaria,
Germany

DocAElstein

08-21-2014, 12:51 AM

DocAElstein (http://www.excelfox.com/forum/users/9041/), welcome to the ExcelFox community.

The text to column feature in Excel.................

Hi,
Thanks very much for replying. Happy to be here in the Forum!.
. I was mainly interested in a reply from Rick to my specific question on a line in his code as I had asked in #15 from the MrExcel thread ( Multiple Columns into Single Column using Data, Text to Column (http://www.mrexcel.com/forum/excel-questions/797921-multiple-columns-into-single-column-using-data-text-column.html?#post3902054) ), as I had not managed to catch him there.

. But nevertheless Thanks for the interesting infomation. I expect your infomation would have been of particular interest to the initiator of that Thread. (I could possibly referrence him here, but I am not too sure about the Forum Rules about that sort of thing? Maybe you can advise me on that one?)

. I had simply replied in that MrExcel Thread in parralel with Rick to that thread and had asked him for some clarification of one line in his code. Unfortunately it is difficult to get in touch with him.
. Thanks again.
. Hope I can be active as well in the future in this forum!

Alan Elston
Bavaria,
Germany

Excel Fox

08-21-2014, 01:20 AM

OK. The conditional evaluation returns an array of values, whereas a direct non-conditional evaluation would return a single value 'in some cases'. There could be a valid theory behind that, but that is not something I am privy to. It's probably the way EVALUATE function works. So anyway, to understand that difference a bit more clearly, you can test the following to codes. Both are actually trying to do the same time, but since the evaluate function 'in some cases' only returns a single value in the second code, the entire output in A1:A10 becomes the same. Having said that, in this case below, both the codes seem to be working fine in some systems, and not in some other systems. I will get back to you with a suitable example.

Range("A1:A10") = Evaluate("IF(1," & Range("A1:z10").Columns(2).Address & "&"" - ""&" & Range("A1:z10").Columns(3).Address & "&"" - ""&" & Range("A1:z10").Columns(4).Address & ")")

Range("A1:A10") = Evaluate(Range("A1:z10").Columns(2).Address & "&"" - ""&" & Range("A1:z10").Columns(3).Address & "&"" - ""&" & Range("A1:z10").Columns(4).Address)

And by the way, you don't necessarily need to use ROW()

Rick just used that as a means to move the IF function's execution to the TRUE condition. Now, you would know that anything except 0 (zero) means TRUE in the Excel boolean world. And since the minimum value of ROW() is greater than 0 (yes, it's 1 and above), using ROW(), or using 1, has the same effect in the IF condition.

Hope this makes it clear.

EDIT: Will get back with more clarity.

DocAElstein

08-21-2014, 01:42 AM

Thanks very much. I'll try that all out and keep watching this space!

DocAElstein

08-21-2014, 01:42 AM

Thanks very much. I'll try that all out and keep watching this space!

Excel Fox

08-21-2014, 01:48 AM

Here's another sample by Vishesh. There also he is using a similar logic. But when I tested it on my machine, the code works fine even if I remove the IF condition.

Evaluate (VBA) for Concatenation | ExcelExperts.com (http://excelexperts.com/evaluate-vba-concatenation)

Let me investigate that!

Excel Fox

08-21-2014, 02:08 AM

OK, here's another thread that confirms my claim. Phew!

VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan (http://usefulgyaan.wordpress.com/2013/06/19/avoid-loop-for-range-calculations-evaluate/)

Specifically, read the below excerpt

Suppose we’ve some text values in range A1:A10 and we want to extract and keep only the first three letters of the values in all cells of this range. We could try to do so using the below code:
Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10")
rngData = Evaluate("Left(" & rngData.Address & ",3)") BUT, you’ll find it does not work. It will fill the whole range with first 3 letters of cell A1. The reason is that if the Excel function used in Evaluate does not accept an array, the Evaluate function will not return an array. So in order to make this function return an array we need to modify the code slightly like this:

Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")
rngData = Evaluate("if(Row(1:10),left(" & rngData.Address & ",3))") In this case, ROW(1:10) returns an array of numbers from 1 to 10. Any numeric value other than 0 returned from a logical function is considered as TRUE, So there are 10 vertical TRUE values. For each TRUE, it will return the corresponding cell’s value from A1:A10.

And the reason why it was working in the previous examples was probably because we weren't using any other function within the EVALUATE function. Hopefully, this makes more sense.

Excel Fox

08-21-2014, 02:12 AM

By the way, you can reference this thread to the OP at the other forum, as long as you feel it is relevant, and is not intended to manipulate traffic. Hope that's as straight as it gets.

DocAElstein

08-21-2014, 03:15 AM

Wow, great, Thanks for all that info. - I'll work me way throught it now (or maybe tomorrow night in my daily "VBA Hour" - It is nearly bed-time now here in Bavaria)

Thanks again
Alan

.P.s....
By the way, you can reference this thread to the OP at the other forum, as long as you feel it is relevant, and is not intended to manipulate traffic. Hope that's as straight as it gets....OK....(should be OK - Rick referrences this Forum in all his replies!)

DocAElstein

08-21-2014, 03:15 AM

Wow, great, Thanks for all that info. - I'll work me way throught it now (or maybe tomorrow night in my daily "VBA Hour" - It is nearly bed-time now here in Bavaria)

Thanks again
Alan

.P.s....
By the way, you can reference this thread to the OP at the other forum, as long as you feel it is relevant, and is not intended to manipulate traffic. Hope that's as straight as it gets....OK....(should be OK - Rick referrences this Forum in all his replies!)

DocAElstein

08-22-2014, 02:36 AM

On sobering up, this last solution from Mr xladept, looks like a nice solution to look at to get a bit of knowledge about what this “AutoFilter” is about.
So for the benefit of any novices catching this Thread , here is my “take” on what the “AutoFilter” is about, and in particular what we can learn from the last routines..

I haven’t used stuff like “AutoFilter” much myself: It seems to be one of those things that profis, use, but which can be a bit daunting to the novice VBA user..

Here is the last routine in a slightly SAlanitised form.

Sub NormanXLFoxAfterASecondThink_2() ' xladept http://www.excelfox.com/forum/showthread.php/2293-Move-values-in-rows-at-the-end-of-the-preceding-row?p=10878#post10878
Rem 0
Dim wa As Worksheet, wb As Worksheet: Set wb = Sheets("Before"): Set wa = Sheets("AfterASecondThink")
wb.Activate ' We are working on wb, which means the the Range stuff beow should probably better be wb.Range , as it they arent, then it might be best to activate that worksheet because usually an unqualifed Range call will go to the active worksheet
Rem 1 "The Filter thing" Part 1
wb.UsedRange.AutoFilter Field:=1, Criteria1:="=2018*"
Rem 2 Loop all rows, "Hidden" and "Visible"
Dim r As Long
For r = 2 To Range("A" & Rows.Count).End(xlUp).Row + 1 ' For this code we must +1 to be sure to catch any last rouge number, beacus looking at this point, VBA will go back up to the last now "visible" row. This is just how VBA is wired to work. VBA uses and sees both "visible" and "invisible" rows, but .End(xlUp) is the VBA equivalent to keys Ctrl+Up which takes us to the last "visible" cell in a row
If Rows(r).RowHeight = 0 And Rows(r - 1).RowHeight <> 0 And IsNumeric(Left(Range("A" & r), 1)) Then
Range("A" & r - 1) = Range("A" & r - 1) & "," & Range("A" & r)
Else
End If
Next r
Rem 3 "The Filter thing" Part 2
wb.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=wa.Cells(1, 1)
wb.UsedRange.AutoFilter
Rem 4
wa.Activate
wa.Columns("A:A").AutoFit
End Sub

Rem 1 is the main part of the code concerned with “AutoFilter”. ( Typically a code line such as this would form about half of the important code lines related to a use of “AutoFilter” )
The main “AutoFilter” bit in this code line is
______.AutoFilter Field:=1, Criteria:="=2018*"
( There are more optional arguments available, but I don’t understand them all yet ( https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofilter ) )
This “AutoFilter” thing is a Method of a Range. In this example it is applied to UsedRange. UsedRange is a bit of a dodgy thing to use, as it often does not give you what you expect. In this example we will get the range A1 : A40 if we are .
Field:=__ is column of the range that we are interests in. In our case we only have 1, but if we had a couple of columns then we could use 1 or 2 etc. This determines where VBA is going to look for the criteria you give it in the next argument.
Criteria:=__ is what is looked for. This argument will take “wild cards” , which in this example means basically that VBA will look in the first column of our range for a text which starts with "=2018". The * is taken as meaning anything.

Take look at the code….
( Hit Alt+F11 from the spreadsheet and look for it in a code module,
or
_1 Hit Alt+F8 to get the routine dialogue window )
_2 Select the routine
_3 Select to edit or work on the code
Alt8 SelectRoutine EditCode .JPG : https://imgur.com/aRHLZbC
)

Click anywhere in the routine.
Run it in F8 debug mode and stop close after wb.UsedRange.AutoFilter Field:=1, Criteria:="=2018*" ,
or
Click in the margin close after wb.UsedRange.AutoFilter Field:=1, Criteria:="=2018*" to put a stop in , and run using the play button
Stop Play.JPG : https://imgur.com/9M9uQlU

So you want to get at the point where the routine is paused something like this
Paused.JPG : https://imgur.com/alAqyS4

At this part of the routine progression, if you look at the main data sheet, you will now see that it has changed from the original , ( Worksheets: BEFORE http://www.excelfox.com/forum/showthread.php/2293-Move-values-in-rows-at-the-end-of-the-preceding-row#post10870 ) , and now looks lIke
_____ Workbook: Data Sample.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B

1Configurable Alerts blabla:

112018, 1, 90515, 10024515, G9, SBlabla (HQ), CHE, BLABLA, blabla, 10012098, 12003.5

122018, 1, 90629, 10022334, P3, BLABLA blabla (blablabla), CHE, BLABLA,blabla, 10033609, 13941.72

132018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah,

152018, 1, 90765, 10012123, P4, Ch of Blabla(Blabla of Blabla), CHE, BLA-BLA,Bla Blabla,

172018, 1, 90712, 10022908, P4, Snr BLA Off (Strat BLa, BLA), CHE, BLABLA,Bla BLabla, 10023234,

192018, 1, 90919, 10020984, P2, Ass BLA Balbla, CHE, BLA,Blabla, 10033098, 10486.33

202018, 1, 95706, 10023098, NB, Assc BLA Blabal (LatBLAa), BLA, BLABLABLA,Blabla, 10034318,

292018, 1, 95716, 10018763, NA, Asst BLA Off (Blabla & Multi-BLa), BLA, BLA,Bla, 10097776, 8607.96

302018, 1, 99716, 10026132, G5, Snr BLA Asst (Bla Blabla), BLA, BLABLA,bla BLa, 18767043, 5477.44

312018, 1, 99716, 10016545, G6, Blabla Blabla (BLA), BLA, BLABLABLA,Blabla, 1097029,

41
Worksheet: BEFORE
That screen shot and the next code sections illustrate a lot of what / how the “AutoFilter” is.
When talking in Excel jargon about a spreadsheet looking like that last screenshot we might often say that we have the lines 11, 12, 13 , 15, 17, 19, 20 , 29, 30 , 31 as “visible” in the range A1:A40.
Or we might say the Range A1:A40 has “visible cells” of cells A11, A12, A13 , A15, A17, A19, A20 , A29, A30 , A31. Or we might say something similar, but the word “visible” will likely be used somehow.

Rem 2 This section gives a nice insight as to how the “AutoFilter” works.
( The Looping of this section is not so typically seen in coding using “AutoFilter” ).
Because of the particular requirement of this Thread, we need to check if we had any “rouge numbers”, which seem to have slipped off the line they were likely intended to be on and appear in the next line.
So the question is, having used the “AutoFilter” effectively to do the main filtering out of the lines we did not want, how do we now check the lines above our “visible” lines. … Well, the fact that the row height is checked and the complete routine seems to do what we want, illustrates to us that what “AutoFilter” appears to do: It appears to reduce the height of the rows which we do not want to 0 height. As far as we Humans perceive this, we regard such lines as “invisible”. To VBA they are not invisible. To VBA they are lines similar to any others. One of the VBA range properties of what we regard as “invisible” is a row height of 0. In other words our “invisible” row is a “row of zero height” to VBA.
We might say that the routine has at this point “filtered out” lines other than those with the text bit of “2018,” at the start. VBA has actually reduced the row height to 0 of all but the cells with “2018,” at the start.
The section of coding here in Rem 2 is similar to the looping in my original code in post #7, except that we no longer need to check for the bit of “2018,”. We simply look at lines of zero height in the now filtered range which also have a row above which has not been “filtered out” . In other words those two criteria are satisfied by
____If Rows(r).RowHeight = 0 And Rows(r - 1).RowHeight <> 0
With that condition met, then the “rogue numbers” are tacked on in a similar way as in my routine.
Here we have
Range("A" & r - 1).Value = Range("A" & r - 1).Value & Range("A" & r).Value
Because this routine differs in general from mine, in that it works directly with the spreadsheet, this step is somewhat more obvious than in my routine. ( My routine uses what is called a VBA arrays type coding, which puts all data in an internal array, manipulates that to produce a final output array which is then pasted into the worksheet in one go. Using “AutoFilter” ) is strictly called the using “Range AutoFilter Method” , and as such comes under the general heading of Worksheet functions which are mostly applied to worksheets. My code does more traditional elementary coding and mathematics internally to achieve similar final results )

Rem 3
The lines here would be typical of the second part the coding in a simple usage of “AutoFilter”
Usually we would like to have what is to us “visible” after the filtering, to be in some “normal” form.
Doing a simple .Copy of the range of what we “see” will probably give us copied in the clipboard some form of a total range of discontinuous cells, in our case something like A1,A11:A13,A15,A17,A19:A20,A29:A31. VBA tends in such a case of all the cells being “in line” to actual Hold that as if it was single 11 row range. So copying and pasting will likely get us near what we finally want.
There tends in the practice to be odd things that might catch you out doing that simple copy and paste. One thing to note is that the “AutoFilter” often catches the first row whether it meats the criteria or not. ( This is generally regarded as an option to keep the header row in any filtering work, which is often practically useful ). Sometimes adding a bit in a code line to be sure of copying just our “visible” rows is often a good idea. Taking these things into account, and knowing what you are doing, ( which I don’t too well here ) , will mean that rather than a simple line like
wb.UsedRange.Copy Destination:=wa.Cells(1, 1)
Instead something like this would be used to be sure to get what you want
wb.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=wa.Cells(1, 1)
The .Offset(1, 0) is perhaps easy to understand as this will mean that we will not get the header row copied.
The .SpecialCells(xlCellTypeVisible) in one function is easy to Understand: it will limit us to not going too far down the worksheet with our copying. Copying to our last row would probably often do the same, but there are likely subtle reasons only to gained by experience to make the use of .SpecialCells(xlCellTypeVisible).
The things talked about, in particular in this Rem 3 code section, are the subtleties that can trip up the inexperienced. That is why “AutoFilter” way tends to be the way a profi might do it. So it is probably better left to them to do it. I tend to avoid it for fear of getting it wrong.
( Mr xladept of course, is, in reality, not quite what you might call a “Junior” thing, so he likely by now should know what he is doing …. :-) )

DocAElstein

08-22-2014, 02:36 AM

On sobering up, this last solution from Mr xladept, looks like a nice solution to look at to get a bit of knowledge about what this “AutoFilter” is about.
So for the benefit of any novices catching this Thread , here is my “take” on what the “AutoFilter” is about, and in particular what we can learn from the last routines..

I haven’t used stuff like “AutoFilter” much myself: It seems to be one of those things that profis, use, but which can be a bit daunting to the novice VBA user..

Here is the last routine in a slightly SAlanitised form.

Sub NormanXLFoxAfterASecondThink_2() ' xladept http://www.excelfox.com/forum/showthread.php/2293-Move-values-in-rows-at-the-end-of-the-preceding-row?p=10878#post10878
Rem 0
Dim wa As Worksheet, wb As Worksheet: Set wb = Sheets("Before"): Set wa = Sheets("AfterASecondThink")
wb.Activate ' We are working on wb, which means the the Range stuff beow should probably better be wb.Range , as it they arent, then it might be best to activate that worksheet because usually an unqualifed Range call will go to the active worksheet
Rem 1 "The Filter thing" Part 1
wb.UsedRange.AutoFilter Field:=1, Criteria1:="=2018*"
Rem 2 Loop all rows, "Hidden" and "Visible"
Dim r As Long
For r = 2 To Range("A" & Rows.Count).End(xlUp).Row + 1 ' For this code we must +1 to be sure to catch any last rouge number, beacus looking at this point, VBA will go back up to the last now "visible" row. This is just how VBA is wired to work. VBA uses and sees both "visible" and "invisible" rows, but .End(xlUp) is the VBA equivalent to keys Ctrl+Up which takes us to the last "visible" cell in a row
If Rows(r).RowHeight = 0 And Rows(r - 1).RowHeight <> 0 And IsNumeric(Left(Range("A" & r), 1)) Then
Range("A" & r - 1) = Range("A" & r - 1) & "," & Range("A" & r)
Else
End If
Next r
Rem 3 "The Filter thing" Part 2
wb.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=wa.Cells(1, 1)
wb.UsedRange.AutoFilter
Rem 4
wa.Activate
wa.Columns("A:A").AutoFit
End Sub

Rem 1 is the main part of the code concerned with “AutoFilter”. ( Typically a code line such as this would form about half of the important code lines related to a use of “AutoFilter” )
The main “AutoFilter” bit in this code line is
______.AutoFilter Field:=1, Criteria:="=2018*"
( There are more optional arguments available, but I don’t understand them all yet ( https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofilter ) )
This “AutoFilter” thing is a Method of a Range. In this example it is applied to UsedRange. UsedRange is a bit of a dodgy thing to use, as it often does not give you what you expect. In this example we will get the range A1 : A40 if we are .
Field:=__ is column of the range that we are interests in. In our case we only have 1, but if we had a couple of columns then we could use 1 or 2 etc. This determines where VBA is going to look for the criteria you give it in the next argument.
Criteria:=__ is what is looked for. This argument will take “wild cards” , which in this example means basically that VBA will look in the first column of our range for a text which starts with "=2018". The * is taken as meaning anything.

Take look at the code….
( Hit Alt+F11 from the spreadsheet and look for it in a code module,
or
_1 Hit Alt+F8 to get the routine dialogue window )
_2 Select the routine
_3 Select to edit or work on the code
Alt8 SelectRoutine EditCode .JPG : https://imgur.com/aRHLZbC
)

Click anywhere in the routine.
Run it in F8 debug mode and stop close after wb.UsedRange.AutoFilter Field:=1, Criteria:="=2018*" ,
or
Click in the margin close after wb.UsedRange.AutoFilter Field:=1, Criteria:="=2018*" to put a stop in , and run using the play button
Stop Play.JPG : https://imgur.com/9M9uQlU

So you want to get at the point where the routine is paused something like this
Paused.JPG : https://imgur.com/alAqyS4

At this part of the routine progression, if you look at the main data sheet, you will now see that it has changed from the original , ( Worksheets: BEFORE http://www.excelfox.com/forum/showthread.php/2293-Move-values-in-rows-at-the-end-of-the-preceding-row#post10870 ) , and now looks lIke
_____ Workbook: Data Sample.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B

1Configurable Alerts blabla:

112018, 1, 90515, 10024515, G9, SBlabla (HQ), CHE, BLABLA, blabla, 10012098, 12003.5

122018, 1, 90629, 10022334, P3, BLABLA blabla (blablabla), CHE, BLABLA,blabla, 10033609, 13941.72

132018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah,

152018, 1, 90765, 10012123, P4, Ch of Blabla(Blabla of Blabla), CHE, BLA-BLA,Bla Blabla,

172018, 1, 90712, 10022908, P4, Snr BLA Off (Strat BLa, BLA), CHE, BLABLA,Bla BLabla, 10023234,

192018, 1, 90919, 10020984, P2, Ass BLA Balbla, CHE, BLA,Blabla, 10033098, 10486.33

202018, 1, 95706, 10023098, NB, Assc BLA Blabal (LatBLAa), BLA, BLABLABLA,Blabla, 10034318,

292018, 1, 95716, 10018763, NA, Asst BLA Off (Blabla & Multi-BLa), BLA, BLA,Bla, 10097776, 8607.96

302018, 1, 99716, 10026132, G5, Snr BLA Asst (Bla Blabla), BLA, BLABLA,bla BLa, 18767043, 5477.44

312018, 1, 99716, 10016545, G6, Blabla Blabla (BLA), BLA, BLABLABLA,Blabla, 1097029,

41
Worksheet: BEFORE
That screen shot and the next code sections illustrate a lot of what / how the “AutoFilter” is.
When talking in Excel jargon about a spreadsheet looking like that last screenshot we might often say that we have the lines 11, 12, 13 , 15, 17, 19, 20 , 29, 30 , 31 as “visible” in the range A1:A40.
Or we might say the Range A1:A40 has “visible cells” of cells A11, A12, A13 , A15, A17, A19, A20 , A29, A30 , A31. Or we might say something similar, but the word “visible” will likely be used somehow.

Rem 2 This section gives a nice insight as to how the “AutoFilter” works.
( The Looping of this section is not so typically seen in coding using “AutoFilter” ).
Because of the particular requirement of this Thread, we need to check if we had any “rouge numbers”, which seem to have slipped off the line they were likely intended to be on and appear in the next line.
So the question is, having used the “AutoFilter” effectively to do the main filtering out of the lines we did not want, how do we now check the lines above our “visible” lines. … Well, the fact that the row height is checked and the complete routine seems to do what we want, illustrates to us that what “AutoFilter” appears to do: It appears to reduce the height of the rows which we do not want to 0 height. As far as we Humans perceive this, we regard such lines as “invisible”. To VBA they are not invisible. To VBA they are lines similar to any others. One of the VBA range properties of what we regard as “invisible” is a row height of 0. In other words our “invisible” row is a “row of zero height” to VBA.
We might say that the routine has at this point “filtered out” lines other than those with the text bit of “2018,” at the start. VBA has actually reduced the row height to 0 of all but the cells with “2018,” at the start.
The section of coding here in Rem 2 is similar to the looping in my original code in post #7, except that we no longer need to check for the bit of “2018,”. We simply look at lines of zero height in the now filtered range which also have a row above which has not been “filtered out” . In other words those two criteria are satisfied by
____If Rows(r).RowHeight = 0 And Rows(r - 1).RowHeight <> 0
With that condition met, then the “rogue numbers” are tacked on in a similar way as in my routine.
Here we have
Range("A" & r - 1).Value = Range("A" & r - 1).Value & Range("A" & r).Value
Because this routine differs in general from mine, in that it works directly with the spreadsheet, this step is somewhat more obvious than in my routine. ( My routine uses what is called a VBA arrays type coding, which puts all data in an internal array, manipulates that to produce a final output array which is then pasted into the worksheet in one go. Using “AutoFilter” ) is strictly called the using “Range AutoFilter Method” , and as such comes under the general heading of Worksheet functions which are mostly applied to worksheets. My code does more traditional elementary coding and mathematics internally to achieve similar final results )

Rem 3
The lines here would be typical of the second part the coding in a simple usage of “AutoFilter”
Usually we would like to have what is to us “visible” after the filtering, to be in some “normal” form.
Doing a simple .Copy of the range of what we “see” will probably give us copied in the clipboard some form of a total range of discontinuous cells, in our case something like A1,A11:A13,A15,A17,A19:A20,A29:A31. VBA tends in such a case of all the cells being “in line” to actual Hold that as if it was single 11 row range. So copying and pasting will likely get us near what we finally want.
There tends in the practice to be odd things that might catch you out doing that simple copy and paste. One thing to note is that the “AutoFilter” often catches the first row whether it meats the criteria or not. ( This is generally regarded as an option to keep the header row in any filtering work, which is often practically useful ). Sometimes adding a bit in a code line to be sure of copying just our “visible” rows is often a good idea. Taking these things into account, and knowing what you are doing, ( which I don’t too well here ) , will mean that rather than a simple line like
wb.UsedRange.Copy Destination:=wa.Cells(1, 1)
Instead something like this would be used to be sure to get what you want
wb.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Destination:=wa.Cells(1, 1)
The .Offset(1, 0) is perhaps easy to understand as this will mean that we will not get the header row copied.
The .SpecialCells(xlCellTypeVisible) in one function is easy to Understand: it will limit us to not going too far down the worksheet with our copying. Copying to our last row would probably often do the same, but there are likely subtle reasons only to gained by experience to make the use of .SpecialCells(xlCellTypeVisible).
The things talked about, in particular in this Rem 3 code section, are the subtleties that can trip up the inexperienced. That is why “AutoFilter” way tends to be the way a profi might do it. So it is probably better left to them to do it. I tend to avoid it for fear of getting it wrong.
( Mr xladept of course, is, in reality, not quite what you might call a “Junior” thing, so he likely by now should know what he is doing …. :-) )

DocAElstein

09-20-2014, 03:00 AM

<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Produnt</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Name</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Choc</td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-right: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Choc</td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-right: 1px solid black;;">Banana-Chocolate-Split</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">10</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Bana</td><td style="text-align: center;;">Bana</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-right: 1px solid black;;">Limette-Käsekuchen</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">16</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Lime</td><td style="text-align: center;;">Lime</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Quark</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">8</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Erdb</td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Mix</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Erdb</td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-right: 1px solid black;;">Jamaica Sun</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">6</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Jama</td><td style="text-align: center;;">Jama</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;border-right: 1px solid black;;">Waldbeeren</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Wald</td><td style="text-align: center;;">Wald</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;border-top: 1px solid black;border-left: 1px solid black;;">LOOKUP Table</td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;border-left: 1px solid black;;">Product Name</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;border-left: 1px solid black;;">Haselnuß-Walnuß-aromatisiert</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;border-left: 1px solid black;;">Tiramisu</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">2</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;border-left: 1px solid black;;">Chocolate-colonial blend</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;border-left: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">4</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;border-left: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;border-left: 1px solid black;;">Jamaica Sun</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">6</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;border-left: 1px solid black;;">Himbeere-Joghurt</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;border-left: 1px solid black;;">Erdbeere-Quark</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">8</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;border-left: 1px solid black;;">Erdbeere-Mix</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;border-left: 1px solid black;;">Banana-Chocolate-Split</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">10</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;border-left: 1px solid black;;">Waldbeeren</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;border-left: 1px solid black;;">Kirsche</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">12</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;border-left: 1px solid black;;">Kirsche-grüner Apfel</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;;"></td></tr></tbody></table>Sheet1 <table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" >Worksheet Formulas<table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=VLOOKUP(A3,$A$16:$C$33,3,FALSE)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=VLOOKUP(A4,$A$16:$C$33,3,FALSE)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=VLOOKUP(A5,$A$16:$C$33,3,FALSE)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=VLOOKUP(A6,$A$16:$C$33,3,FALSE)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=VLOOKUP(A7,$A$16:$C$33,3,FALSE)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=VLOOKUP(A8,$A$16:$C$33,3,FALSE)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B9</th><td style="text-align:left">=VLOOKUP(A9,$A$16:$C$33,3,FALSE)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B10</th><td style="text-align:left">=VLOOKUP(A10,$A$16:$C$33,3,FALSE)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=LEFT(A3,4)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=LEFT(A4,4)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=LEFT(A5,4)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=LEFT(A6,4)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D7</th><td style="text-align:left">=LEFT(A7,4)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D8</th><td style="text-align:left">=LEFT(A8,4)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D9</th><td style="text-align:left">=LEFT(A9,4)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D10</th><td style="text-align:left">=LEFT(A10,4)</td></tr></tbody></table></td></tr></table>

DocAElstein

09-20-2014, 03:38 AM

Error and Error Handling VBA Summary
<table cellpadding="1px" rules="all" style=";background-color:#FFFFFF;border:1px solid;border-collapse:collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color:#E0E0F0"/>
<tr>
</tr>
<tr>
<td style="border:solid windowtext 1.0pt;mso-border-alt:solid windowtext .5pt;
padding:0cm 3.5pt 0cm 3.5pt;height:10pt">
Error Handling Code line _______________________
</td>
<td style='border:solid windowtext 1.0pt;border-left:none;mso-border-left-alt:
solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 3.5pt 0cm 3.5pt;
height:10pt'>
Notes</td>
</tr>
<tr>
<td style='border:solid windowtext 1.0pt;border-top:none;mso-border-top-alt:
solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 3.5pt 0cm 3.5pt;
height:39.85pt'>
On Error Resume Next 
</td>
<td style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;
border-right:solid windowtext 1.0pt;mso-border-top-alt:solid windowtext .5pt;
mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;
padding:0cm 3.5pt 0cm 3.5pt;height:39.85pt'>
Makes code always carry on after error line. Clears the exception – So works time and time again, But retains infomation of last error in Err object
</td>
</tr>
<tr>
<td style='border:solid windowtext 1.0pt;border-top:none;mso-border-top-alt:
solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 3.5pt 0cm 3.5pt;
height:34.75pt'>
On Error GoTo Label/Line 
</td>
<td style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;
border-right:solid windowtext 1.0pt;mso-border-top-alt:solid windowtext .5pt;
mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;
padding:0cm 3.5pt 0cm 3.5pt;height:34.75pt'>
Does not clear the exception. Just goes to the indicated Label or Line Number (Typically at that label or line number would be code lines for an error handling routine ) It is Prevented by default ( due to it not clearing the exception ) from working more than once
</td>
</tr>
<tr>
<td style='border:solid windowtext 1.0pt;border-top:none;mso-border-top-alt:
solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 3.5pt 0cm 3.5pt;
height:21.75pt'>
On Error GoTo 0
</td>
<td style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;
border-right:solid windowtext 1.0pt;mso-border-top-alt:solid windowtext .5pt;
mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;
padding:0cm 3.5pt 0cm 3.5pt;height:21.75pt'>
Does not clear the exception Disables any enabled error handler This Clears the Err object
</td>
</tr>
<tr>
<td style='border:solid windowtext 1.0pt;border-top:none;mso-border-top-alt:
solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 3.5pt 0cm 3.5pt;
height:21.35pt'>
On Error GoTo -1
</td>
<td style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;
border-right:solid windowtext 1.0pt;mso-border-top-alt:solid windowtext .5pt;
mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;
padding:0cm 3.5pt 0cm 3.5pt;height:21.35pt'>
Clears the exception (* Deactivates any enabled error handler) Does not disable any enabled error handler This Clears the Err object
</td>
</tr>
<tr>
<td style='border:solid windowtext 1.0pt;border-top:none;mso-border-top-alt:
solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 3.5pt 0cm 3.5pt;
height:29.1pt'>
Resume</td>
<td style="border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;
border-right:solid windowtext 1.0pt;mso-border-top-alt:solid windowtext .5pt;
mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;
padding:0cm 3.5pt 0cm 3.5pt;height:29.1pt">
Clears the exception (* Deactivates any enabled error handler) Does not disable any enabled error handler. Makes code try again at error line. ( Be careful as can lead to an infinite loop of retrying!!) Does not retain infomation of last error:Clears Err object
</td>
</tr>
</tr>
<tr>
<td style='border:solid windowtext 1.0pt;border-top:none;mso-border-top-alt:
solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 3.5pt 0cm 3.5pt;
height:21.35pt'>
Resume Next
</td>
<td style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;
border-right:solid windowtext 1.0pt;mso-border-top-alt:solid windowtext .5pt;
mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;
padding:0cm 3.5pt 0cm 3.5pt;height:21.35pt'>
 As Resume , but resumes after line which errored
</td>
</tr>
<tr>
<td style='border:solid windowtext 1.0pt;border-top:none;mso-border-top-alt:
solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 3.5pt 0cm 3.5pt;
height:21.35pt'>
Resume Label/Line Number 
</td>
<td style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;
border-right:solid windowtext 1.0pt;mso-border-top-alt:solid windowtext .5pt;
mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;
padding:0cm 3.5pt 0cm 3.5pt;height:21.35pt'>
 As Resume , but resumes at Label/Line number
</td>
</tr>
</table>
 * Deactivated means: "The trap is reset: but not currently working - It is "primed" ". It is enabled, but not activated.
 Err : An object 6 Properties containing infomation about last error and 2 Methods, .Raise and .Clear 
 Erl : A Function returning line number of last error or 0 if no line number is present at erroring code line. 
 vbObjectError : Probably broken or no one remembers what it does - A plie of wank - forget about it!

DocAElstein

09-20-2014, 03:41 AM

Error and Error Handling VBA Summary
<table cellpadding="1px" rules="all" style=";background-color:#FFFFFF;border:1px solid;border-collapse:collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color:#E0E0F0"/>

<tr>
<td style="border:solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt;height:10pt">
Error Handling Code line ORNeRe_GoRoT_N0Nula_1_____
</td>
<td style='border:solid windowtext 1.0pt;border-left:none;padding:0cm 3.5pt 0cm 3.5pt;height:10pt'>
Notes
</td>
</tr>

<tr>
<td style="font-family:'courier new';font-size: 8pt;color:#0070C0;border:solid windowtext 1.0pt;border-top:none;padding:0cm 3.5pt 0cm 3.5pt;height:39.85pt">
On Error Resume Next
</td>
<td style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt;height:39.85pt'>
Makes code always carry on after error line. Clears the exception – So works time and time again, But retains infomation of last error in Err object
</td>
</tr>

<tr>
<td style="font-family:'courier new';font-size: 8pt;color:#0070C0;border:solid windowtext 1.0pt;border-top:none;height:34.75pt">
On Error GoTo Label/Line 
</td>
<td style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt;height:34.75pt'>
Does not clear the exception. Just goes to the indicated Label or Line Number (Typically at that label or line number would be code lines for an error handling routine ) It is Prevented by default ( due to it not clearing the exception ) from working more than once
</td>
</tr>

<tr>
<td style="font-family:'courier new';font-size: 8pt;color:#0070C0;border:solid windowtext 1.0pt;border-top:none;padding:0cm 3.5pt 0cm 3.5pt;height:21.75pt">
On Error GoTo 0
</td>
<td style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;mso-border-top-alt:solid windowtext .5pt;padding:0cm 3.5pt 0cm 3.5pt;height:21.75pt'>
Does not clear the exception Disables any enabled error handler This Clears the Err object
</td>
</tr>

<tr>
<td style="font-family:'courier new';font-size: 8pt;color:#0070C0;border:solid windowtext 1.0pt;border-top:none;padding:0cm 3.5pt 0cm 3.5pt;height:21.35pt">
On Error GoTo -1
</td>
<td style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt;height:21.35pt'>
Clears the exception (* Deactivates any enabled error handler) Does not disable any enabled error handler This Clears the Err object
</td>
</tr>

<tr>
<td style="font-family:'courier new';font-size: 8pt;color:#0070C0;border:solid windowtext 1.0pt;border-top:none;padding:0cm 3.5pt 0cm 3.5pt;height:29.1pt">
Resume
</td>
<td style="border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt;height:29.1pt">
Clears the exception (* Deactivates any enabled error handler)    Does not disable any enabled error handler. Makes code try again at error line. ( Be careful as can lead to an infinite loop of retrying!!) Does not retain infomation of last error:Clears Err object
</td>
</tr>
</tr>

<tr>
<td style="font-family:'courier new';font-size: 8pt;color:#0070C0;border:solid windowtext 1.0pt;border-top:none;height:21.35pt">
Resume Next
</td>
<td style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt;height:21.35pt'>
 As Resume , but resumes after line which errored
</td>
</tr>

<tr>
<td style="font-family:'courier new';font-size: 8pt;color:#0070C0;border:solid windowtext 1.0pt;border-top:none;mso-border-top-alt:solid windowtext .5pt;padding:0cm 3.5pt 0cm 3.5pt;height:21.35pt">
Resume Label/Line Number 
</td>
<td style='border-top:none;border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt;height:21.35pt'>
 As Resume , but resumes at Label/Line number
</td>
</tr>
</table>
 * Deactivated means: "The trap is reset: but not currently working - It is "primed" ". It is enabled, but not activated.
 Err : An object 6 Properties containing infomation about last error and 2 Methods, .Raise and .Clear 
 Erl : A Function returning line number of last error or 0 if no line number is present at erroring code line. 
 vbObjectError : Probably broken or no one remembers what it does - A plie of wank - forget about it!