0

So I have created multiple arrays that include various info (states, names, etc). Using these arrays, I need to check to see if the values match to a fixed spreadsheet. So doing so for the names array I do within a loop:

If InStr(Worksheets("Import Spreadsheet").Cells(x, 2), Names(c)) > 0 Then......

And it works perfectly fine. But once I now check using the states array it no longer works. For whatever reason excel is not recognizing it as a string (I think). I tested this theory by hard coding an example. I would take an array value that I know should satisfy the if statement on a given cell and it did not work. So:

If InStr(Worksheets("Import Spreadsheet").Cells(1, 4), State(1)) > 0

TO

If InStr(Worksheets("Import Spreadsheet").Cells(1, 4), "TX") > 0

The first statement would not work even though I know State(1)= TX, but the second if statement worked. Any reasons this might be happening? Both arrays were initialized and filled the same way

EDIT: This is how the arrays are populated within a for loop:

ElseIf IsEmpty(Cells(i + 2, 1)) Then         
Names(c) = Names(c - 1)                   
Hours(c) = Cells(i + 2, 2).Value            
State(c) = Cells(i + 2, 4)
c = c + 1
Else                                                        
Names(c) = Cells(i + 2, 1)                    
Hours(c) = Cells(i + 2, 2).Value            
State(c) = Cells(i + 2, 4)

I know that the values in each array are correct because I printed them all out beforehand.

Fernando Toledo
  • 1
  • 1
  • Do `debug.print "#" & State(1) & "#"` to ensure that it comes out #TX#. – dcromley Sep 28 '22 at 00:48
  • When I do this it displays TX, the correct state in this scenario – Fernando Toledo Sep 28 '22 at 02:06
  • There's some other piece of information we don't have - there should be no functional difference between your second and third code samples if State(1) is actually "TX". Perhaps you can show more code, including how `State` is populated. – Tim Williams Sep 28 '22 at 05:53
  • Just added that – Fernando Toledo Sep 28 '22 at 12:35
  • Just before `If InStr(Worksheets("Import Spreadsheet").Cells(1, 4), State(1)) > 0` add `State(1) = "TX"` does the `Instr` now succeed? If Yes then your cell value is not "TX" but something that *looks* like "TX". If it still doesn't work then we're still missing something, and the only way to move ahead would be if you could share a workbook which replicates the problem. – Tim Williams Sep 28 '22 at 18:08
  • InStr now succeeds when I add that line, which confirms my suspicion. What I don't understand is how it can be something that looks like "TX" because when I print it manually it returns state(1) returns "TX" – Fernando Toledo Sep 28 '22 at 18:18
  • How about replacing all `instr(string1,string2)` with `instr(1,string1,string2)` ? I always have that first parameter. – dcromley Sep 29 '22 at 14:47
  • Loop over the string and print out each character and its ASCII code. `Dim i As Long: For i=1 to Len(State(1)): Debug.Print Mid(State(1), i ,1), Asc(Mid(State(1), i ,1)): Next i` Check the output is "T 84 X 88" – Tim Williams Sep 29 '22 at 18:30
  • It correctly returns that as the output – Fernando Toledo Oct 03 '22 at 23:00

0 Answers0