Appendix Thread. App Index Rws() Clms() Majic code line Codings for other Threads, Tables etc.)

**DocAElstein** · 09-16-2015, 03:25 PM

For more recent posts on the App Index Rws() Clms() idea, see from approximately page 50
https://www.excelfox.com/forum/showt...s-etc-)/page50
, and the best, most recent will be from about page 53
https://www.excelfox.com/forum/showt...s-etc-)/page53

Other posts before this may be subject to change and testing experiments

This thread will become the main Blog thread for posts on the App Index Rws() Clms() idea

**DocAElstein** · 06-08-2016, 02:24 PM

Function Code for getting Column Letter from Column Number
Shortened version used in Post #14
http://www.excelfox.com/forum/showth...=9837#post9837
Public Function CL(ByVal lclm As Long) As String

And Fuller version with explaining ‘Comments

Code:

Public Function CL(ByVal lclm As Long) As String '         http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

Function FukOutChrWithDoWhile(ByVal lclm As Long) As String 'Using chr function and Do while loop      For example http://www.excelforum.com/excel-programming-vba-macros/796472-how-to-go-from-column-number-to-column-letter.html
Dim rest As Long 'Variable for what is "left over" after subtracting as many full 26's as possible
    Do
    '    Let rest = ((lclm - 1) Mod 26) 'Gives 0 to 25 for Column Number "Left over" 1 to 26. Better than ( lclm Mod 26 ) which gives 1 to 25 for clm 1 to 25 then 0 for 26
    '    Let FukOutChrWithDoWhile = Chr(65 + rest) & FukOutChrWithDoWhile 'Convert rest to Chr Number, initially with full number so the "units" (0-25), then number of 26's left over (if the number was so big to give any amount of 26's in it, then number of 26's in the 26's left over (if the number was so big to give any amount of 26 x 26's in it, Enit ?
    '    'OR
    Let FukOutChrWithDoWhile = Chr(65 + (((lclm - 1) Mod 26))) & FukOutChrWithDoWhile
    Let lclm = (lclm - (1)) \ 26 'This gives the number of 26's ( if any ), but just the excact part, in the next number down , - so applying the rest formula to this new number will again leave a difference "left over" rest.
    'lclm = (lclm - (rest + 1)) \ 26 ' As the number is effectively truncated here, any number from 1 to (rest +1)  will do in the formula
    Loop While lclm > 0 'Only loop further if number was big enough to still have 0-25's in it
End Function
Rem Ref    http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Rem Ref    http://www.excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html

**DocAElstein** · 06-08-2016, 11:12 PM

LOOKUP function and finding the last filled cell in a column

This is probably not worthy of a Tips, tricks or tutorials post, as it is a simple Excel standard function and there are almost certainly a lot more better Blogs, Videos etc. on it. I am also not initially looking at it extensively
But I needed it and a specific typical use of it, and it was not known to me, and so I made my own notes, and excelfox.com is a good place to access my stuff, and also the use of it was in ( another more substantial Blog posting, ) , where I came up with some similar formulas with VLOOKUP in a frenzy on Panzerschokolade, so can’t remember how I got it…..
So all in all it seemed OK to drop it in here, as I may need it to refer to later, possibly when others catch up with me, when/have I stop/ stopped taking those drugs.

( I actually stated using any of the Excel Loolup things , with VLOOKUP and had missed noticing this more fundamental LOOKUP. In the meantime there is the newer XLOOKUP, or some such, which like any new function excites the Microsoft loyal Excel addicts like excited children with a new toy.
But I am keen to encourage using just the basic fundamental functions as much as possible, ( and then thereafter, rather moving on to VBA / user defined functions for more complex things, so that gives me yet another reason for me posting it here. )

This first post will not be an extensive explanation of LOOKUP. It will just be enough to get me as far as moving on in the next post to explain a typically used formula / function version used to find the last filled cell in a column in a spreadsheet

Simplest 2 argument LOOKUP
For its simplest spreadsheet use we need a single row or column of numerical values, and for a simple explanation of its behaviour , this should be ascending in value, so something like this
1
3
5
6
8
If I apply the simplest version of LOOKUP to that example, and say I am looking in that column for a number of 4.9, then the function as written might look something like this, (assuming in this example I have the number 1 in cell E15, and am using the range E15:E19 for all of the 5 numbers )
=LOOKUP(4.9,E15:E19)
The final solution forms I am envisaging, are likely to involve array type things, so therefore as I proceed from here further, I will try to always show an array alternative, such as this alternative, using hard coded numbers rather than a spreadsheet range, as it will work on an array instead.
=LOOKUP(4.9,{1;3;5;6;8})
( , most spreadsheet formulas will work in some way or another with arrays, at least almost always in a spreadsheet.
Since Excel tends to update the whole spreadsheet, or at least a large part of it, it is often efficient to take advantage of this, which is what the array calculation concept in Excel is based on )

This would be how the test range and the LOOKUP formulas looks in a spreadsheet

_Row\^Col	E	F
15	1	=LOOKUP(4.9,E15:E19)
16	3	=LOOKUP(4.9,{1;3;5;6;8})
17	5
18	6
19	8

In both cases the answer you would see would be 3, like this:

_Row\^Col	E	F
15	1	3
16	3	3
17	5
18	6
19	8

Perhaps it is not obvious why we get 3 when looking for 4.9 ?

To Explain:
What seems to be happening is that it assumes the numbers go up in value as you go along/down ( ascending ), (as we have in this example), and it will either give back the value if it finds it, or otherwise it gives you the next value down.
So it is looking for the nearest match, … err .. or .. ? - .. well not quite… it is looking for the value, (and if it finds it , then it will return that number), but if it does not find it, it gives you the nearest value next one down.
Another way of looking at this is to say we get a value out for a certain range of numbers we might give in the first argument, and this returned number is the lowest number in that certain range, like A to C goes in tray A, D to F goes in try D, F to W goes in tray F etc…
In the example above, something approximately as in this sketch form
Spread of numbers
We might give
In the first argument ----- value returned by VLOOKUP ( tray identificater )
___1 - 2.999 --------------- 1
___3 – 4.999 --------------- 3
___5 – 5.9999 -------------- 5
___6 - 7.999 --------------- 6
( If you give in this example anything above 8 in the first argument in the LOOKUP function , then the LOOKUP function will return 8 )
____> 8 …….. --------------- 8

3 argument LOOKUP
In the previous example the "nearest", (nearest lowest), value in the second argument column range, that nearest to the first argument value you gave, was the output. (Or indeed the exact value you gave , if that value was in the list).
By adding a new third argument as a similar sized range, the output will then be the value at the corresponding row/position along in that new third argument range. This new extra range can be a range of numbers or text or both.

So the first column range (second argument) is used to find the "nearest" row/position along that the number you give as first argument "belongs" to , (In other words the number range the number you give as first argument is in, as per the sketch above), but the output now LOOKUP gives is the value in the corresponding row in the second range column, (third argument).
So effectively you can determine what output is given for a range of numbers given in the first argument.

For example it can be used to give an output to tell you in which number range your number was. Extending/ following on from the last example…..

_Row\^Col	E	F	G	H
15	1	3	\|1--3\|	\|3--5\|
16	3	3	\|3--5\|	\|3--5\|
17	5		\|5--6\|
18	6		\|6--8\|
19	8		\| < 8 \|

_Row\^Col	E	F	G	H
15	1	=LOOKUP(4.9,E15:E19)	\|1--3\|	=LOOKUP(4.9,E15:E19,G15:G19)
16	3	=LOOKUP(4.9,{1;3;5;6;8})	\|3--5\|	=LOOKUP(4.9,{1;3;5;6;8},{"\|1--3\|";"\|3--5\|";"\|5--6\|";"\|6--8\|";"\| < 8 \|"})
17	5		\|5--6\|
18	6		\|6--8\|
19	8		\| < 8 \|

I will leave the explanation of the function at this stage, possibly me or someone may come back in further posts after the next ones, later, or probably much later, if at all..
For now I want to concentrate on a particular example.

**DocAElstein** · 06-08-2016, 11:42 PM

Using LOOKUP to get the last value

For the particular formula that I am trying to get to and understand, ( one of this sort of form =LOOKUP(2;1/(C:C<>"");C:C) ) ,I need to look a bit further into the behaviour of LOOKUP, in particular the second argument.

A few observations:
_observation
VLOOKUP seems to assume that the list/ range of numbers is in ascending order
For example, this sort of thing,
1
3
4
2

_Row\^Col	E
37	1
38	3
39	4
40	2
41
42

, with the LOOKUP formula of these sort of things
=LOOKUP(5;E37:E40)
=LOOKUP(5;{1;3;4;2})
, will return 2. In fact it always seems to return the last value. This initially points us into perhaps a direction of thinking to just look for any number likely to be bigger than any in the list of numbers (in the second argument range)

_observation
Extend the range a bit , to example include some empty cells,
=LOOKUP(5;E37:E43)
, and once again the answer is 2.
This is very interesting and helpful. It seems not to error when not getting numbers, (as I was thinking that the second argument would be always expecting numbers). The empty cells simply seem to be ignored

But at this point some alarm bells are ringing in my head. The final solution forms I am envisaging, are likely to involve array type things, so I want to continue as I have done so far, to consider the array alternative, and I am getting worried now – because…
depending on your viewpoint, an empty cell can be regarded and one if at least 3 things I know about
___ Empty
___ 0
___ a zero length string, often represented by ""

Well, I can’t be too sure about how to distinguish all 3 in a formula, but I can do a partial job by considering these two array formula versions
=LOOKUP(5;{3;4;2;0;0;0}) ' returns 0
=LOOKUP(5;{3;4;2;"";"";""}) ' returns 2

The difference in those two results is enough for me to ponder about for a while. But I don’t need tom ponder too long.
It makes sense where we get 0, since 0 is a number, and in line with what we have already said
_____ VLOOKUP takes the very last number in a list if it never finds either the number it is looking for , or if it never finds a larger number at any other position in a list, (in this latter case it would take the number before that bigger number)
For the purposes of the array type formula, "" , can be regarded, (with some caution perhaps), of behaving like our spreadsheet empty cell. We will come back to this last point, but let’s give a pictorial summary so far of where we are

_Row\^Col	E	F
37	1	=LOOKUP(5,E37:E40)
38	3	=LOOKUP(5,{1;3;4;2})
39	4	=LOOKUP(5,E37:E43)
40	2	=LOOKUP(5,{3;4;2;0;0;0})
41		=LOOKUP(5,{3;4;2;"";"";""})
42
43

_Row\^Col	E	F
37	1	2
38	3	2
39	4	2
40	2	0
41		2
42
43

In Excel functions and formulas generally, the difference between using a number and a text can be significant. The fact that the array version both accepts ( does not error with ) the "" , and gives us perhaps a useful answer , the last number, could be useful….

Also it may be worth checking what happens if the text is not zero length.
Here we go, lets try filling the empty cells with some text:

_Row\^Col	E	F
37	1	=LOOKUP(5,E37:E41)
38	3	=LOOKUP(5,{1;3;4;2})
39	4	=LOOKUP(5,E37:E43)
40	2	=LOOKUP(5,{1;3;4;2;0;0;0})
41	a	=LOOKUP(5,{1;3;4;2;"a";"b";"c"})
42	b
43	c

_Row\^Col	E	F
37	1	2
38	3	2
39	4	2
40	2	0
41	a	2
42	b
43	c

Hmm…. So it seems to treat an actual text just like the zero length text

In the next post we will develop/ explain a solution

**DocAElstein** · 06-26-2016, 10:27 PM

Using LOOKUP to get the last value
A solution
The development of an answer is a slightly back and forth chicken or egg thing: The initial investigations and observations from the last post have helped, but as the final solution discussed here was developed, those explanations were modified a bit to make the final full set of notes a bit clearer.

The last few post have suggested that the LOOKUP looking for a number bigger than it might ever find, is the main thing that is needed, Any numbers, preferably not too big in the column are OK
In the discussion so far we may have missed or overlooked something: So far we have considered numbers in a column, but of course for a general solution there could be any combination of text and numbers.
The solution that I am suggesting here, will use the LOOKUP in its 3 argument form, but unconventionally both the second argument and the third argument, which typically would be two similar size columns, will, in this solution, be the same column range. The third argument column range will be the simple range, unaltered, since as per the working of the LOOKUP, we will get the value from that, which we want, (the last value that is, hopefully, finally)
The key to the solution is unconventionally using that same column range for the second argument column range, but not in its original form, but somehow altered so as to
_ show numbers, or at the very least a number in the last used cell
_ above the last cell something such as text, that will be ignored by LOOKUP

So:
_ we know the first argument. It will be a number, perhaps a big one
_ we know the third/ last argument. It is simply the actual column range that we are trying to find the last value of

_ We are now concentrating on the second argument. We need to use in it the third/ last argument, modified in some way to
_ show numbers, or at the very least a number in the last used cell
_ above the last cell something such as text, that will be ignored by LOOKUP

Here we go:
Some general ideas and thoughts
We can change a value into a False or True thing, which can then either be changed to a 0 or 1, or may in some situations conveniently it may get changed automatically in some mathematical operation that will take False as 0 and take True as 1
(All that is just to do with how Excel, and to some extent computers in general, does certain things.

The solution
Assume we are looking for the last value in column C

Consider the result from this sort of thing if it is in an Excel formula or Excel function
C:C <> ""
(for clarity, I will just look here at a 11 entries in the column, and assume we are using just the first few cells in that C column )
FALSE
FALSE
TRUE
TRUE
TRUE
FALSE
TRUE
TRUE
FALSE
FALSE
FALSE
Those results would come from if I had a last value in about the 8th row, after that row the logic of C:C <> "" is always false, since I have after that always an empty cell, which in simple logic may come out, as it does here, to be ""
**It can be a bit hit and miss with these things. As discussed in previous posts, an empty cell might be taken as 0 or "", but test suggest we are seeing "" here

Applying a logic of x 1 ( or using in some mathematical Excel thing that would itself convert True or false things to a 1 or 0), ( 1 x (C:C<>"") ) , would / could change that to
0
0
1
1
1
0
1
1
0
0
0
That is looking close to what we are looking for, but as we have seen, a 0 is a number, so our LOOKUP will end up returning us the value in the last cell in the range, which is 0. So whether we use some fixed range or the entire column C, the last few ( or thousands) of unused cells will contain a 0, and we will be returned the very last one. In other words we will get 0, and specifically Excel is giving us the value 0 from the very last cell (object***)in the range. That is not what we want. We want Excel to give us specifically the last filled cell.
( It might not do too much harm to get slightly technical at this point for later clarity*** Excel is indeed returning a specific cell range object. In the spreadsheet this concept is hard to show and in our case the LOOKUP will show us the value in it .)

But how about this: Rather than multiplying the range by 1, try dividing 1 by the range, thus 1/(C2:C12<>"")
The results from that, will look something of this form
#DIV/0!
#DIV/0!
#VALUE!
1
1
#DIV/0!
1
1
#DIV/0!
#DIV/0!
#DIV/0!
Technically speaking, where we do not have a 1, we have a specific Excel concept which is an Error type. In some things, such as VBA coding, that concept can be recognised. However, in a spreadsheet, Microsoft have organised that a text is given, specifically a text that gives us some indication of the error.
( In this case the #DIV/0! is probably telling us we had an empty cell, which was however in this case interpreted as a 0, so we have an error caused by 1/0 which pretty well errors in most things and systems and situations. The #VALUE! seems to come from text in a cell, and for some reason the TRUE was not interpreted as 1. However further testing suggests that in the final formula it was interpreted as 1, meaning that we can say that in the final analysis effectively it was at this stage more like this
#DIV/0!
#DIV/0!
1
1
1
#DIV/0!
1
1
#DIV/0!
#DIV/0!
#DIV/0!
**It is a bit hit and miss sometimes with these things, but at the end of the day here, we seem to have as if a 1 in any filled cell and the text of #DIV/0! for any empty cell.

All we need now is to use any number greater than 1 in the first argument, and then the LOOKUP will return us 1. But although this to us is indistinguishable form any of the 1s, Excel is actually giving us the last 1, (*** technically in its innards a cell range object is returned, but a spreadsheet can that concept not show so well, and we are simply shown the contents of that cell object ) and so if the actual unmodified C range is given in the third argument, then the actual last value in the range will be returned.

That’s it!

____________=LOOKUP(1.1,1/(C:C<>""),C:C)

**DocAElstein** · 10-23-2016, 02:19 PM

Referrences in suppost of this post:
http://www.excelfox.com/forum/showth...=9985#post9985

and solution to this post
http://www.excelforum.com/excel-prog...ml#post4507157

' http://www.snb-vba.eu/VBA_Arraylist_en.html
' http://www.snb-vba.eu/VBA_Arraylist_en.html#L_11.3

' https://usefulgyaan.wordpress.com/20...1/#comment-587

' https://usefulgyaan.wordpress.com/20...1/#comment-515

Code:

'   https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/comment-page-1/#comment-587
Sub M_snbSortof()  '   http://www.snb-vba.eu/VBA_Arraylist_en.html#L_11.3
Dim rngVoll As Range: Set rngVoll = Tabelle3.Range("A1:E10")
Dim snAll() As Variant, Sported() As Variant
 Let snAll() = rngVoll.Value
Dim j As Long, jj As Long
    With CreateObject("System.Collections.Arraylist")
        For j = 1 To UBound(snAll(), 1)
         .Add snAll(j, 3)
        Next
     .Sort
     Let Sported() = .ToArray
     .Clear
        For j = 0 To UBound(Sported())
            For jj = 1 To UBound(snAll(), 1)
                If snAll(jj, 3) = Sported(j) Then
                ' Use Range to overcome  Array size Limits of Worksheets Functions
                'Dim Clm As Range: Set Clm = Application.Index(rngVoll, jj, 0)
                ' .Add Clm.Value
                ' .Add (Application.Index(rngVoll, jj, 0).Value)
                
                ' Use Cells to overcome  Array size Limits of Worksheets Functions
                Dim LB As Long, UB As Long '…User Given start and Stop Column as a Number
                 Let LB = LBound(snAll(), 2): Let UB = UBound(snAll(), 2)
                Dim strLtrLB As String, strLtrUB As String '…Column Letter corresponding to Column Number
                'There are many ways to get a Column Letter from a Column Number – excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html
                 Let strLtrLB = Split(Cells(1, LB).Address, "$")(1) 'An Address Method
                 Let strLtrUB = Replace (Replace(Cells(1, UB).Address, "1", ""), "$", "") 'A Replace Method
                'Obtain Column Indicies using Spreadsheet Function Column via VBA Evaluate Method
                Dim clms() As Variant
                 Let clms() = Evaluate("column(" & strLtrLB & ":" & strLtrUB & ")") 'Returns 1 D “pseudo” Horizontal Array of sequential numbers from column number of LB to UB
                'Or
                 clms() = Evaluate("column(" & Split(Cells(1, LB).Address, "$")(1) & ":" & Replace (Replace(Cells(1, UB).Address, "1", ""), "$", "") & ")")
                .Add (Application.Index(Tabelle3.Cells, jj, clms()))
                 
                 'Let snAll(jj, 3) = ""
                Exit For
                End If
            Next jj
        Next j
        For j = 0 To .Count - 1
         Tabelle3.Cells(j + 1 + 10, 1).Resize(, UBound(snAll, 2)) = .Item(j)
        Next j
    End With
End Sub
'
Sub M_snb()
Dim sn, sp, j As Long, jj As Long
sn = Tabelle3.Range("A1:E10")
    With CreateObject("System.Collections.Arraylist")
        For j = 1 To UBound(sn)
        .Add sn(j, 3)
        Next
     .Sort
     sp = .ToArray
     .Clear
        For j = 0 To UBound(sp)
            For jj = 1 To UBound(sn)
                If sn(jj, 3) = sp(j) Then
                 .Add Application.Index(sn, jj)
                 sn(jj, 3) = ""
                Exit For
                End If
            Next
        Next
        For j = 0 To .Count - 1
         Tabelle3.Cells((j + 1) + 10, 1).Resize(, UBound(sn, 2)) = .Item(j)
        Next
    End With
End Sub































'Rem Ref
'    http://www.excelforum.com/excel-programming-vba-macros/1139207-how-to-move-a-userform-and-module-from-one-book-to-another-2.html
'    http://www.excelforum.com/excel-programming-vba-macros/1138300-vba-userform-value-check-if-user-form-buttons-checked-not-working-check-button-on-open.html
'    http://www.excelforum.com/excel-programming-vba-macros/1139742-workbooks_open-crashes-when-file-opened-with-code-manually-open-ok-userform-issue.html
'    http://www.excelfox.com/forum/showthread.php/2130-Sort-an-array-based-on-another-array-VBA?p=9985#post9985
'    http://www.snb-vba.eu/VBA_Arraylist_en.html
'    http://www.snb-vba.eu/VBA_Arraylist_en.html#L_11.3
'    http://www.excelforum.com/showthread.php?t=1154829&page=4#post4502593
'    http://www.excelforum.com/excel-programming-vba-macros/1160648-how-to-create-a-pop-up-notification-for-two-different-conditions-at-the-same-time.html#post4507157
'    http://www.excelfox.com/forum/showthread.php/2130-Sort-an-array-based-on-another-array-VBA?p=9985#post9985

**DocAElstein** · 01-30-2017, 04:58 AM

**DocAElstein** · 02-03-2017, 07:13 PM

Code for Nelson in this Thread
http://www.excelfox.com/forum/showth...liday-overtime
http://www.excelfox.com/forum/showth...0060#post10060

Code:

Option Explicit
Sub IJAdjustTotalAllWorksheet()     '  http://www.excelfox.com/forum/showth...0060#post10060
Rem 1) Workbooks Info.
Dim Wb As Workbook                  ' Dim: For Object variabls: Address location to a "pointer". That has all the actual memory locations (addresses) of the various property values , and it holds all the instructions what / how to change them , should that be wanted later. That helped explain what occurs when passing an Object to a Call ed Fucntion or Sub Routine By Val ue. In such an occurance, VBA actually  passes a copy of the pointer.  So that has the effect of when you change things like properties on the local variable , then the changes are reflected in changes in the original object. (The copy pointer instructs how to change those values, at the actual address held in that pointer). That would normally be the sort of thing you would expect from passing by Ref erence.  But as that copy pointer "dies" after the called routine ends, then any changes to the Addresses of the Object Properties in the local variable will not be reflected in the original pointer. So you cannot actually change the pointer.)
 Set Wb = ActiveWorkbook            ' Set: Fill or partially Fill: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. We will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
Dim wsStear As Worksheet            ' Used for each Worksheet counting Tabs from left from 1 To Total
Rem 2) varables for some totals ;)
Const TDays As Long = 30            'Total days just taken as 30            ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in.  '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
Dim NOHrsV2 As Double, HOHrsV2 As Double                                    ' I am proposing to use the underlying number an adjust as necerssary to get the reqired format
Dim Dte As Date, DteNo As Long                                              ' I am hoping Dte will sort out getting a date in a format that I can use the Weekday function to see what week day it is and get that as a nuumber to check for..
Rem 3) Loop through worksheets and give some Totals
Dim Cnt As Long ' Loop Bound variable count for going through all worksheets
'3a) main Loop start=====================================================
    For Cnt = 1 To Wb.Worksheets.Count                                      ' The Worksheets collection Object Property returns the number of worksheet items in the Workbook
     Let NOHrsV2 = 0: Let HOHrsV2 = 0                                       ' The varaibles are emtied before run for each worksheet
     Set wsStear = Wb.Worksheets.Item(Cnt)                                  ' At each loop the variable is set to the current Worksheet counting from the Cnt'ths tab from left
    Dim lr As Long                                                          ' Used for last row number in column E
     Let lr = wsStear.Range("E" & Rows.Count & "").End(xlUp).Row            ' The Range Object ( cell ) that is the last cell in the column of interest (CHOOSE a column typically that will always have a last Entry in any Data) ,( Row Number given by .Count Property applied to ( any Worksheet would do, so leaving unqualified is OK here, ) Spreadsheet Range Rows Property)    has the Property .End ( argument "Looking back up" ) appled to it. This Returns a new Range ( cell ) object which is that of the first Range ( cell ) with something in it "looking back up" in the XL spreadsheet from that last Cell. Then the .Row Property is applied to return a long number equal to the Row number of that cell:     Rows.Count is the very last row number in your Worksheet. It is different for earlier versions of Excel.  The End(xlUp) is the same as pressing a Ctrl+UpArrow key combination. The final ".Row" returns the row where the cursor stops after moving up.
    Dim FstDtaCel As Range: Set FstDtaCel = wsStear.Range("A1")             ' Worksheets Range(" ") Property used to return Range object of first cell in second row
