VALUE error - Cannot pass vlookup result to array items

[whitehawk81]

Hi,

I'm trying to create a keyword search function, that uses a named range called "kwmap". It creates a string array from the found keywords and maps them with vlookup to the corresponding number (solution ID). After that it should compare the amounts of each ID and set the result to the ID with the highest count. But unfortunately I'm stuck by the step, when I try to pass the vlookup result to the array items.
This is the code I wrote so far:

Function Kwrd(Words As Range, strText As Range)Dim c As Range


For Each c In Words
If InStr(1, strText, c, 1) > 0 Then Kwrd = Kwrd & "," & c
Next c
If Kwrd = 0 Then
    Kwrd = "-"
Else
    Kwrd = Right(Kwrd, Len(Kwrd) - 1)
    
mapKw Kwrd


End If
End Function


Sub mapKw(ByVal Kwrd As String)
Dim wb As Workbook
Dim SolNum As Variant
Dim myArray() As String
Dim solArray() As Long
Dim myRange As Range
myRange = wb.Worksheets("Legend").Range("kwmap")


myArray = Split(Kwrd, ",")


ReDim myArray(1 To UBound(myArray))


For i = 1 To UBound(myArray)

'non of these seem to work
    myArray(i) = Evaluate("VLOOKUP(myArray(i), myRange, 2, False)")
    'myArray(i)= Application.WorksheetFunction.VLookup(myArray(i), myRange, 2, False)
     
'pass array items to integer array


    'solArray(i) = myArray(i)


Next i


End Sub

I get a VALUE error and it seems to have a problem with my named range, though I don't know why.

 

[whitehawk81]

So I modified the script a bit and created another subroutine to test if the array items are setup correctly. I could print out the array item as a string, but couldn't pass it to the vlookup.
Here is the current code:

Sub stringToArray(ByVal Kwrd As String)
Dim w As Variant
Dim myArray() As String
Dim SolNum As Variant


myArray = Split(Kwrd, ",")


'ReDim myArray(1 To UBound(myArray))


For Each w In myArray


'kwToID (w) ------------That was the test


If w <> vbNullString Then
    SolNum = Application.WorksheetFunction.VLookup(w, kwmap, 2, False)
    w = SolNum
    
End If


Next w


ReDim myArray(1 To UBound(myArray))


End Sub
Sub kwToID(ByVal w As String)
Dim SolNum As Variant


'MsgBox w ------------That actually gave the correct string


If w <> vbNullString Then
    SolNum = Application.WorksheetFunction.VLookup(w, kwmap, 2, False)


'MsgBox SolNum -------but that wasn't working


End If
End Sub

 

[whitehawk81]

I changed the column in the vlookup function to look for strings, and it works fine with that, but when I change it back to look for numbers, I get the value error again. I even tried to convert the array to long, but it still doesn't work.
Here is the modified code:

Sub mapKw(ByVal Kwrd As String)Dim i As Long
Dim myArray() As String
Dim solArray() As Long
myArray = Split(Kwrd, ",")


Dim myRange As Range
Set myRange = Range("kwmap")


For i = LBound(myArray) To UBound(myArray) 


   'myArray(i) = Application.WorksheetFunction.VLookup(myArray(i), myRange, 2, False)  ----->  that works fine
   solArray(i) = Application.WorksheetFunction.VLookup(myArray(i), myRange, 3, False)   -----> not working
   
   'myArray(i) = CLng(myArray(i))   ------> tried to convert the array to long here
   'myArray(i) = solArray(i)     -------> here too with another method


Next i


End Sub

 

[Norie]

Why are you converting the value(s) in the array after the Vlookup code?

 

[whitehawk81]

Because this keyword search function should return the ID number of the keyword string, which has the highest count in the searched text. But now I consider to make a 2 dimensional array from the keyword range and perform the lookup in that.