Checking if one string is in a row, and using application.vlookup to retrieve coordinates

Question

Scenario: I am building a code that reads some inputs from a worksheet, find the equivalents in other two worksheets and, if the data is available, performs some operations with it.

Data:

Worksheet 1 (CAs):

name    ident    date       value1    value2
C       xxx      xx/xx/xx    5000      100
T       YYY      xx/xx/xx    1000      101

and so on, there are more than 2 thousand identifiers (ident) in sheets A and B, while in sheet CAs only 10 of those appear.

Worksheet 2 (A): Monthly dates
             Ident 1     Ident 2    ...    Ident N     row number
20/01/01      1            1                  1             2
20/02/01      1            1                  1             3
20/03/01      1            1                  1             4
...
20/12/12      1            1                  1             N-1 
col number    2            3                  N              N

Worksheet 3 (B): Daily dates
             Ident 1     Ident 2    ...    Ident N     row number
01/01/01      1            1                  1             2
02/01/01      1            1                  1             3
03/01/01      1            1                  1             4
...
12/12/12      1            1                  1             N-1 
col number    2            3                  N              N

What the code does: It reads sheet 1, get date and identifier, uses a lookup procedure to find the coordinates of that ident and date in the other sheets and gets the data at the intersection point.

Problem: For some reason, when I input an identifier for my IsInArray function to read, it always return False, despite it surely being in the identifiers arrays.

Question: Any ideas what I am doing wrong here?

Code:

Sub Calculations()

Dim lrow As Long, i_number As String, lastRow As Long, lastCol As Long, datefinalmatch As Long, datefinalmatch2 As Long, z1 As Long, ifinalmatch As Long
Dim lastColLetter As String, a As String
Dim p_number As Variant, amount_number As Variant, aaa As Long, bbb As Long
Dim date_number As Date
Dim wb As Workbook
Dim ilist As Variant

Set wb = ThisWorkbook

    For lrow = 2 To wb.Sheets("CAs").UsedRange.Rows.count

        i_number = wb.Sheets("CAs").Range("B" & lrow).Value
        date_number = wb.Sheets("CAs").Range("C" & lrow)
        p_number = wb.Sheets("CAs").Cells(lrow, 5)
        amount_number = wb.Sheets("CAs").Range("D" & lrow)

        lastRow = wb.Sheets("A").Cells(Rows.count, 2).End(xlUp).Row
        lastCol = wb.Sheets("A").Cells(2, Columns.count).End(xlToLeft).Column
        lastColLetter = Col_Letter(lastCol)
        ilist = wb.Sheets("B").Range("B1:" & lastColLetter & "1")

        datefinalmatch = Application.VLookup(wb.Sheets("CAs").Cells(lrow, 3), wb.Sheets("AMT").UsedRange, lastCol, True) + 1 'row
        If IsInArray(i_number, ilist) = True Then
        ifinalmatch = Application.HLookup(i_number, wb.Sheets("A").UsedRange, lastRow, False) 'column

'other sheets date row finder
        datefinalmatch2 = Application.VLookup(wb.Sheets("CAs").Cells(lrow, 3), wb.Sheets("B").UsedRange, lastCol, True) + 1 'row
        End If
    Next lrow 

End Sub

Private Function IsInArray(valToBeFound As String, arr As Variant) As Boolean
Dim element As Variant
    For Each element In arr
        If element = valToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next element
End Function

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

OBS: The code runs smoothly with no errors. When running it line by line, I see that the function is always returning false, and that is prompting the next row and eventually the end of the code, without it making any actual changes (with I omitted for brevity purposes)

OBS2: There are some variables that are declared but not used. They are used for the calculations in another part of the code. That is why I removed the Option Explicit from the top.

Possible duplicate of [How to search for string in an array](https://stackoverflow.com/questions/10951687/how-to-search-for-string-in-an-array) — , Jan 10 '18 at 13:11
Have you checked if the values are really equal? Look especially to blanks, tabs and other special chars and lower vs upper writing. Also, check the size of the Array (maybe with a `Debug.print` statement). If `valToBeFound` is in the array, it will return true. — FunThomas, Jan 10 '18 at 13:25
@FunThomas Yeah, I am trying to avoid that by running a trial with much less data, only 20*20, where I can see where everything goes. Still the same result for some reason. I am also checking the variable explorer, and the value is there. — DGMS89, Jan 10 '18 at 13:39

score 0 · Answer 1 · answered Jan 10 '18 at 13:12

0

Why don't you try the following function?

Sub Test()
  Dim arr As Variant
  arr = Split("abc,def,ghi,jkl", ",")
  Debug.Print IsInArray("ghi", arr)
End Sub

Looks that your function is not working properly. How to search for string in an array

answered Jan 10 '18 at 13:12

Thanks for the answer. That was the first post I came to when searching for this issue. I also tried the function proposed on the first answer of that post. Same result. – DGMS89 Jan 10 '18 at 13:16

sporc · Answer 2 · 2018-01-10T13:59:35.297

-1

Instead of your "IsInArray" Function you could use something like this:

    '...

    'If IsInArray(i_number, ilist) = True Then
    Set cell = wb.Sheets("B").Range(Cells(1, 2), Cells(lastcol, 1)).Find(What:=i_number, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not(cell Is Nothing) Then
        ifinalmatch = Application.HLookup(i_number, wb.Sheets("A").UsedRange, lastRow, False) 'column
    End If

    '...

edited Jan 10 '18 at 13:59

answered Jan 10 '18 at 13:37

sporc

387
1
4
14

Thanks for the answer. Although the trial run is done with a simplified data sample, the final run will contain lots of data, so I am aiming to use arrays when possible, and avoid selections. – DGMS89 Jan 10 '18 at 13:40
You also make a "selection" with `ilist = wb.Sheets("B").Range("B1:" & lastColLetter & "1")`, thought it would be the same. Regarding lots of data i think the solution `For Each element In arr` is slower than `Selection.Find`, but i am not sure about this – sporc Jan 10 '18 at 13:44
1

@sporc The OP is specifically talking about `.Select` which is the selection he refers to. Pointing to a range is not a "selection". See this post for more information. It is bad practice to ever use or recommend `.Select` or `.Activate` in any Excel VBA code: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – tigeravatar Jan 10 '18 at 13:47
Okay, I wasn't aware of this. I edited the `.Activate` and `.Select`. Maybe it's better this way.. – sporc Jan 10 '18 at 13:59

Checking if one string is in a row, and using application.vlookup to retrieve coordinates

2 Answers2