I set out this morning to count duplicates in an array and report a succinct list.... so I went down the route of using a collection with keys to do this.
1. Is there a better way? Else
2. split my collection values? I'm a bit stuck.
I have a series of values in a collection like this
So I have 3 key pieces of data delimited by a pipe sign -
Product | Date | Number of Occurrences
Item 4 is of interest to me because it occurs twice.
How I can turn those collection values into something I can work with, else another approach to sum rows in an array which are duplicates (if you only look at 2 columns).
I have a string which follows this format: "App Alg FMA", "App Pgm FMA", "App Slf FMA" This string can shrink or grow dynamically depending on the number of App*'s selected. How can I split this string and load the App*'s into an Array so the array would contain the following
I am trying to do a sort of index match thing using VBA. What I am attempting to do is to use the prefix of a long number and try to find that exact prefix in a string array, and output that string array value. So this is what I have so far as a test:
[Code].....
So I can match the text exactly so if I put PREFIXB in cell A1 in this example, i will get the msg box saying "YES", but if I make it PREFIXB1231k4j3jj1kj32cj, it will display "NO". I want to get it so that PREFIXB will be displayed in the cell that I put the formula in. So if A1 = "PREFIX1AAA100CF" and cell B1 = "=ABC(A1)", cell B1 will display "PREFIX1AAA".
Now the thing is that these prefixes can have different lengths, but will never encompass the exact prefix of another. So if I had a prefix of: PRE1AB, I won't have a prefix of PRE1A.
in C a string is nothing more than an Array of characters ending with a null character.
in VBA this does not seem to be the case.I am trying to use the BlowFish code from David Midkiff for some encryption, however the code sometimes fails:
When encrypting a string a string of a specific length should be returned. however sometimes one of the characters of the encrypted string is a null character, and the returned encrypted string (with a embedded null character) is causing me problems. I want to check for the null character and if present redo the encryption. But how do I check for the presence of this null character in a unicode (double-byte) string?
Checking with Len(encrypted) gives the correct length (!) but trying to move each (unicode)character into an array fails when using the Mid() function past the null character in the string.
Now I can use
byteArray() = StrConv(unicodetext,vbFromUnicode)
to get the lower byte of each character into an array easily, but how do I do this for both bytes of the unicode characters?
Using an integer array does not work other than through
intArray(j) = CInt(AscW(Mid(Outp, j, 1)))
which fails on the nullstring in my encrypted text.
I have tried with variants but so far no luck. I thought if the string was entered into a variant then I could also address the variant as an array of bytes, but that does not seem to be accepted by VBA.
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:
I'm stumbling upon something I just can't figure out. I thought I was being very clever using the Split() function, and it did all work beautifully for a while. I've got a list sort of like this:
Helloworld Hello of World Hello1 of Planet And I want to be able to run the following code.
For Each u In units d_type_a = Split(u.Value, " of") Debug.Print (d_type_a(0))
Select Case u.Value Case "Hello" stuff... Case "Helloworld" stuff... Case "Hello1" stuff... End select next
But it fails on the Debug.print with a "Subscript out of range, nr 9" error. The debug is just there because it doesn't want to work. The debug.print DOES out put "Helloworld" into the Immediate window but then still fails which sorta confuses me. The best I can figure out is that it just doesn't quite like the string without an " of" in it.
I have a column with cells that look like: XYZ - JobABC - Area 1-A. I'd like to split the column into to and have it look like: XYZ <next column> Job ABC - Area 1-A Normally I'd use the text to columns function and "-" as delimiter, however, there are other instances of "-" which I do not want separated. Note that the XYZ can vary in length and therefore fixed width will not work either. I have attached a small sample with the different variations that I could encounter.
In filed I have couple of value separated by comma like below:
A1 header1 B1 header2 C1 header3
Audi 592035, 579733, 653749, 579735 20 000
If my macro found that string (always will be separate by ",") should split the string and add rows (= to number of string). The output should be as below:
A1 header1 B1 header2 C1 header3
Audi 592035 20 000
[Code] .....
I have:
Code: Set sourceWb = ActiveWorkbook Set ws = sourceWb.Worksheets(1)
Code: Sub SplitApart() Dim data As String data = Sheets(1).Cells(20, 1).Text For Each EachSplit in Split(data) n = n + 1 Sheets(1).Cells(20, n + 1) = EachSplit Next End Sub
Error code when Debugging on "Split":
"Wrong Number of arguments or invalid property assignment"
I need to open a text based file (not a txt file), read the data in as a String. Split the string and paste into a worksheet. The string really has 2 delimitors a & and a =.
Ideally, I would like to keep only what was between the = and the &. But would settle for splitting the string by &, then putting token to left of = in one column and token to right of = in the next column. The first sounded cleaner, but I'm getting desparate.
This didn't sound hard when I started, but I've never really done VBA before and it has been posing a much greater problem than I thought. I've tried several things, but this is the latest rendition. Am I thinking too much in terms of C++ and Java? ...
I am having trouble figuring out how to split the data in a column.
For Example on the attached .xls - the first few records under the SIC column are:
73790200 59470104 70110100 581223
& how i want them to look in seperate columns:
73 790200 59 470104 70 110100 58 1223
I need the first two digits in their own column and the rest of the number in it's own column. I have over 6,000 records, so doing it manually is not a place I want to go!
I have a spreadsheet with Approx 900 rows of information that has been entered incorrectly.
Spreadsheet has 6 columns. Contact Name Contact Number Company Name Company Account Number Order Date Order Numbers
Every Order should have its own line - However I have approx 900 rows where the order numbers have multiple entries instead of single entries.
All the order numbers end "LO" and there all 8 digits long.
I wanted to know if its possible to use excel to look for all instances of "LO" in the column Order Numbers and delete the original Row and replace it with 3 rows with the same information.
Example: Attached to this post!
I have 15 historic files each approx 35000 rows and I suspect there are more errors
My code below returns a type mismatch? It is looking at the values in column B which are formatted as text and the output is in column J. An example of a value is 2.1.15 I want to extract 1 (i.e. the central character between two ".").
VB: Sub ConvertLineNo()
Dim r As Long, TempStr As String For r = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -2 TempStr = Cells(r, "B") TempStr = Split(TempStr, ".") Cells(r, "J").Value = TempStr Next End Sub
is there a function that will split a text string based on a symbol. I know how tyo use left and right, which are based on a set number of characters but I want to split based on a "/" mark. whats to the left of the "/" mark and whats to the right of the "/" mark. any ideas. an exaplme is: tom / tim. i want a formula that will put the word "tom" in a cell and another formula that will put "tim" in another cell.
I have used this code below to split a large excel file into multiple sheets from matching column data, but now I need to split it by a partial match (set number of characters from the beginning) from beginning of the column data.
For Example:
[Code]....
So with the code provided below using column 3 I would get 10 different sheets since none of the data in the column is identical. I want to modify the code (or come up with new code) so I can set the number of characters to compare from the beginning of the data in the set column and split into sheets based on that. So if I set it to the first 4 characters in column 3 I would receive only 5 sheets sheets: Safe, Fail, Dont, Poop, & 21-4.
What are the modifications or new code needed for this? I have searched for a bit with no luck, just keep finding code to check the full cell data for matches in a set column like this code I have:
I have another problem with this damn address file. Column H and I have data in them that is often mixed. As shown below, I have used A and B below, but its normaly in Column H and I. I would be greatul if some could write a macro to split the data into the two columns.
Rows 2-8 is what Im presented with. I would like them to look like 11-17
Note that the number in row 8 does not match, so is left for manual intervention....
I have list of strings to be searched in column A2 to A150 (A1 has column title). The array that needs to be searched is in B2 to AG1000 (B1 to AG1 has column title). I want each string in column A to be searched in the full array. If match is found the corresponding column title (B1 or C1 and so.) and cell address needs to written to a new result columns in AY and AZ. it should work for duplicates as well i.e. the string can be in all the columns of array from B to AG. and all of them needs to be written to result column.
