First Code for last few posts
Code:
' https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-11.html#post4555023 https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-12.html#post4555457 https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-12.html#post4561126 https://www.excelforum.com/tips-and-tutorials/1136702-vba-range-insert-method-excel-oop-syntax-error.html https://testosteronesboosterweb.com/titan-blast/
Option Explicit
Dim rngCopy As Range, rngNew As Range
Dim dicLookupTableMSRD As Object
Sub MeOwl() ' https://www.excelforum.com/development-testing-forum/1215283-gimmie-ta-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u.html#post4822550
' Arbritrary Test Range Clear and Refresh
Rows("1:20").Clear
Dim RngObj1Area As Range
Set RngObj1Area = Range("B2:J10")
Let RngObj1Area.Interior.Color = vbYellow
' List of Range.Insert parameter argument options
Set dicLookupTableMSRD = CreateObject("Scripting.Dictionary") 'Late Binding MSRD In this case Dictionary and Scripting.Dictionary are the same. You can be sure of that because removing the reference to the Scripting runtime makes the Dictionary code fail. When you declare a variable as Dictionary, the compiler will check the available references to locate the correct object. There is no native VBA.Dictionary incidentally, though it is of course possible to create your own class called Dictionary, which is why I used the phrase "in this case". https://www.excelforum.com/excel-programming-vba-macros/1147313-dictionary-or-scripting-dictionary-binding-referencing-dim-ing-sub-routines-and-function.html#post4431231 http://www.eileenslounge.com/viewtopic.php?f=30&t=24955#p193413 https://www.excelforum.com/excel-programming-vba-macros/1135724-compare-and-find-missing-rows-in-two-sheets-with-different-headings-and-formats.html http://advisorwellness.com/blue-fortera/
Let dicLookupTableMSRD.CompareMode = vbTextCompare
dicLookupTableMSRD.Add Key:=-4121, Item:="xlShiftDown or -4121: Shifts cells down." ' XlInsertShiftDirection https://powerspreadsheets.com/excel-vba-insert-row/#Insert-Rows-with-the-RangeInsert-Method
dicLookupTableMSRD.Add Key:=-4161, Item:="xlShiftToRight or -4161: Shifts cells to the right." ' XlInsertShiftDirection
dicLookupTableMSRD.Add Key:=0, Item:="xlFormatFromLeftOrAbove or 0: Newly-inserted cells take formatting from cells above or to the left." ' Default .. xlInsertFormatOrigin Enumeration https://powerspreadsheets.com/excel-vba-insert-row/#Insert-Rows-with-the-RangeInsert-Method
dicLookupTableMSRD.Add Key:=1, Item:="xlFormatFromRightOrBelow or 1: Newly-inserted cells take formatting from cells below or to the right." ' xlInsertFormatOrigin Enumeration
' Range to be copied to Clipboard. CHANGE TO EXPERIMENT
Set rngCopy = Range("D4:E5")
' Set rngCopy = Rows("4:5")
Let rngCopy.Interior.Color = vbRed
' Function call to return Demo Array to paste out into a Worksheet to demonstrate the Range Property Item arguments for both the two and one argument case, with the two argument case demonstrating the option of using a column Letter for the second argument in that two argument option
Let RngObj1Area.Value = RangeItemsArgumantsSHimpfGlified(RngObj1Area)
Columns("B:J").AutoFit
End Sub
' Simplified one Liner Function
Public Function RangeItemsArgumantsSHimpfGlified(RngOrg As Range) As Variant
Let RangeItemsArgumantsSHimpfGlified = Evaluate("=" & """RngItm(""" & "&" & "(Row(" & RngOrg.Address & ")" & "-" & "Row(" & RngOrg.Item(1).Address & ")" & ")+1&" & """, """"""" & "&" & "MID(ADDRESS(1,COLUMN(" & RngOrg.Address & ")-COLUMN(" & RngOrg.Item(1).Address & ")+1),2,(FIND(""$"",ADDRESS(1,COLUMN(" & RngOrg.Address & ")-COLUMN(" & RngOrg.Item(1).Address & ")+1),2)-2))" & "&" & """"""") &(""" & "&" & "(Column(" & RngOrg.Address & ")-Column(" & RngOrg.Item(1).Address & "))+1+" & "(((Row(" & RngOrg.Address & ")" & "-" & "Row(" & RngOrg.Item(1).Address & ")" & ")+1-1)*" & RngOrg.Columns.Count & ")" & "&" & """)""")
End Function
Bookmarks