VBA excel: How do I compare a string array to a string?

Question

I have a script that takes the contents of a cell, and puts the first 2 characters of the cell into a string array. I need to later compare that string array to a string, but I can't seem to get that to work. Here's what I have:

For i = 2 To 600
colStr = Sheets("smartlist").Cells(i, "A").Value
If colStr <> "" Then
    ReDim charArray(Len(colStr) - 1)
    For j = 1 To Len(colStr)
        charArray(j - 1) = Mid$(colStr, j, 1)
    Next
    strArray = LCase(charArray(0)) & LCase(charArray(1))

    If CStr(Join(strArray)) = CStr(Join(pwArray)) Then

Now, I've tried:

If charArray = "ab"

If Join(charArray) = "ab"

If CStr(Join(charArray)) = "ab"

I'm pretty lost at this point. Any suggestions would be welcome!

Edit: added the whole function up until I get the 'Type mismatch'

You loop with the var i but use j to index your array in the for loop -> is this a typo or where does j come from? — DAXaholic
– DAXaholic, Commented Jul 8, 2016 at 12:44
@DAXaholic typo when I moved it over, fixed. When I debug, I can see that charArray looks to be correctly holding the values in the cells, it's the comparison I'm having issues with. — Chuck
– Chuck, Commented Jul 8, 2016 at 12:49
Just to be sure that I understood it correctly: you want to check if "ab" is one of the elements of the array? — DAXaholic
– DAXaholic, Commented Jul 8, 2016 at 12:50
I'm not quite sure I understand why you're doing this. Why not use spreadsheet formulas? You could cut a lot of the coding work by using formulas then grind the data with VBA later. — user1945782
– user1945782, Commented Jul 8, 2016 at 12:54
@DAXaholic I want to check if the letters combined make "ab" i.e. charArray(0) is "a", and charArray(1) is "b" — Chuck
– Chuck, Commented Jul 8, 2016 at 12:56

DAXaholic · Accepted Answer · 2016-07-08 13:00:03Z

2

You could use Join(charArray, "") - without "" it joins the elements with space so the result of your initial try was "a b"

answered Jul 8, 2016 at 13:00

DAXaholic

35.9k6 gold badges84 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user1945782 · Accepted Answer · 2016-07-08 13:11:21Z

0

Firstly, you really need to clarify what you're doing. You say that later you need to check what's in the string. If that is the case, then you don't need an array, you simply need another string...

Dim chars As String
chars = Left$(cellToTest, 2)

Later you can test more simply using the InStr function, like so...

Dim loc As Integer
loc = Instr(colStr, chars)
If loc > 0 Then
    ...

If you want to turn this into a function on your spreadsheet you can use the following in the cells as formulas...

=LEFT(A1, 2)
=SEARCH(A3, A1)

Here's a little screen shot of what I mean...

answered Jul 8, 2016 at 13:11

user1945782

2 Comments

Chuck Over a year ago

By 'later' I was referring to the comparison further down in the code, but this is EXACTLY what I needed, thank you!

user1945782 Over a year ago

No problem, Chuck. Despite the fact that it's not brilliant, the help (F1) with formulas in Excel may be a good place to start.

Collectives™ on Stack Overflow

VBA excel: How do I compare a string array to a string?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related