Expected Output:
Using Excel 2007 32 bit
Row\Col 16 17 0 10 20 30 40 18 2 12 22 32 42 19 4 14 24 34 44 20 6 16 26 36 46 21 10 20 30 40 50 22 12 22 32 42 52 23 14 24 34 44 54 24 16 26 36 46 56 25 18 28 38 48 58 26
Sheet: NPueyoGyanArraySlicing
Required Function in next Post
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
Function for last Three Posts
Code:Function FuR_Alan(ByVal rngIn As Range, ByVal FoutRw As Long) As Variant 10 ' use "neat magic" code line arrOut() = Application.Index(arrIn(), rwsT(), clms()) ' http://www.excelforum.com/excel-new-...ba-arrays.html http://www.mrexcel.com/forum/excel-q...ml#post4375354 20 ' BUT in Cells form arrOut() = Application.Index(Cells, rwsT(), clms()) ' http://www.excelforum.com/excel-prog...t-range-2.html 30 Dim ws As Worksheet ' ' Preparing a "Pointer" to an Initial "Blue Print" ( or a Form, or a Questionnaire not yet filled in, a template etc.) in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Object of this type ) . This also us to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense ) 40 Set ws = rngIn.Parent ' Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed. http://www.excelforum.com/excel-prog...ml#post4387191 50 ws.Range("K30").ClearContents: ws.Range("K30").Value = "Here I am, in this Worksheet!" 60 'clms() 70 Dim sClm As Long, Cs As Long 'Variable for Count of, Start Column. - This makes a Pigeon Hole sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. Long is very simple to handle, final memory "size" type is known (13.456, 00.001 have same "size" computer memory ),so an Address suggestion can be given for 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 anyway, so a Long is actually faster) 80 Let sClm = rngIn.Areas.Item(1).Column: Cs = rngIn.Areas.Item(1).Columns.Count ' A Range in a Worksheet must not be continuous. The way I understand it, a Range object is a group of cells in a worksheet. The list of the cells of the range is organised in rectangular groups of cells, each one named an Area. Post #27 http://www.mrexcel.com/forum/excel-q...-column-3.html http://www.eileenslounge.com/viewtop...181736#p181736 90 Dim clms() As Variant ' Evaluate Function used below returns a Field of Variant Element Types so the Array Elemments must be Declared appropriately. Must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it. 95 Let clms() = Evaluate("column(" & CL(sClm) & ":" & CL(sClm + (Cs - 1)) & ")") 100 160 'rwsT() 170 Dim sRw As Long, Rs As Long ' 180 Let sRw = rngIn.Areas.Item(1).Row: Let Rs = rngIn.Areas.Item(1).Rows.Count 240 'Get Full row indicies convenientally ( As 1 D "pseudo horizontal" Array ) from Spreadsheet Column() Function 250 Dim rws() As Variant: Let rws() = Evaluate("column(" & CL(sRw) & ":" & CL(sRw + (Rs - 1)) & ")") 260 280 'Get full sequential row indicies in a string. 290 Dim strRws As String: Let strRws = VBA.Strings$.Join(rws(), "|") ' 'The VBA strings collection such as Join in there basic form must not returnn a string, they can also return for example Null, a special type of variant. That lies within it's "powers. - It will coerce even an Empty, or Null to a variant type and return that. That takes extra ( unecerssary work here ). If the result of a function is used as a string or assigned it to a string variable, use the $ form of the function. This results in faster executing code, because a conversion from a variant to a string is unnecessary. http://www.excelforum.com/excel-new-...ml#post4084783 I believe that without $ a Strings collection Function coerces the first parameter into Variant, with $ does not - that's why $ is preferable over no $ , it's theoretically more efficient. http://www.xoc.net/standards/rvbacc....rSignFunctions 300 330 'Get String with missing row 340 Dim strrwsD As String: Let strrwsD = Replace(strRws, "|" & FoutRw & "", "", 1, -1) 350 360 'Get Array ( 1 D Pseudo Horizontal ) of required row indicies 370 Dim rwsS() As String ' The VBA Strings Collection Function, Split, used below returns a Field of String Element Types so the Array Elemments must be Declared appropriately. It must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it. 375 Let rwsS() = VBA.Strings$.Split(strrwsD, "|", -1) 380 'final Transposed Array for "magic neat" code line 390 Dim rwsT() As String: ReDim rwsT(0 To (UBound(rwsS())), 1 To 1) ' Both the type and size of Array is known so can be decared initially appropriatelly. Re Dim must be used as Dim only takes values, not variables 400 Dim Cnt As Long 410 For Cnt = 0 To UBound(rwsS()) 420 Let rwsT(Cnt, 1) = rwsS(Cnt) 430 Next Cnt 440 'Output Array 450 Dim arrOut() As Variant 460 Let arrOut() = Application.Index(ws.Cells, rwsT(), clms()) '"Magic neat" Code line in Cells first argument Form 470 480 Let FuR_Alan = arrOut() 490 500 ' . Transpose 510 Dim rwsDotT() As Variant ' Transpose Function used below returns a Field of Variant Element Types so the Array Elemments must be Declared appropriately. Must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it. 520 Let rwsDotT() = Application.Transpose(rwsS()) 530 Let arrOut() = Application.Index(ws.Cells, rwsDotT(), clms()) 540 550 Let FuR_Alan = arrOut() ' End Function
Function Code for getting Column Letter from Column Number is in main Thread
http://www.excelfox.com/forum/showth...=9837#post9837
Public Function CL(ByVal lclm As Long) As String
and Further given again along with an 'opened up' and 'Commented version in next post
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
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
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
Second Code with further lines to overcome extra () required for start row and star column codes
Obtaining grid coordinates for an Area of contiguous cells in a Spreadsheet using [ ] and Evaluate(" ") through the use of a Named Range for that Area
Aka ' It is a Range Name Test 2: Its n Range Name Test 2: 's 'n Rng Name Test 2: s n Rg Name Testie 2: snRg.Name = "snRgNme"This code is in support of other Posts in various Threads. ( I will edit the Links as I reference this post )
The code takes in a hard coded Range, A1:E10.
That Range is given a Name as held in the Names Register of a Workbook ( Workbooks Scope ).
Various code lines are developed which reference this Named Range and return the Grid Coordinates.
These coordinates are held within the following Long Type Variables
Cs is the column count
sClm is the start column
stpClm is the stop column
Rs is the rows count start row
sRw is the start row
stpRw is the stop row
Code:' Code 2 '10 ' It is a Range Name Test 2: Its n Range Name Test 2: 's 'n Rng Name Test 2: s n Rg Name Testie 2: snRg.Name = "snRgNme" Sub snRgNameTest2() ' Inspired by.. snb .. " array [ ] " ' http://www.excelfox.com/forum/showthread.php/2083-Delete-One-Row-From-A-2D-Variant-Array?p=9714#post9714 20 ' Worksheets Info 30 Dim ws As Worksheet ' ' Preparing a "Pointer" to an Initial "Blue Print" ( or a Form, or a Questionnaire not yet filled in, a template etc.) in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Object of this type ) . This also us to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense ) 40 'Set ws = ThisWorkbook.Worksheets("NPueyoGyanArraySlicing") 'The worksheets collection object is used to Set ws to the Sheet we are playing with, so that we carefull allways referrence this so as not to go astray through Excel Guessing inplicitly not the one we want... ' Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed. http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191 50 Set ws = ActiveSheet ' Alternative to last line, make code apply to the current active sheet, - That being "looked at" when running this code ' 60 Dim vTemp As Variant ' To help development when you are not sure what type is retuned. "Suck and see what comnes out!" Highlight it and Hit Shift+F9 to see it in the imediate Window 70 ' Named Range referrencing ' Workbooks ( Default ) Scope Invoke Pike Evaluate Rabbit Rabbit. How's the Bunny ? Bunnytations Banters 80 Dim snRg As Range: Set snRg = ws.Range("A1:E10") 90 Dim sName As String: Let sName = "snRgNme" ' 100 Let snRg.Name = "snRgNme" ' It is a Range Name me - " 's 'n Range Name me " .. "snRgNme" ;) This name appears permanentlly in then sheet. It remains referrencing this range unless the name iis deleted or the range referrenced is overwritten by a similar code line which has a different range in it on RHS of = http://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables 110 Let snRg.Name = sName ' Identical to last line 120 130 '== DANGER: === Pitful: Above we gave the Range Object a Name, but now see what "Name" or "Name" 's comes back "!" ! 131 Dim clms() As Variant 'Array to take returned Variant type Field of sequential column numbers 132 Dim retRefstrName As String, retObjName As Object 133 Let retRefstrName = snRg.Name: Set retObjName = snRg.Name: Debug.Print snRg.Name 'something of the form "NPueyoGyanArraySlicing!$A$1:$E$10" is reveald in Immediate ( Ctrl+G when in VB Editor ) Window 134 'Let clms() = Evaluate("column(=NPueyoGyanArraySlicing!$A$1:$E$10)") 'Let clms() = Evaluate("column(" & retRefstrName & ")")' Rintime Error 13: Incompatiblee types 135 Let clms() = Evaluate("column(NPueyoGyanArraySlicing!$A$1:$E$10)") 'Works 137 Dim NameOnly As String: Let NameOnly = Replace((snRg.Name), "!", "", (InStr(1, (snRg.Name), "!"))): 'Debug.Print snRg.Name: Dim pos&: pos = InStr(1, (snRg.Name), "!"): NameOnly = Replace((snRg.Name), "!", "", pos) ' We had ---- "NPueyoGyanArraySlicing!$A$1:$E$10" This is a String referrece returned when the Name Object is used directly or set to a String Variable. so here I return a string that starts at the position of the ! and which replaces in that truncated shortened string - "!$A$1:$E$10" the "!" with nothing 138 Let clms() = Evaluate("column(" & NameOnly & ")"): Let clms() = Evaluate("column(" & Replace((snRg.Name), "!", "", (InStr(1, (snRg.Name), "!"))) & ")") 139 140 Dim strName As String: Let strName = snRg.Name.Name: Debug.Print strName: Let strName = retObjName.Name: Debug.Print strName ' returns our original "CoN" 142 Let clms() = Evaluate("column(" & strName & ")") 150 Dim rngF1G2 As Range: Set rngF1G2 = Range("F1:G2"): Let Range("F1:G2").Value = "From Line 150" 151 Let Range("=NPueyoGyanArraySlicing!F1:G2").Value = "From Line 151" 152 Let rngF1G2.Name = "snFG": Let Range("snFG").Value = "From Line 152" 153 154 159 '=============== 160 Let clms() = Evaluate("column(snRgNme)"): Let clms() = [column(snRgNme)] ' Full and "shorthand" Simple 1 D "pseudo horizontal" Array of column Indicies. 170 ' 180 ' Count, Start, and Stop of columns in an Area of contiguous cells in a Spreadsheet 190 Dim Cs As Long 'Variable for ColumnsCount. -This makes a Pigeon Hole sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular "Value", or ("Values" for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. Long is very simple to handle, final memory "size" type is known (13.456, 00.001 have same "size" computer memory ),so an Address suggestion can be given for 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 anyway, so a Long is actually faster) 200 Let Cs = Evaluate("columns(snRgNme)") ' = 5 210 'Let Cs = Evaluate("columns(RetunedsnRgName)") 'Run time Error as expected 220 Let Cs = [columns(snRgNme)] ' = 5 'Is this Most Powerful Command in VBA?, or what ... http://www.ozgrid.com/forum/showthread.php?t=52372 http://www.mrexcel.com/forum/excel-questions/899117-visual-basic-applications-range-a1-a5-vs-%5Ba1-a5%5D-benefits-dangers.html 230 'Let Cs = [columns(RetunedsnRgName)] 'Run time Error as expected 240 Let Cs = [columns(A1:E10)] ' = 5 250 Let vTemp = Evaluate("column(snRgNme)") ' Reveals an Array {1, 2, 3, 4, 5} - 1 Dimension "pseudo Horizontal" Array 260 Dim sClm As Long 'Variable for Start Column 270 Let sClm = Evaluate("column(A1:E10)")(1) 280 Let sClm = Evaluate("column(snRgNme)")(1) ' = 1 290 Let sClm = [column(A1:E10)]()(1) 300 Let sClm = [column(snRgNme)]()(1) 301 302 Let sClm = Evaluate("=MIN(column(snRgNme))"): Let sClm = [=MIN(column(snRgNme))] 'Alternative using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate 329 ' 330 Dim stpClm% ' Variable for Stop column Number ' ( % is shorthand for As Long ..http://www.excelforum.com/showthread.php?t=1116127&p=4256569#post4256569 340 Let stpClm = sClm + (Cs - 1) ' = 5 350 ' [ ] 360 Let stpClm = [column(A1:E10)]()(1) + ([columns(A1:E10)] - 1) 370 Let stpClm = [column(snRgNme)]()(1) + ([columns(snRgNme)] - 1) 380 ' In between step [ ] and Evaluate(" ") 390 Let stpClm = [column(snRgNme)]()(UBound([column(snRgNme)])) 400 ' Now Full Evaluate(" ") 410 Let stpClm = Evaluate("column(snRgNme)")(1) + (Evaluate("columns(snRgNme)") - 1) 420 Let stpClm = Evaluate("column(snRgNme)")(UBound(Evaluate("column(snRgNme)"))) 421 430 Let stpClm = Evaluate("=MIN(column(snRgNme))") + (Evaluate("columns(snRgNme)") - 1) ''Alternatives using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate 431 Let stpClm = [=MIN(column(snRgNme))] + ([columns(snRgNme)] - 1) 432 Let stpClm = [=MIN(column(snRgNme)) + (columns(snRgNme) - 1)] 439 ' 440 ' Start, Count and Stop of rows in an Area of contiguous cells in a Spreadsheet 450 Dim Rs As Long 'Rows Count 460 Let Rs = Evaluate("rows(snRgNme)") 470 Let Rs = [rows(snRgNme)] 480 Let Rs = [rows(A1:E10)] 490 Let vTemp = Evaluate("row(snRgNme)") ' = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} 500 Dim sRw As Long 'Start Row 510 Let sRw = Evaluate("row(A1:E10)")(1, 1) 'Note a 2 Dimensional, 1 column, "vertical" Array is returned : ' vTemp = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} 520 Let sRw = Evaluate("row(snRgNme)")(1, 1) 530 Let sRw = [row(A1:E10)]()(1, 1) 540 Let sRw = [row(snRgNme)]()(1, 1) 541 550 Let sRw = Evaluate("=MIN(Row(snRgNme))"): Let sRw = [=MIN(Row(snRgNme))] '''Alternatives using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate 560 570 Dim stpRw% 'Stop Row 580 Let stpRw = sRw + (Rs - 1) 590 Let stpRw = [row(A1:E10)]()(1, 1) + ([rows(A1:E10)] - 1) 600 Let stpRw = [row(snRgNme)]()(1, 1) + ([rows(snRgNme)] - 1) 610 ' 620 Let stpRw = [row(snRgNme)]()(UBound([row(snRgNme)], 1), 1) 'UBound([row(snRgNme)], 1) is Ubound first ( "row" ) dimension. UBound([row(snRgNme)], 2) would be the second dimension ( "column" ) count 630 ' 640 Let stpRw = Evaluate("row(snRgNme)")(1, 1) + (Evaluate("rows(snRgNme)") - 1) 650 Let stpRw = Evaluate("row(snRgNme)")(UBound(Evaluate("row(snRgNme)")), 1) 660 ' 670 Let stpRw = Evaluate("=MIN(Row(snRgNme))") + (Evaluate("rows(snRgNme)") - 1) ''''Alternatives using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate 680 Let stpRw = [=MIN(Row(snRgNme))] + [rows(snRgNme)] - 1 690 Let stpRw = [=MIN(Row(snRgNme))] + [rows(snRgNme)] - 1 700 Let stpRw = [=MIN(Row(snRgNme)) + rows(snRgNme) - 1] End Sub
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
Alternative Codes using [ ] shorthand
Delete One Row From A 2D Excel Range Area
' To Test Function, Type some arbitrary values in range A1:E10, step through Main Test Code in F8 Debug Mode in VB Editor, and examine Worksheet, Immediate Window ( Ctrl+G when in VB Editor ), hover over variables in the VB Editor Window with mouse cursor, set watches on variables ( Highlight any occurrence of a variable in the VB Editor and Hit Shift+F9 ) , etc.. and then you should expected the required Output to be pasted out starting Top Left at cell M17
Code:' Delete One Row From A 2D Excel Range Area ' To Test Function, Type some arbitrary values in range A1:E10, step through code in F8 Debug Mode in VB Editor, and examine Worksheet, Immediate Window ( Ctrl+G when in VB Editor ), hover over variables in the VB Editor Window with mouse cursor, set watches on variables ( Highlight any occurrence of a variable in the VB Editor and Hit Shift+F9 ) , etc.. and then you should expected the required Output to be pasted out starting Top Left at cell M17 ' "Short hand", ShtHd, version using [] ' requires snb and kalak "neat trick" so you can to all intents and purpose do very close to doing vba Un Hard coded in [ ] - http://www.excelfox.com/forum/showthread.php/2083-Delete-One-Row-From-A-2D-Variant-Array?p=9714#post9714 http://www.mrexcel.com/forum/excel-questions/899117-visual-basic-applications-range-a1-a5-vs-%5Ba1-a5%5D-benefits-dangers.html#post4331217 ' and Evaluate [ ] Properties , AloPerties, Methods - Alan Dynamic Coding Wonks http://www.excelforum.com/excel-programming-vba-macros/1141369-evaluate-and-differences-evaluated-array-return-needs-extra-bracket-for.html#post4400666 Function FuR_AlanShtHd(ByVal rngIn As Range, ByVal FoutRw As Long) As Variant 1 Let rngIn.Name = "snRgNme" 5 Dim vTemp As Variant 'A varyable to fill with something and "suck and see" what you get 10 ' use "neat magic" code line arrOut() = Application.Index(arrIn(), rwsT(), clms()) ' http://www.excelforum.com/excel-new-...ba-arrays.html http://www.mrexcel.com/forum/excel-q...ml#post4375354 20 ' BUT in Cells form arrOut() = Application.Index(Cells, rwsT(), clms()) ' http://www.excelforum.com/excel-prog...t-range-2.html 30 Dim ws As Worksheet ' ' Preparing a "Pointer" to an Initial "Blue Print" ( or a Form, or a Questionnaire not yet filled in, a template etc.) in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Object of this type ) . This also us to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense ) 40 Set ws = rngIn.Parent ' Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed. http://www.excelforum.com/excel-prog...ml#post4387191 50 ws.Range("K30").ClearContents: ws.Range("K30").Value = "Here I am, in this Worksheet!" 60 'clms() 70 Dim sClm As Long, Cs As Long 'Variable for Count of, Start Column. - This makes a Pigeon Hole sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular "Value", or ("Values" for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. Long is very simple to handle, final memory "size" type is known (13.456, 00.001 have same "size" computer memory ),so an Address suggestion can be given for 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 anyway, so a Long is actually faster) 80 Let sClm = [=MIN(column(snRgNme))] '###Not needed now. Alternative using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate 90 Dim clms() As Variant ' Evaluate Function used below returns a Field of Variant Element Types so the Array Elemments must be Declared appropriately. Must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it. 95 Let clms() = [column(snRgNme)] ' snb Range Name equivalent so ### 'Let clms() = Evaluate("column(" & CL(sClm) & ":" & CL(sClm + (Cs - 1)) & ")") 100 160 'rwsT() 170 Dim sRw As Long, Rs As Long, stpRw As Long ' 180 Let sRw = [=MIN(Row(snRgNme))] '''Alternatives using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate ' Let sRw = rngIn.Areas.Item(1).Row 190 Let Rs = [rows(snRgNme)] 'Let Rs = rngIn.Areas.Item(1).Rows.Count 191 Let stpRw = [=MIN(Row(snRgNme)) + rows(snRgNme) - 1] '''Alternatives using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate 200 240 'Get Full row indicies convenientally ( As 1 D "pseudo horizontal" Array ) from Spreadsheet Column() Function 250 Dim rws() As Variant: Let rws() = Evaluate("column(" & CL(sRw) & ":" & CL(sRw + (Rs - 1)) & ")") 251 Let vTemp = [CL(1)]: vTemp = [CL(MIN(Row(snRgNme)))] 'Both Return "A" 252 vTemp = [CL(MIN(Row(snRgNme)) + rows(snRgNme) - 1)] 'Returns "J" 254 Let rws() = [column(A:J)] ' Works 257 'Let rws() = [column(CL(1):J)] ' Fails - Bug in Excel ! ? ! 258 'Let rws() = [column(CL(MIN(Row(snRgNme))):CL(MIN(Row(snRgNme)) + rows(snRgNme) - 1))] ' Fails - Bug in Excel ! ? ! 260 Let rws() = Evaluate("column(" & [CL(MIN(Row(snRgNme)))] & ":" & [CL(MIN(Row(snRgNme)) + rows(snRgNme) - 1)] & ")") 270 280 'Get full sequential row indicies in a string. 290 Dim strRws As String: Let strRws = VBA.Strings$.Join(rws(), "|") ' 'The VBA strings collection such as Join in there basic form must not returnn a string, they can also return for example Null, a special type of variant. That lies within it's "powers. - It will coerce even an Empty, or Null to a variant type and return that. That takes extra ( unecerssary work here ). If the result of a function is used as a string or assigned it to a string variable, use the $ form of the function. This results in faster executing code, because a conversion from a variant to a string is unnecessary. http://www.excelforum.com/excel-new-...ml#post4084783 I believe that without $ a Strings collection Function coerces the first parameter into Variant, with $ does not - that's why $ is preferable over no $ , it's theoretically more efficient. http://www.xoc.net/standards/rvbacc....rSignFunctions 300 330 'Get String with missing row 340 Dim strrwsD As String: Let strrwsD = Replace(strRws, "|" & FoutRw & "", "", 1, -1) 350 360 'Get Array ( 1 D Pseudo Horizontal ) of required row indicies 370 Dim rwsS() As String ' The VBA Strings Collection Function, Split, used below returns a Field of String Element Types so the Array Elemments must be Declared appropriately. It must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it. 375 Let rwsS() = VBA.Strings$.Split(strrwsD, "|", -1) 380 'final Transposed Array for "magic neat" code line 390 Dim rwsT() As String: ReDim rwsT(0 To (UBound(rwsS())), 1 To 1) ' Both the type and size of Array is known so can be decared initially appropriatelly. Re Dim must be used as Dim only takes values, not variables 400 Dim Cnt As Long 410 For Cnt = 0 To UBound(rwsS()) 420 Let rwsT(Cnt, 1) = rwsS(Cnt) 430 Next Cnt 440 'Output Array 450 Dim arrOut() As Variant 460 Let arrOut() = Application.Index(ws.Cells, rwsT(), clms()) '"Magic neat" Code line in Cells first argument Form 470 480 Let FuR_AlanShtHd = arrOut() 490 500 ' . Transpose 510 Dim rwsDotT() As Variant ' Transpose Function used below returns a Field of Variant Element Types so the Array Elemments must be Declared appropriately. Must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it. 520 Let rwsDotT() = Application.Transpose(rwsS()) 530 Let arrOut() = Application.Index(ws.Cells, rwsDotT(), clms()) 540 550 Let FuR_AlanShtHd = arrOut() ' End Function
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
More Named Range Scope Wonks. Problems when Worksheet Scoped WorkSheet is different from Worksheet refered to in RefersTo:= Range Object argument
Here is a another partial solution to the This Thread
http://www.excelforum.com/excel-prog...acket-for.html
It was also used to answer a few questions I had here:
http://www.thespreadsheetguru.com/bl...o-named-ranges[I][COLOR="#000080"] ( Comment 22 )
Here is what I wrote there:
Reply Posted at
http://www.thespreadsheetguru.com/bl...o-named-ranges
6 th June 2016:
Hi
Hi Just feeding back from my "experiments" over the weekend_...
http://www.excelforum.com/showthread...t=#post4404276
_..So now o answer my questions:
_1) I have not yet seen anything to suggest the answer to that is not yes.
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
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
http://www.excelforum.com/showthread...=4#post4502593
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
Hi Nelson,
I think both
_ the ways to do this,
_and the possibly output forms
are infinite
_ You must try to be a bit more precise in exactly what you want.
_ I have done another fairly simple code:
It is here:
http://www.excelfox.com/forum/showth...0047#post10047
It runs though all the Worksheets to calculate normal overtime and holiday overtime. The total added for all Worksheets is displayed in a message box:
NelsonMessageBox.jpg http://imgur.com/XSvQpQi
Attachment 1863
I expect the code is not yet want you finally want. It tells you the total sum for Normal Overtime and Holiday overtime.
( I still do not understand what calculations should be done for total days)
Please start a new Thread here: _.. http://www.excelfox.com/forum/forumd...p/2-Excel-Help
_.. Please try again to explain exactly what you want.
_.. I will then look at it again for you on Monday
Alan
Code:
Code:' An Initial code for Nelson for Post in this Forum http://www.excelfox.com/forum/forumdisplay.php/2-Excel-Help Sub SomeingSumTotals() ' https://www.dropbox.com/s/u76eo5trrtppgoi/SAMPLE2.xlsx?dl=0 Rem 1) Worksheets info. Dim WsStear As Worksheet ' 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: 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 Rem 2) varables for some totals ;) Dim NOHrsV2 As Double, HOHrsV2 As Double, TDays As Long Let NOHrsV2 = 0: Let HOHrsV2 = 0: Let TDays = 0 Rem 3) Loop through worksheets and give some Totals Dim Cnt As Long ' Loop Bound variable count for going through all worksheets For Cnt = 1 To ThisWorkbook.Worksheets.Count Set WsStear = ThisWorkbook.Worksheets.Item(Cnt) ' 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 lr As Long ' 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. ) 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("A2") ' Worksheets Range(" ") Property used to return Range object of first cell in second row Dim arrInNorm() As Variant, arrInOver() As Variant ' In the next lines the .Value2 Property is applied a Range object which presents the the 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 arrInNorm() = FstDtaCel.Offset(0, 8).Resize(lr - 1, 1).Value2 ' 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.php?t=1154829&page=13&p=4563838&highlight=#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 arrInOver() = FstDtaCel.Offset(0, 9).Resize(lr - 1, 1).Value2 ' 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. Dim ShtCnt As Long ' Loop Bound Variable Count for hours columns looping For ShtCnt = 1 To UBound(arrInNorm(), 1) Step 1 If arrInNorm(ShtCnt, 1) <> 0 And arrInOver(ShtCnt, 1) <> 0 Then Let NOHrsV2 = NOHrsV2 + arrInOver(ShtCnt, 1) If arrInNorm(ShtCnt, 1) = 0 And arrInOver(ShtCnt, 1) <> 0 Then Let HOHrsV2 = HOHrsV2 + arrInOver(ShtCnt, 1) Next ShtCnt Next Cnt Rem 4) Output some totals ;) MsgBox prompt:="Normal Overtime is " & NOHrsV2 * 24 & vbCrLf & "Holiday Overtime is" & HOHrsV2 * 24 ' NelsonMessageBox.jpg http://imgur.com/XSvQpQi '4b) Tell you Totals ' Sub Speach() ' Richard Buttrey http://www.excelforum.com/showthread.php?t=1164765&p=4535112#post4535112 Dim Saps As Object Set Saps = CreateObject("SAPI.SpVoice") Saps.Speak "Hello Nelson. These are Sum totals for Normal Overtime and Holiday Overtime for the two Worksheets you gave.. Normal Overtime is " & NOHrsV2 * 24 & ".. Holiday Overtime is " & HOHrsV2 * 24 & ".. I expect this is not yet quite what you want." ' ' End Sub End Sub
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
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
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
Second Code for nelson
Post 9
http://www.excelfox.com/forum/showth...0070#post10070
Code:Sub IJAdjustKAddTotalAllWorksheet() ' http://www.excelfox.com/forum/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10060#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 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 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 arrInNorm() As Variant, arrInOver() 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 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 Dim arrTotHrs() As Variant ' ,' ## ' arrDteClr() As Variant Let arrTotHrs() = FstDtaCel.Offset(0, 7).Resize(lr, 1).Value ' H ' ' 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.php?t=1154829&page=13&p=4563838&highlight=#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.. ) ' 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 arrK() As String 'I know the size, but must make it dynamic as Dim declaration only takes numbers, and so I use ReDim method below wehich can also take variables or formulas ReDim arrK(1 To UBound(arrInNorm(), 1), 1 To 1) ' Any array first dimension ("row") will do 'This will not work. ' ## ' Let arrDteClr() = FstDtaCel.Offset(0, 4).Resize(lr, 1).Interior.Color ' because .Interior property for a Range object shows only one value for the entire range which seems to be zero unless all the cells have a colour Dim arrDteClr() As Double, rngDts As Range Set rngDts = FstDtaCel.Offset(0, 4).Resize(lr, 1) Dim Rws As Long: ReDim arrDteClr(1 To lr, 1 To 1) ' so must loop in each Interior color value For Rws = 1 To UBound(arrDteClr(), 1) Step 1 'InnerLoop for dates background colors Let arrDteClr(Rws, 1) = rngDts.Item(Rws, "A").Interior.Color Next Rws '3c) Inner loop for rows Dim ShtCnt As Long ' Loop Bound Variable Count for hours columns looping For ShtCnt = 1 To UBound(arrDteClr(), 1) Step 1 '------------------- For "rows" in data arrays '3d) We need to check Interior color 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 ..... and add a H or N in helper column K If arrDteClr(ShtCnt, 1) = 65535 Then 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 Let arrK(ShtCnt, 1) = "H" ' Give string, "" value of H for Holiday in Admin's help column K Else ' No Holy Holiday Let arrK(ShtCnt, 1) = "N" ' give string N for normal End If '3e) ' from last code, is not now used to calculate totals Next ShtCnt '--------------------------End Inner loop for rows----- '3f) Paste out final Totals and days to current Worksheet Let wsStear.Range("G35").Value = "=SUMIF(K1:K" & lr & ",""N"",J1:J" & lr & ")*24" Let wsStear.Range("J35").Value = "=SUMIF(K1:K" & lr & ",""H"",J1:J" & lr & ")*24" 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 ' And can paste out help column if you like 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 Let FstDtaCel.Offset(0, 10).Resize(lr, 1).Value2 = arrK() ' K Next Cnt '==End main Loop============================================== End Sub
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
Posting Permissions
Reply With Quote
Bookmarks