'3b) Data arrays from worksheet. We need columns E H I J      ....   Date ( Column E ) and Total hrs ( Column H ) are required to use in calculations
    Dim arrDte() As Variant, arrTotHrs() As Variant                         ' In the next lines the .Value2 or .value Property is applied a Range object which presents the Value or Value2 value or values in a single variable of appropriate type or a field of member Elements of varaint types.We are expecting the latter, so declare ( Dim ) a dynamic Array variable appropriately. It must be dynamic as its size will be defined at that assignment
     Let arrDte() = FstDtaCel.Offset(0, 4).Resize(lr, 1).Value    '  E      ' One thing you pick up when learning VBA programming is that referring to cells from one to another via an offset is both fundamental and efficient. That makes sense as Excel is all about using the offsets mentioned above. So like if you use them you can “cut out the middle man”. ( The middle man here might be considered as, for example, in VBA, using extra variables for different Range objects: A fundamental thing to do with any cell ( or strictly speaking the Range object associated to a cell ) is the Range Item Property of any range Object, through which you can “get at” any other Range object. http://www.excelforum.com/showthread...t=#post4563838 ( It is often quicker than using a separate variable for each Range object – probably as all the variable does is hold the offset , so you might as well use the offset in the first place.. )
     Let arrTotHrs() = FstDtaCel.Offset(0, 7).Resize(lr, 1).Value '  H      ' Similarly Another thing you pick up along the way is that the cells ( or strictly speaking the Range objects associated with it ) can be organised into groups of cells which then are also called Range objects and are organised in their constituent parts exactly the same as for the single cell Range object. Once again this is all an indication of organising so that we get at information by sliding along a specific amount ( offset value). The Offset and Resize properties therefore return a new range object. I use the .Value 2 here as i seemed to get it for .Value anyway, not sure why yet, - so i thought be on the safe side , get it always and work somehow with that for now and convert as necerssary.   Also 1 breadth Arrays due to Alan Intercept theory are held in such a ways as to be very effient in usage of values within:
    
    Dim arrInNorm() As Variant, arrInOver() As Variant
     Let arrInNorm() = FstDtaCel.Offset(0, 8).Resize(lr, 1).Value2 ' I      ' Normal Hrs ( Column I ) are needed as they must be set to zero for Holy ?? Holidays ?? Friday ??
     Let arrInOver() = FstDtaCel.Offset(0, 9).Resize(lr, 1).Value2 ' J      ' Overtime ( Column J ) is needed as it will be changed and then used in calculations
'3c) Inner loop for rows
    Dim ShtCnt As Long ' Loop Bound Variable Count for hours columns looping
        For ShtCnt = 1 To UBound(arrDte(), 1) Step 1 '------------------- For "rows" in data arrays
'3d) We need to check for a Holy?? Holiday?? Friday??    Adjust columns I and J so that column I has no hours for holiday day and total hours goes to over time hours with criteria 9 or less than 9 hrs all total hours added overtime, 10 or above 10 hrs one hour deducted from total hours and added to column J
         Let Dte = arrDte(ShtCnt, 1): Let DteNo = Weekday(Dte, [vbSunday])  ' I do not really nead this extra variable, but for dates I prefer always to do this to help in looking into the variable in Debugging
            If DteNo = 6 Then ' 6 I think is Friday, Nelson's Holy HoliDay ?
                If (arrTotHrs(ShtCnt, 1) * 24) <= 9 Then                    '(i) If  Total Hrs are less than or equal to 9 ,Then all  Total Hrs are added to Overtime Hrs
                 Let arrInOver(ShtCnt, 1) = arrTotHrs(ShtCnt, 1)            ' Given To ' Added to arrInOver(ShtCnt, 1) + arrTotHrs(ShtCnt, 1)
                ElseIf (arrTotHrs(ShtCnt, 1) * 24) > 9 Then                 ' (ii) If  Total Hrs are less greater than 9 , Then (  Total Hrs - 1 )  are added to Overtime Hrs
                 Let arrInOver(ShtCnt, 1) = arrTotHrs(ShtCnt, 1) - 1 / 24   ' Given To      ' arrInOver(ShtCnt, 1) + arrTotHrs(ShtCnt, 1) - 1 / 24 'Added to  1 hr less overtime for more than 9 hrs worked
                End If
             Let arrInNorm(ShtCnt, 1) = Empty                               ' (iii) As array is variant type can empty     Remove normal Hrs  Array for(Column I) is then set tom zerow for this "row"o
            Else ' No Holy Holiday
            End If
'3e)
            If arrInNorm(ShtCnt, 1) <> 0 And arrInOver(ShtCnt, 1) <> 0 Then Let NOHrsV2 = NOHrsV2 + arrInOver(ShtCnt, 1) ' Normal Overtime is simply calculated from summing hours in column J  only If there are Overtime hours in column J And there are  Normal hours are in column I.
            If arrInNorm(ShtCnt, 1) = Empty And arrInOver(ShtCnt, 1) <> 0 Then Let HOHrsV2 = HOHrsV2 + arrInOver(ShtCnt, 1) ' Holiday  Overtime is simply calculated from summing hours in column J  only If there are Overtime hours in column J And there are  no Normal hours are in column I.
        Next ShtCnt '--------------------------End Inner loop for rows-----
'3f) Paste out final Totals and days to current Worksheet
     Let wsStear.Range("G34").Value = NOHrsV2 * 24 'Normal Overtime is held in Array as fraction of a day
     Let wsStear.Range("J34").Value = HOHrsV2 * 24 'Holiday Overtime is held in Array as fraction of a day
     Let wsStear.Range("C34").Value = TDays ' The constant value of Total days is simply added to cell C34
'3g) Normal   Hrs  ( Column I ) and Overtime Hrs ( Column J ) are  changed
     Let FstDtaCel.Offset(0, 9).Resize(lr, 1).Value2 = arrInOver() ' J       ' The required spreadsheet cells range has its Range Object .Value2 values filled an allowed direct assignment to an array of values
     Let FstDtaCel.Offset(0, 8).Resize(lr, 1).Value2 = arrInNorm() ' I
    Next Cnt '==End main Loop==============================================
End Sub


' Rem Ref '_-   http://www.excelfox.com/forum/showth...0062#post10062
'_-             http://www.excelfox.com/forum/showth...0012#post10012