VBA Find Partial String In Array And Output Found String Array Value?

Mar 31, 2014

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:


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.

View 2 Replies


Treating String As Array And Correct Array Format For Unicode Characters?

Jul 30, 2012

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.

View 1 Replies View Related

Search Substring Of Array Matching List Of String From Another Array?

Dec 20, 2013

I need to export this to Xcelsius which doesn't support any macros/vba. Btw I can;'t use Row() in xcelsius too.


View 4 Replies View Related

String Array Values To Array Of User-Defined Types

Oct 2, 2008

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:

View 4 Replies View Related

Find If Text Cell Contains A String Of Characters From Array In Different Workbook

Aug 12, 2011

I've scoured the internet for this answer and have not found a formula that works.

I have a list of domain names/URLs in column A of workbook 1:


I have a list of domain name extensions in workbook #2:


I want to determine if the cells in column A workbook one contain any of the strings in workbook 2 - note that I need to look at the string, not the entire cell in workbook one. If there is a match, true, if not, false for that cell within workbook one.

It's sort of a reverse array lookup with text. It seems that cross workbook and text is what causes the challenge.

View 9 Replies View Related

VLookup - How To Find Partial Match Within Larger String

Apr 9, 2014

I'm trying to lookup a 10 digit number against a string of numbers seperated by commas. And then return the Carrier Name and On-Time

Order #(s) Carrier Name On-Time
5082940535,5082940507 Freight Lines Yes
5083055781,5083056150,5083056098 Ocean X No

Order # Carrier Name On-Time
5082940535 ? ?
5083056150 ? ?
5082940507 ? ?
5083056098 ? ?

View 5 Replies View Related

Find Value In Array Based On Partial Input From User

Feb 22, 2013

I am trying to find a value in an array based on partial input from a user.

The user will enter the last four digits of a number.

Then the formula should find the number in a list and return that complete number

I will explain using example.

Here is the list of numbers (array):


User input= 3233

formula output = 3003283233

View 3 Replies View Related

VBA To Output From Array Based On Variable In One Element Of Array

May 2, 2013

I'm only starting to get to grips with arrays. I have what I consider to be a lot of data that I need to 'cut' into separate workbooks. I have written some code that does this by simply looping through each line, 250k+, checking against a variable and copying the row into a separate sheet. This took longer than it would have doing it manually. It was suggested to me that I use arrays to speed up the process. I have managed to store the test data into an array but am struggling to find a way to loop through and pull out an entire 'row' from the array based on a variable. I have looked for 2 days in various places to find some way to loop through the data held in the array, but to no avail.

That code will appear here from about 8am GMT tomorrow. I know that once I've cracked this I'm on the road to some very significant time saving and comprehensive report writing.

View 9 Replies View Related

Finding String In Array

Aug 8, 2013

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.

View 9 Replies View Related

If String Equals Value In Array?

Sep 26, 2013

I'm trying to verify if a cellvalue is available in an array. Here is the code:

First I tried to check if the cellval is in the array by returning a -1, 0 or 1:

If UBound(txt, cellval) = 0 Then
-> returns zero for value "h3", but also for value "3"
(where cellval the value in the cell is)
(with txt being the array containing "h3,r8,ty657", etc)

But I need only the If to be true when there is an exact match.

View 2 Replies View Related

Array As String And Integer Together

Sep 19, 2007

I have defined array say like this:
Dim myarray (1 to 5, 1 to 2)

Is possible to define something like this?
myarray (x, 1) as string
myarray (x, 2) as integer

x is anything between 1 to 5 .

View 9 Replies View Related

Split String Into Array

Sep 20, 2008

Say I have a string, "a test array." I want to split this into an array where each character is an element. I was thinking something like

Dim MyArray As Variant
Dim MyString As String
MyString = "a test array"
MyArray = Split(MyString, "", Len(MyString), vbTextCompare)

But this doesn't work because Split() returns the entire string when the delimiter is a zero-length string.

View 9 Replies View Related

Passing Array Of UDT With String To C++ Dll

Feb 17, 2009

I'm trying to pass an array of UDT from Excel to a C++ dll but I have no idea how. My UDT looks like this

Private Type Student
Age As Integer
Name As String
StudentID As String
End Type
And my dll function declaration..

Declare Function RegisterStudent Lib "SimpleDLL.dll" _
(arr() as Student) As String
.. which will return a unique Id in string

View 9 Replies View Related

Sort String Array

Nov 18, 2006

I have a string (PreString) that gets its values from a procedure call (PreResult). The string consists of 4 values for each loop which I then split into the y array.

I then want to transpose the array to the worksheet for sorting.

