Thread: Code Tag Test with Long Comments . Code Window Horitontal scroll bar
Code:'3) Part 3)--transfer range objects from dictionary to array of ranges in one go, typical Array assignment "One Liner" - The quick way to place data into an array is to dimension a variable ( for example rResults ) .... Dim rResults() As Variant '... As a Variant and then the "Let rResults = " code line Using a variant allows BOTH .-1) for capturing Objects, text, numbers, blanks errors etc. from those cells and also 2) allows rResults to be an array variable..... Let rResults() = dicLookupTable.Items() '... In this case it will also accept us quasi pre - defining as Array with the pair of parentheses rResults() 'End part 3)--- NOTE: this gives automatically the 0 to .. convention in rResults Array! and the output held in the Array is Variant type and is an OBJECT of the sort like a Range. (This compares with a similar code utilising An Array of Ranges rather than the MicrosoftScriptimeRuntimeDictionary. (In the Array of Ranges case we could Dim as Range or Variant here. - Important however in that case is that the Dynamic rResults() Array and the (in that Array of Ranges case non dynamic) arrIn() Array are of the same type. - Either both Range to return an Array of element Type Range or both Variant to Return an Array of element Type Of Objects of the Range Sort : - RoryA . You can only assign one array to another directly (i.e. without looping....... and the two arrays are the same type. http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html
Code:Dim TempCell As Range: Set TempCell = wksLE.Cells(1, Columns.Count): Dim TempCellOffset As Long: Let TempCellOffset = 0 'We choose a cell (or through the later use of the offset a column) to use for Duplicate or Empty cells. We use the last column in the sheet. (This is genarally a good practice as it will not effect attempts with .End(XltoLeft) to find last column '2a) Part2a) Looping to put Range Objects in MRSD 'For i = StartColumnTableOutput To LastColumnTableOutput Step 1 For rws = sr To lr Step 1 If wksLE.Cells(rws, 1).Value <> "" Then 'If cell is not empty then... If Not dicLookupTable.Exists(wksLE.Cells(rws, 1).Value) Then 'check that the unique value does not already exist. ##NOTE dicLookupTable.Add wksLE.Cells(rws, 1).Value, wksLE.Cells(rws, 1) 'it is easier to understand as well as kind of explicit the first argument does a CStr and the Second Takes anything Else 'If the key exists, that is to say we have a Range with a Duplicate value, we give the key a slightly modified (unique value) , still give the Range Object as an item, but make an indication, here by highlighting the cell in Pink Let TempCellOffset = TempCellOffset + 1 Let TempCell.Offset(TempCellOffset, 0).Value = "Duplicate at " & rws & " | " & 1 & "" wksLE.Cells(rws, 1).Interior.Color = 10987519 dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, wksLE.Cells(rws, 1) 'In case of duplicate we need a unique key, but we stillinclude the Duplicate Range End If Else 'Case fo an empty cell - inform of empty cell by writing message in that cell via the Tempory cell Let TempCellOffset = TempCellOffset + 1 'Go to next free tempory cell in tempory column Let TempCell.Offset(TempCellOffset, 0).Value = "Empty Cell at " & rws & " | " & 1 & "" dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, TempCell.Offset(TempCellOffset, 0) End If Next rws 'Next i 'End Part 2----------------------------------------------------------- '3) Part 3)--transfer range objects from dictionary to array of ranges in one go, typical Array assignment "One Liner" - The quick way to place data into an array is to dimension a variable ( for example rResults ) .... Dim rResults() As Variant '... As a Variant and then the "Let rResults = " code line Using a variant allows BOTH .-1) for capturing Objects, text, numbers, blanks errors etc. from those cells and also 2) allows rResults to be an array variable..... Let rResults() = dicLookupTable.Items() '... In this case it will also accept us quasi pre - defining as Array with the pair of parentheses rResults() 'End part 3)--- NOTE: this gives automatically the 0 to .. convention in rResults Array! and the output held in the Array is Variant type and is an OBJECT of the sort like a Range. (This compares with a similar code utilising An Array of Ranges rather than the MicrosoftScriptimeRuntimeDictionary. (In the Array of Ranges case we could Dim as Range or Variant here. - Important however in that case is that the Dynamic rResults() Array and the (in that Array of Ranges case non dynamic) arrIn() Array are of the same type. - Either both Range to return an Array of element Type Range or both Variant to Return an Array of element Type Of Objects of the Range Sort : - RoryA . You can only assign one array to another directly (i.e. without looping....... and the two arrays are the same type. http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html End Sub 'MicrosoftScriptimeRuntimeDictionaryRangeOfRangesKeysItems()
Code:Dim TempCell As Range: Set TempCell = wksLE.Cells(1, Columns.Count): Dim TempCellOffset As Long: Let TempCellOffset = 0 'We choose a cell (or through the later use of the offset a column) to use for Duplicate or Empty cells. We use the last column in the sheet. (This is genarally a good practice as it will not effect attempts with .End(XltoLeft) to find last column '2a) Part2a) Looping to put Range Objects in MRSD 'For i = StartColumnTableOutput To LastColumnTableOutput Step 1 For rws = sr To lr Step 1 If wksLE.Cells(rws, 1).Value <> "" Then 'If cell is not empty then... If Not dicLookupTable.Exists(wksLE.Cells(rws, 1).Value) Then 'check that the unique value does not already exist. ##NOTE dicLookupTable.Add wksLE.Cells(rws, 1).Value, wksLE.Cells(rws, 1) 'it is easier to understand as well as kind of explicit the first argument does a CStr and the Second Takes anything Else 'If the key exists, that is to say we have a Range with a Duplicate value, we give the key a slightly modified (unique value) , still give the Range Object as an item, but make an indication, here by highlighting the cell in Pink Let TempCellOffset = TempCellOffset + 1 Let TempCell.Offset(TempCellOffset, 0).Value = "Duplicate at " & rws & " | " & 1 & "" wksLE.Cells(rws, 1).Interior.Color = 10987519 dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, wksLE.Cells(rws, 1) 'In case of duplicate we need a unique key, but we stillinclude the Duplicate Range End If Else 'Case fo an empty cell - inform of empty cell by writing message in that cell via the Tempory cell Let TempCellOffset = TempCellOffset + 1 'Go to next free tempory cell in tempory column Let TempCell.Offset(TempCellOffset, 0).Value = "Empty Cell at " & rws & " | " & 1 & "" dicLookupTable.Add TempCell.Offset(TempCellOffset, 0).Value, TempCell.Offset(TempCellOffset, 0) End If Next rws 'Next i 'End Part 2----------------------------------------------------------- '3) Part 3)--transfer range objects from dictionary to array of ranges in one go, typical Array assignment "One Liner" - The quick way to place data into an array is to dimension a variable ( for example rResults ) .... Dim rResults() As Variant '... As a Variant and then the "Let rResults = " code line Using a variant allows BOTH .-1) for capturing Objects, text, numbers, blanks errors etc. from those cells and also 2) allows rResults to be an array variable..... Let rResults() = dicLookupTable.Items() '... In this case it will also accept us quasi pre - defining as Array with the pair of parentheses rResults() 'End part 3)--- NOTE: this gives automatically the 0 to .. convention in rResults Array! and the output held in the Array is Variant type and is an OBJECT of the sort like a Range. (This compares with a similar code utilising An Array of Ranges rather than the MicrosoftScriptimeRuntimeDictionary. (In the Array of Ranges case we could Dim as Range or Variant here. - Important however in that case is that the Dynamic rResults() Array and the (in that Array of Ranges case non dynamic) arrIn() Array are of the same type. - Either both Range to return an Array of element Type Range or both Variant to Return an Array of element Type Of Objects of the Range Sort : - RoryA . You can only assign one array to another directly (i.e. without looping....... and the two arrays are the same type. http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays.html End Sub 'MicrosoftScriptimeRuntimeDictionaryRangeOfRangesKeysItems()
Code:Function DeleteArrayRow(Arr As Variant, RowToDelete As Long) As Variant Dim Rws As Long, Cols As String Rws = UBound(Arr) - LBound(Arr) Cols = "A:" & Split(Columns(UBound(Arr, 2) - LBound(Arr, 2) + 1).Address(, 0), ":")(0) DeleteArrayRow = Application.Index(Arr, Application.Transpose(Split(Join(Application.Transpose(Evaluate("Row(1:" & (RowToDelete - 1) & ")"))) & " " & Join(Application.Transpose(Evaluate("Row(" & (RowToDelete + 1) & ":" & UBound(Arr) & ")"))))), Evaluate("COLUMN(" & Cols & ")"))End Function
Code:Function DeleteArrayRow(Arr As Variant, RowToDelete As Long) As Variant Dim Rws As Long, Cols As String Rws = UBound(Arr) - LBound(Arr) Cols = "A:" & Split(Columns(UBound(Arr, 2) - LBound(Arr, 2) + 1).Address(, 0), ":")(0) DeleteArrayRow = Application.Index(Arr, Application.Transpose(Split(Join(Application.Transpose(Evaluate("Row(1:" & (RowToDelete - 1) & ")"))) & " " & Join(Application.Transpose(Evaluate("Row(" & (RowToDelete + 1) & ":" & UBound(Arr) & ")"))))), Evaluate("COLUMN(" & Cols & ")")) End Function
Reply With Quote
Originally Posted by DocAElstein
Bookmarks