Sub BubblesIndexIdeaWay As Sub Bubble , just replacing name in three places
This is Thread post 23 , forum post #post17885
https://www.excelfox.com/forum/showt...ll=1#post17885
https://www.excelfox.com/forum/showt...age2#post17885
……. the API, which knows nothing of BSTRs, assumes it is a pointer to an LPSTR (or LPWSTR) ……..
……In the case of passing strings, a pointer is passed, which has the effect of working similar to ByRef in a normal VBA function…..
VB Strings and win32API
This post is based on research and some initial investigations. It will be followed by more development to support and further the knowledge.
For the sake of brevity we will use the convention of historical reference misnomers:
_ ANSI for the 1 Byte, 8 Binary Bit character encoding for the first 256 characters as defined in the Windows code page, and
_ Unicode for the 16 bit 2 Byte Little Endian encoding used by Microsoft to implement Unicode for the first 65536
Unicode or (Microsoft) ANSI?
API ANSI considerations
Let’s get one thing clear: deep in the innards, VB and VBA strings are Unicode. But some aspects of the innards workings of the API Functions ( the API functionAs that most people are familiar with) , do most all things in ANSI, as perhaps do a few other Microsoft things. Perhaps this makes sense, in a broader sense. API controls window things, in one way or another, and a lot of things involving strings goes on, messaging etc. All this can easily be done with things like Askjuszff982jw33i()%, etc.
Historically all API functions exclusively in their string workings used ANSI.
This plays a part in the issue/ problem sparking off these rhetoric musings. So things ANSI will also be considered, since their consideration helps with the final solution
(VBA) Runtime Unicode to ANSI Transfer
In simple terms, VB, albeit having Unicode 16 behind the scenes, only understands ANSI characters
There seems to be no official documentation to the following anomaly. ** However, this sounds reasonable: VB strings are stored in UNICODE, but all API calls are made with ANSI strings. This is accomplished by converting any string passed to an API call to ANSI before the call and back to UNICODE afterwards. While this conversion is transparent to the user most of the time, it makes it impossible to pass a UNICODE string from VB to a DLL via an argument typed As String in a Declare statement. Similarly, any structure which contains strings will go through the double conversion process during an API call. https://i.postimg.cc/28bPrbNj/Runtim...API-string.jpg
https://i.postimg.cc/28bPrbNj/Runtim...API-string.jpg
We note here, just briefly for now, that for most all the original the API functionAs a later functionW. This goes some way, unclear how much, to make the API function work more with Unicode. Possibly this is just some data, such as that likely to be passed / carried in it.
VB Strings Object
Thinking about a VB Strings as an object, comes partly from hindsight, as I think it helps get to grips with some of the other goings on further down in these rhetoric musings
A string variable itself is usually a "pointer", something like an address ( usually made with 32 bits ), of ( or usually part of, the left hand side start of ) the memory used to hold the actual string.
This variable is often referred to as a data type called BSTR, which is short for Basic String. A BSTR is, in fact, a 32 Bit pointer to a null-terminated Unicode character array (that is preceded by a 4-byte length field).
The thing pointed to and often "got", in a manner of speaking, the "object", will be something like a 1 dimensional array, where the elements are:
For ANSI: a bit close to an array of the characters we might be as layman familiar with, in
their character form,
or
their decimal encoding.
What we see or get, may depend on how we ask…… Example https://eileenslounge.com/viewtopic....297329#p297329 : StrConv("ZAC", vbFromUnicode) - coerced a string into a byte array
For Unicode: something similar….. maybe.
….The Unicode character array that is pointed to by a BSTR must be preceded by a 4-byte length field and terminated by a single null 2-byte character (ANSI = 0).
There may be additional null characters anywhere within the Unicode character array, so we cannot rely on a null character to signal the end of the character array. This is why the length field is vital.
Again, the pointer points to the beginning of the character array, not to the 4-byte length field that precedes the array.
https://postimg.cc/Xpgwq1Ny
Attachment 6147 https://i.postimg.cc/Xpgwq1Ny/ANSI-U...ter-arrays.jpg
https://i.postimg.cc/Yq58XTJ8/ANSI-U...ter-arrays.jpg
LPSTR LPWSTR LPTSTR data types
There is a pseudo data type, LongPtr which has, or will adjust itself appropriately, to the format required for the pointer of a string variable. This can be used to store the pointers as shown in the above Penis Pointer diagram. This is likely to be useful based on the tip above…. ….. the API, which knows nothing of BSTRs, assumes it is a pointer to an LPSTR (or LPWSTR…..) …..
In documentation we distinguish three pointer types that can be stored in the LongPtr : An LPSTR string is defined as a pointer to a null-terminated ANSI character array. However, because the only way that we can tell when an LPSTR string ends is by the location of the terminating null, LPSTRs are not allowed to contain embedded null characters. Similarly, an LPWSTR is a pointer to a null-terminated Unicode character set with no embedded nulls. (The W in LPWSTR stands for Wide, which is Microsoft's way of saying Unicode.) )
Pointers in general seem to be flexible things, and we have a LPTSTR which will effectively view the final character string as appropriate: LPTSTR, designates a general type that can be compiled for either Windows code pages or Unicode.
VarPtr( ) strPtr( )
We can get the pointer from a string (the address of the real UNICODE string buffer), using the strPtr function. It seems to be one of those important historical facts, not so well publicised, that this was introduced to help overcome problems when Unicode was introduced around the 95/97 VB 4/5. All VB strings are stored in UNICODE, but all API calls are made with ANSI strings. This is accomplished by converting any string passed to an API call to ANSI before the call and back to UNICODE afterwards. While this conversion is transparent to the user most of the time, it makes it impossible to pass a UNICODE string from VB to a DLL via an argument typed As String in a Declare statement.
Unicode strings are converted to ANSI strings when using the declare statement as Win 95 didn't do Unicode.
Code:
Sub VBptr() ' https://stackoverflow.com/questions/47499525/why-is-the-result-of-varptrbyval-str-the-same-as-strptrstr-vb6/47500197#47500197
Dim BSTR As String ' BSTR is a 32 Bit pointer to a null-terminated Unicode character array (that is preceded by a 4-byte length field).
' VarPtr( ) returns the starting address of the memory area in which a variable is stored
' VarPtr(ByVal ) - Strings passed ByVal pass the address of the first character of the containing string in the BStr.
' StrPtr( ) does the same, but makes sure it eturns the address of the real UNICODE string buffer
Debug.Print VarPtr(BSTR); VarPtr(ByVal BSTR); StrPtr(BSTR); StrPtr(ByVal BSTR)
' 1307644 0 0 0
Let BSTR = "Alan"
Debug.Print VarPtr(BSTR); VarPtr(ByVal BSTR); StrPtr(BSTR); StrPtr(ByVal BSTR)
' 1307644 141549884 141549884 141549884
' So VarPtr(BSTR) is the address of the Pointer. In that is the address of the first character, which VarPtr(ByVal ) should get, and might, and StrPtr most likely will. There isnt one if the variable is not filled
Dim Poynter As Long, Trget As String, Rslt As Variant
Let Poynter = VarPtr(BSTR)
VBGetTarget Trget, Poynter, LenB(BSTR)
Dim ByteArr() As Byte
'Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Trget) ' "A" & Chr(0) & "l" & Chr(0) & "a" & Chr(0) & "n" & Chr(0)
Let ByteArr() = Trget
Call DBugPrntArr(ByteArr()) ' {65, 0, 0, 0, 108, 0, 0, 0, 97, 0, 0, 0, 110, 0, 0, 0}
Debug.Print BSTR, Trget ' ?? - Trget might be anything A l a n
End Sub
_._________________
It looks like we may be getting at some tools to assist us in the issues…. Let's see if we can find some more
_.______________________________
Some Tools for my research
_1 StrPtr(), and LongPtr – see last bits above
_2 Byte() Type Arrays and strConv()
……
A Unicode string, the final array object, is just a memory buffer so we can hack it. Which is what The StrConv Hacker Function
does…. This, and the Byte() Type Arrays in general , give both interesting and useful results, as well as a being great tools for researching the issues under consideration,…….
…… next post
Ref
https://web.archive.org/web/20201201...t/tips/varptr/ https://stackoverflow.com/questions/...-strptrstr-vb6
https://web.archive.org/web/20201113...rconv-function
** Page 35, Steven Roman's book, Win32 API Programming: when we pass a string to an external function, VB translates the string from Unicode to ANSI
Sub Call_Sub_BubblesIndexIdeaWay()
Calling routine required for previous Post and following Post
Code:
Sub Call_Sub_BubblesIndexIdeaWay() ' Partially hard coded for ease of explanation
' data range info
Dim WsS As Worksheet: Set WsS = ThisWorkbook.Worksheets("Sorting")
Dim RngToSort As Range: Set RngToSort = WsS.Range("B11:E16")
' Set RngToSort = Selection ' ' Selection.JPG : https://imgur.com/HnCdBt8
Dim arrTS() As Variant ' This is somewhat redundant for this version and could be replaced by arrOrig()
Let arrTS() = RngToSort.Value
' Index idea variables
Let arrOrig() = arrTS()
Let arrIndx() = arrTS()
Let Cms() = Evaluate("=Column(A:D)") ' Convenient way to get
Let Rs() = Evaluate("=Row(1:6)") ' Initial row indicies
' Add initial indicies
Let RngToSort.Offset(-1, 0).Resize(1, 4).Value = Cms(): RngToSort.Offset(-1, 0).Resize(1, 4).Font.Color = vbRed
Let RngToSort.Offset(0, -1).Resize(6, 1).Value = Rs(): RngToSort.Offset(0, -1).Resize(6, 1).Font.Color = vbRed
' Initial row indicies from full original range´of rows
Dim strRows As String, Cnt As Long: Let strRows = " "
For Cnt = 1 To 6
Let strRows = strRows & Rs(Cnt, 1) & " "
Next Cnt
' we should have now strRows = " 1 2 3 4 5 6 "
Call BubblesIndexIdeaWay(1, arrIndx(), strRows, " 1 Asc 3 Asc 2 Asc ")
' Call BubblesIndexIdeaWay(1, arrIndx(), strRows, " 1 Asc ")
' Call BubblesIndexIdeaWay(1, arrIndx(), strRows, " 1 Asc 3 Asc ")
' Demo output
Dim RngDemoOutput As Range: Set RngDemoOutput = WsS.Range("B31").Resize(RngToSort.Rows.Count, RngToSort.Columns.Count)
' Let WsS.Range("B31").Resize(RngToSort.Rows.Count, RngToSort.Columns.Count).Value = arrIndx()
Let RngDemoOutput = arrIndx()
Let RngDemoOutput.Offset(-1, 0).Resize(1, 4).Value = Cms(): RngToSort.Offset(-1, 0).Resize(1, 4).Font.Color = vbRed
Let RngDemoOutput.Offset(0, -1).Resize(6, 1).Value = Rs(): RngToSort.Offset(0, -1).Resize(6, 1).Font.Color = vbRed
End Sub
2 Attachment(s)
Some Killing File tests / What order does Dir(with wild cards) find stuff
Some notes , tests in support of this
https://www.excelforum.com/excel-pro...-the-file.html
These are just some notes and tests into what order the Dir( with wild cards ) thing does stuff.
Introduction
VBA Dir Function thing ( https://docs.microsoft.com/en-us/off...p/dir-function )
In the simplest form, ….._
_____ Dir(Fullpath&FileName, __ )
_............
Code:
Dim IsFileName As String
IsFileName = Dir("C:\MyFolder\myFileName.xls", __ )
this basically gives you the file name back if it exists, based on you giving it the full path and File name string, Fullpath&FileName.
In the above example, if you had the file "myFileName.xls" in the foilder, "MyFolder", then the text "myFileName.xls" would be Placed in variable , IsFileName
If that file does not exist, then it gives you back nothing, or rather an empty string of sorts “” ( I believe Dir is a throw back to older early computer days, when you typed something like Dir C:_____, and the result was that you got to go to that place which Dir C:_____ represented )
It seem that in VBA the Dir is mostly used to loop through all files in a single folder*. ( *It does not suit too well for use in coding looking at all files in folders and sub folders ). The suitability of the Dir function for this is based on a couple of things.
_(i) In Microsoft Windows, Dir supports the use of multiple character (*) and single character (?) wildcards to specify multiple files. ......
You can use wild cards in the full path and file name string that you give it, so it will look for a file matching your given string. ( So typically you might give a string like “C:\myFolder\*.xl*”, which would look for any excel file: In this bit *.xl* , the first * allows for any file name, and the second * will allow for extensions such as .xls , .xlsm, .xlsx, .. etc… )
__Dir _____ without arguments_(ii) After you use like Dir(Fullpath&FileName, __ ) once, then any use after of just _ Dir __ without any arguments, will give the next file it finds based on the criteria given by the wild carded full path and file name string you gave in the first use with arguments, or it returns "" if there are no further files meeting the criteria given by the wild carded full path and file name string you gave in the first use with arguments
What this post is about:
My interest was sparked by the reference thread ( https://www.excelforum.com/excel-pro...-the-file.html )
I am interested in finding out which of the files Dir or Dir(Fullpath&FileName, __ )will choose if there are more than 1 file meeting the criteria of a string , Fullpath&FileName , containing wild cards
Experiments so far
I made a test folder , named "Folder"
Folder.JPG : https://imgur.com/l9OwlQi
Attachment 2213
I created my files in this order
_1 “wbCodes.xlsm” --- the main file with all the codes in it. This is in the same Folder as the folder which I named "Folder" ( The main Folder is called “Kill Stuff” : Kill Stuff Folder.JPG : https://imgur.com/hN26AoW )
After making the main File, I created the folder, "Folder" , and created the following files in it. I created the following three files in the following order,
_2 “SecondFirstAfterwb.xlsx” --- made first after making “wbCodes.xlsm”
_3 “ThirdSecondAfterwb.xlsx” --- made second after making “wbCodes.xlsm”
_4 “AForthThirdAfterwb.xlsx” --- made third after making “wbCodes.xlsm”
I modified the codes from Alf and sintek from the referenced Thread, thus, ( I am mainly interested in the first part of the routines, as this deals with what the Dir chooses )
Code:
Sub zed369() ' sintek
Dim Path As String, File As String, Cnt As Long
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
' sintek's Dir Stuff
'Application.ScreenUpdating = False
Set wb1 = ThisWorkbook: Set ws1 = wb1.Sheets("Sheet1")
Path = ThisWorkbook.Path & "\Folder\": File = Dir(Path & "*.xl*") ' For this example, specific file is in a folder called Folder...same path as macro file...
Debug.Print "First got by Dir is " & File
Debug.Print
For Cnt = 1 To 3 - 1 ' -1 because we have three files, but typically the first is got from the first use of Dir , which is typically outside the loop
File = Dir: Debug.Print " use " & Cnt & " in loop of Dir gives " & File
Next Cnt
Debug.Print
Debug.Print
' sintek's way to do stuff
'Stop ' _____________________________________________________________________________
'Set wb2 = Workbooks.Open(Path & File): Set ws2 = wb2.Sheets("Tabelle1")
'With ws2
' .UsedRange.Copy ws1.Range("A1")
'End With
'wb2.Close
'Kill Path & File
'Application.ScreenUpdating = True
End Sub
'
Sub CopyAndKill() ' Alf
Dim aString As String, Cnt As Long, aStringToOpen As String
' Alf's Dir stuff
'aString = Dir("N:\a_test\")
aString = Dir("F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\Kill Stuff\Folder\")
Debug.Print "First got by Dir is " & aString
aStringToOpen = "F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\Kill Stuff\Folder\" & Dir("F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\Kill Stuff\Folder\")
Debug.Print "First file will be opened using this string " & vbCrLf & aStringToOpen
Debug.Print
For Cnt = 1 To 3 - 1 ' -1 because we have three files, but typically the first is got from the first use of Dir , which is typically outside the loop
aString = Dir: Debug.Print " use " & Cnt & " in loop of Dir gives " & aString
Next Cnt
Debug.Print
Debug.Print
'Stop ' __________________________________________________________________
' Alf's way to do the stuff
'Workbooks.Open ("N:\a_test\" & Dir("N:\a_test\"))
'Sheets("Sheet1").Activate
'ActiveSheet.UsedRange.Copy
'ThisWorkbook.ActiveSheet.Range("A1").PasteSpecial Paste:=xlAll
'Application.CutCopyMode = False
'Windows(aString).Close
'Kill ("N:\a_test\" & Dir("N:\a_test\"))
End Sub
I get this sort of output ( in the immediate window )
Code:
First got by Dir is AForthThirdAfterwb.xlsx
use 1 in loop of Dir gives SecondFirstAfterwb.xlsx
use 2 in loop of Dir gives ThirdSecondAfterwb.xlsx
First got by Dir is AForthThirdAfterwb.xlsx
First file will be opened using this string
F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\Kill Stuff\Folder\AForthThirdAfterwb.xlsx
use 1 in loop of Dir gives SecondFirstAfterwb.xlsx
use 2 in loop of Dir gives ThirdSecondAfterwb.xlsx
Initially it appears that I get alphabetic order.
But possibly there could be more to it than that.
I will look again at this in a few days , possibly on some other computers and systems, and experiment with various settings , etc….
In the next posts I will use this simplified routine which is only interested in looking at the order in which Dir chooses files.
Rem 1 gives a few ways to get the string up to and including the Folder in which files are to be searched for, ( in the form below , ‘1b ) , is used to get the folder named “Folder” in the same folder as the workbook in which the routine is placed )
Rem 2 : As before, an initial use of Dir(C:\somewhers\kjhfkhs.*sdfjkah,___) is made to set the search criteria, followed by the un argumented Dir in a loop which then looks for the next files
Code:
Sub DirOrder()
Dim strWB As String
Rem 1 get the full string, strWB, for a Folder to use in the Dir(Fullpath&FileName, __ ) ( strWB=Fullpath&FileName - FileName )
'1a) use the asking pop up thing, File dialogue folder picker
' With Application.FileDialog(msoFileDialogFolderPicker)
' .Title = "Folder Select"
' .AllowMultiSelect = False
' If .Show <> -1 Then
' Exit Sub
' Else
' End If
' Let strWB = .SelectedItems(1) ' & "\"
' End With
'
'1b) Using a test Folder, named Folder in the same Folder as the workbook in which this code is
Let strWB = ThisWorkbook.Path & "\Folder"
'1c) Hard code instead
'Let strWB = "F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\Kill Stuff\Folder"
Debug.Print "Folder used is" & vbCrLf & strWB & vbCrLf & "" & Right(strWB, (Len(strWB) - InStrRev(strWB, "\", -1, vbTextCompare)))
Debug.Print
Let strWB = strWB & "\"
Rem 2 add last file bit for use in the Dir(Fullpath&FileName, __ ) , but include wild cards... http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11089&viewfull=1#post11089 : _(i) You can use wild cards in the full path and file name string that you give it, so it will look for a file matching your given string. ( So typically you might give a string like “C:\myFolder\*.xl*”, which would look for any excel file: In this bit *.xl* , the first * allows for any file name, and the second * will allow for extensions such as .xls , .xlsm, .xlsx, .. etc… ) _(ii) After you use like Dir(Fullpath&FileName, __ ) once, then any use after of just _ Dir __ without any arguments, will give the next file it finds based on the string you gave in the first use with arguments
'2a) Excel files
Let strWB = strWB & "*.xls*"
Dim file As String: Let file = Dir(strWB)
Debug.Print "First got by Dir(" & strWB & ")" & vbCrLf & "is " & file
Debug.Print
Dim Cnt As Long
For Cnt = 1 To 3 - 1 ' -1 because we have three files, but typically the first is got from the first use of Dir , which is typically outside the loop
file = Dir: Debug.Print "Use " & Cnt & " in loop of unargumented Dir gives " & file
Next Cnt
Debug.Print
Debug.Print
End Sub
This would be comparible output ( in the Immedite Window ( http://www.eileenslounge.com/viewtop...247121#p247121 ) ) to the test files anf folder used so far
HTML Code:
Folder used is
F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\Kill Stuff\Folder
Folder
First got by Dir(F:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsdWbADOMsQueery\Kill Stuff\Folder\*.xls*)
is AForthThirdAfterwb.xlsx
Use 1 in loop of unargumented Dir gives SecondFirstAfterwb.xlsx
Use 2 in loop of unargumented Dir gives ThirdSecondAfterwb.xlsx
And here is what it looks like in the explorer window:
ExpOrder1.JPG : https://imgur.com/OfQfHeH
Attachment 2224
1 Attachment(s)
Deafault item to use if empty column
In support of answer to this excelfox Thread:
http://www.excelfox.com/forum/showth...ll=1#post11090
Code:
Option Explicit
Sub DefaultItem()
Rem 1 data range info
Dim rngIn As Range, Lr As Long, ClmCnt As Long
Let ClmCnt = 3 ' : Let ClmCnt = Worksheets("Sheet2").Range("A1").CurrentRegion.Columns.Count
Let Lr = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
Set rngIn = Worksheets("Sheet2").Range("A1:C" & Lr & "")
Rem 2 Data to array
Dim arrDtaIn() As Variant ' I need Variant type as the .Value in the next line returns a field of Variant type elements
Let arrDtaIn() = rngIn.Value
Rem 3 Determine default values
' 3a) Number of groups
Dim arrGp() As Variant: Let arrGp() = Application.Index(rngIn, 0, 1).Value ' http://www.excelfox.com/forum/showthread.php/1111-VBA-Trick-of-the-Week-Slicing-an-Array-Without-Loop-%E2%80%93-Application-Index Highlight arrGp and Hit F9.JPG : https://imgur.com/PZF0oXE
Dim strGps As String: Let strGps = " " ' For a string like " 1 2 3 "
Dim cnt As Long
For cnt = 2 To Lr ' looking at all rows from the second in our input data
If InStr(1, strGps, " " & arrGp(cnt, 1) & " ") = 0 Then ' This looks for the positiopn along ( starting from character 1 , in strGps , of each row element arrGp(cnt, 1) ) if it is not found then Instr retourns 0 as a n indication that it was not there
Let strGps = strGps & arrGp(cnt, 1) & " " ' Because it is not there, we now put it in
Else
End If
Next cnt
' At this point we should have like strGps = " 1 2 3 "
' 3b) Array of unique groups
Let strGps = Trim(strGps) ' This takes off the first and last trailing spaces
Dim arrGps() As String ' The string split function below returns a fiels of String elements : Highlight arrGps Hit F9.JPG : https://imgur.com/LT9dgHk
Let arrGps() = Split(strGps, " ", -1, vbBinaryCompare) ' this splits the ( strgps , using " " as denominator , and returns all elemants in an array, using exact binary computer match on the " " )
' 3c) Array for output
Dim arrOut() As String ' A dynamic array is needed as I can only use variables in the ReDim method - I cannot use varable in the declaration (Dim) statement
ReDim arrOut(1 To UBound(arrGps()) + 2, 1 To 2) ' I want +1 rows for the header I also need +1 because split retouns a 1 dimensional array stating at indicie 0 - so the Ubound of arrGps() will give a numbe 1 less than I might expect - in our example we have 3 elements with indicies of 0 1 2, ( and values in our example of 1 2 3 - for example arrGps(0)=1 ) so the Ubound returns 2 - but we want 3 elements
' 3d) fill my arrOut()
Dim Stear As Variant ' I want to use a For ´Each loop below VBA must have an object varaible or a variable of variant type to hold each item in a collection of something. Our arrGps() can be considered a collection of numbers 1 2 3
Dim ArrOutRw As Long: Let ArrOutRw = 1 ' Our row number in the outout array : I use 1 initially, for the header
Let arrOut(ArrOutRw, 1) = arrDtaIn(1, 1): Let arrOut(ArrOutRw, 2) = "Deafault item"
For Each Stear In arrGps() ' This outer loop goes throug each unique group number =============== - For each number in { 1, 2, 3 }
For cnt = 2 To Lr ' An Inner loop to go through all data rows ' -----------------------------
If CStr(arrDtaIn(cnt, 1)) = CStr(Stear) Then ' This will catch the first use of our group number, Stear is our group number taken from the array 1 2 3
Let ArrOutRw = ArrOutRw + 1 ' Our next row to fill in arrOut()
Let arrOut(ArrOutRw, 1) = Stear ' First column in our output array
Let arrOut(ArrOutRw, 2) = arrDtaIn(cnt, 2) ' Second column in our output array will be given the first item in column B of our data for this group number, Stear
Exit For ' I only want to get the first item for a group number
Else
End If
Next cnt ' ----------------------------------------------------------------------------------
Next Stear ' ====================================================================================
' at this point we have an array for output of default : Select ArrOut then Hit F9.JPG : https://imgur.com/CNMeYV9
Rem 4 Demo Output
Let rngIn.Offset(0, ClmCnt).Resize(UBound(arrOut(), 1), 2).Value = arrOut() ' In the range which offset to the right of the input, of the dimension size of the output array, I paste my values out
End Sub