I don't really get the transpose to work as I want to. I want every 4:th y to be printed on a new row (x). I don't really know how to use the Ubound function to get it right.

Should I make a 2 dimensional array instead ? If so, how is that made ?

Dim x As Integer, z As Integer
Dim var_Status As Integer
Dim var_Week As String
Dim var_HoursPerWeek As Integer
Dim PreString As String

i = var_StartWeekNr
j = var_RangeNumberOfWeeks
z = 0
x = 0
var_Status = 0
var_Week = ""
var_HoursPerWeek = 0
PreString = ""

View 8 Replies View Related

Find String Within Worksheet & Handle Error If Not Found

Jun 17, 2008

this line

Cells. Find(What:=searchterm, After:=Cells(1, 1), MatchCase:=False).Activate 'find pn that is stored in searchterm

causes a runtime error 91 when there are no results. "Object variable or with block variable not set"

Ideally, when there are no results, I would like to enter a loop, where I could provide a drop down list of fuzzy matches, but I am not sure excel can do fuzzy matches. I'd settle for just highlighting the row and moving on... any help?

Option Explicit

Sub Macro1()

ActiveSheet.Cells.Find(What:="AES", After:=Cells(1, 1), MatchCase:=False).Activate 'find the aes part number column
Dim aescolumn 'create variable for index value of aes column
aescolumn = ActiveCell.Column 'set variable to index number

View 3 Replies View Related

VBA To Search String And Insert Row If String Not Found

Apr 11, 2013

I have a spreadsheet which has "Employee: [agent 1 name]" in column A and it may or may not have the word "Break" in the same column before it mentions "Employee: [agent 2 name]". The amount of data between agent 1 and agent 2 varies and am needing code which will insert a row above "Employee: [agent 2 name]" if "Break" is not found, and add the word "Break" in column A on the inserted row. I would need this to loop through the spreadsheet until all 100+ agents have been searched.

I'm also needing this done for the word "Meeting" and would insert a row 2 rows above the next agent.

View 6 Replies View Related

If Text Found In String Return String

Jan 31, 2008

Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.

View 3 Replies View Related

Extract Data From A String Array And Sum

Nov 9, 2009

I want to extract data from array string and then sum the values. For reference attaching the excel.

View 14 Replies View Related

Split String In Collection Into 2D Array

Jul 30, 2013

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

: Item 2 : "Spam|01/07/2013|1" : Variant/String
: Item 4 : "Chips|01/07/2013|2" : Variant/String

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).

View 6 Replies View Related

Excel VBA - Check If String Is In Array?

Sep 2, 2013

I have a test to see if a text string is in an array that mostly works, but sometimes fails and I don't understand why.

The routine calls a Function IsInArray which returns a True/False value depending on whether the search term is in the array.

Sub TestFilterArray()
MyArray = Array("a", "b", "c")
If IsInArray("a", MyArray) = False Then


In this case the first item in my array is "a - b" (note that this is a text string ), but for some reason because my test value is "a" the routine things it must be in the array.

Thus I am thinking that the ampersand and dash symbols are somehow confusing my routine.

How can I change my routine so that it correctly identifies which text strings are in my array?

View 9 Replies View Related

Converting String To Byte Array

Apr 18, 2007

I have a function that will be used across 10K+ records at a time, so I was taking a look at optimizing it a bit. At the start of the routing I have a string with a 9 to 15 digit numeric value in it. I wanted to load each individual digit into a byte array. I started out using:

byVal = StrConv(sVal,vbFromUnicode)
But that actually loads the ASCII Character codes into the array not the digits themselves. From there I considered looping through the array applying CByte(Chr$(byVal(i))) but then I wondered if it would make more sense to just load the array with Mid$(sVal,i,1)... Does anyone have tested knowledge on which performs better? Is there an option C I have missed?

View 9 Replies View Related

VBA Sort String Array Function

Oct 1, 2008

I have a string that I need to sort.
Below code works beautifully but doesn't sort numbers, any advice to get this to sort numbers as well?

Function Alphabetize(ByVal sText As String) As String
Dim sWords() As String, sTemp As String
Dim i As Long, j As Long, n As Long

'-- clean up text
For i = 1 To Len(sText)
Select Case Mid$(sText, i, 1)
Case " ", "a" To "z", "A" To "Z"
Case Else: Mid$(sText, i, 1) = " "
End Select
'-- remove leading and trailing spaces....................

View 9 Replies View Related

Array Formula Referencing A String

Mar 11, 2009

