VALUE error - Cannot pass vlookup result to array items

whitehawk81

Board Regular
Joined
Sep 4, 2016
Messages
52
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:

Code:
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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

whitehawk81

Board Regular
Joined
Sep 4, 2016
Messages
52
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:
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

Board Regular
Joined
Sep 4, 2016
Messages
52
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:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Why are you converting the value(s) in the array after the Vlookup code?
 
Last edited:

whitehawk81

Board Regular
Joined
Sep 4, 2016
Messages
52
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top