I'm having a problem with the below array. The column heading in cells E7:H7 Never change and i would like to sum up the appropriate data below those headings according to the what is in cell D4. The formula in cell A9 works but only if the string is exactly the same as what is listed in cell D4. Is there a way to reference cell D4 like i have in cell B9. I'm looking to get the formula in B9 to work, but i'm missing something.


ABCDEFGH2Assumptions 3Collateral Sample 1{60;90;FCL} MACRO Populates 4Collateral Sample 2{90;FCL} {60;90;FCL} 5Collateral Sample 3{FCL} 6Collateral Sample 4{60;90;FCL} 7 Current6090FCL8 990 123410200 658711360 4682212
Spreadsheet FormulasCellFormulaA9{=SUM(IF($E$7:$H$7={60;90;"FCL"},$E9:$H9))}B9{=SUM(IF($E$7:$H$7=$D$4,$E9:$H9))}A10{=SUM(IF($E$7:$H$7={60;90;"FCL"},$E10:$H10))}B10{=SUM(IF($E$7:$H$7=$D$4,$E10:$H10))}A11{=SUM(IF($E$7:$H$7={60;90;"FCL"},$E11:$H11))}B11{=SUM(IF($E$7:$H$7=$D$4,$E11:$H11))}Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

View 9 Replies View Related

Declare String As Array Index

Mar 28, 2007

is it possible to declare an array or anyother datatype like the following in VBA?

Dim myArray() As Integer
myArray("A") = 0
myArray("Test") = 0
myArray("G") = 0

after checking some conditions i need to change the corresponding values too, like the following,

If mycondition = True Then
myArray("Test") = 1
End If

View 9 Replies View Related

Transpose And Split String Into An Array

Jun 8, 2007

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

Array(0) = "App Alg FMA",
Array(1) = "App Pgm FMA",
Array(2) = "App Slf FMA",

View 2 Replies View Related

Formula To Indicate When Text String Appears In Array

Jun 30, 2014

I have a long list of terms, and I need to identify which of these terms appear in another body of text strings. Rather than searching for each individual term, is there a formula that check the terms for me automatically.

The terms will appear in the larger body of text, but they won't appear on their own, so they won't be perfect matches.

I've attached a sheet to try to illustrate the problem.

Term Checker.xls‎

View 5 Replies View Related

Covert Space Delimited String To Array

Apr 24, 2008

I have two columns of data, one for Position, and one for description.

The positions are concatenated and separated by spaces. E.g. C1 C2 C3 for each Description. I need to create a separate row for each position

So I need to convert:

Pos Decscription
C1 C2 C3 Cap 100n 10% X7R 0805


Pos Decscription
C1 Cap 100n 10% X7R 0805
C2 Cap 100n 10% X7R 0805
C3 Cap 100n 10% X7R 0805

Or in other words split a space delimited string into an array, so that I can loop through the array, and add new rows

View 9 Replies View Related

Text String To Single Character Array?

Jul 13, 2012

There is a method to use ROW(1:10) within an array formula to strip a string into individual elements e.g.

HELLO > {"H","E","L","L","O"}

I cannot find it anywhere, thought it was something like =MID(A1,ROW(1:10),1) but not yielding results.

View 4 Replies View Related

Excel 2007 :: Create String From Array?

Jul 23, 2012

I have this formula, ( which i found the basis of on a You tube video and Richard Scholar was accredited with improving the soloution)


This forumla generates a number for each player, the higher the number the more inline they are to get a game

Problems are this works for the 29 weeks of this year but more weeks need added to the end of the year as we get there. Can i generate the array numbers from a formula and shorten.

This is a sample data ignore row 2( just a count of players) and data actually goes back to 6th Jan

Excel 2007BCDEFGHIJ1Wk21Wk22Wk23Wk24Wk25Wk26Wk27Wk28210101010101010103Player 1111104Player 210001105Player 300101016Player 4010101107Player 50110118Player 600009Player 7111000110Player 8011100011Player 9000012Player 101011113Player 11101114Player 121010115Player 13016Player 141010017Player 15118Player 160101Sheet1

View 7 Replies View Related

VBA How To Read Cells Into Variables/array And Use In String

Apr 15, 2009

I made the formula work great, however I want to cut it down and simplify it and make it easier to manipulate... as opposed to having the filename written every so many times.. replace with a loop instead.... so:

Private Sub Weeklytestt_Click()
Dim varCurDate As String
If DateYesterday.Value = True Then Answer = "1"
If DateToday.Value = True Then Answer = "2"
If DateTyped.Value = True Then Answer = "3"

Select Case Answer
Case Is = "1"
varCurDate = Format(Date, "yyyymmdd")
varCurDate = varCurDate - 1
Case Is = "2"
varCurDate = Format(Date, "yyyymmdd")..........

View 9 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved