Redim doesn't work after script was run successfully

[whitehawk81]

Hi there,
I managed to run the script only once without error. But now I get a value error and it seems that the Redim is not resizing the arrays.
Originally I used Redim Preserve but after the error I also tried to resize them at the initialization, but neither of them seems to work now.
Here is my code:

Function Kwrd(strText As Range) As VariantDim c As Range
Dim i As Long
Dim sID As Long
Dim sCount As Long
Dim solArr() As Long
Dim countArr() As Long
Dim Words As Range
Dim myRange As Range
Set Words = Range("kw")
Set myRange = Range("kwmap")


On Error GoTo Hell


'ReDim solArr(1 To UBound(solArr))
'ReDim countArr(1 To UBound(countArr))
    
For Each c In Words
    If InStr(1, strText, c, 1) > 0 Then
    
    '----------------found keywords are mapped and replaced with solution ID
    'ReDim Preserve solArr(i)
    solArr(i) = Application.WorksheetFunction.VLookup(c, myRange, 3, False)


    i = i + 1
   
    End If
Next c


For i = LBound(solArr) To UBound(solArr)
If solArr(i) <> 0 Then
'----------------same values are counted
    For sID = 1 To 10
    'ReDim Preserve countArr(sID)
        countArr(sID) = CountArray(solArr, sID)
    Next sID


'----------------result is set to ID with max value
    Kwrd = FindMax(countArr)


End If
Next i


Hell:
Kwrd = "No keyword found."


End Function
Private Function CountArray(Arr() As Long, ToFind As Long) As Long
Dim i As Long
    For i = LBound(Arr) To UBound(Arr)
        If Arr(i) = ToFind Then
            CountArray = CountArray + 1
        End If
    Next
End Function
Private Function FindMax(Arr() As Long) As Long
Dim myMax As Long
Dim i As Long
  
  For i = LBound(Arr) To UBound(Arr)
    If Arr(i) > myMax Then
      myMax = Arr(i)
      FindMax = i
    End If
  Next i
End Function

 

[Norie]

If you rerun code then everything should be 'reset'.

Where are you getting the error(s)?

What happens if you remove this?

On Error Goto Hell

PS Putting something like that in the code isn't going to help, in fact it could hinder as it might hide things.

 

[whitehawk81]

If I remove the On Error statement I get #VALUE ! error.
Actually I put this part there, because there are some cells that don't give any value back and for that I only wanted to give back a string.

 

[Norie]

If you want to handle the VLookup not returning a result don't use On Error...

Use something like this.

Function Kwrd(strText As Range) As Variant
Dim c As Range
Dim i As Long
Dim sID As Long
Dim sCount As Long
Dim solArr() As Long
Dim countArr() As Long
Dim Words As Range
Dim myRange As Range

Dim Res As Variant

    Set Words = Range("kw")
    Set myRange = Range("kwmap")

    'ReDim solArr(1 To UBound(solArr))
    'ReDim countArr(1 To UBound(countArr))

    For Each c In Words
        If InStr(1, strText, c, 1) > 0 Then

            '----------------found keywords are mapped and replaced with solution ID
            'ReDim Preserve solArr(i)
            
            Res = Application.VLookup(c, myRange, 3, False)
            
            If Not IsError(Res) Then
                solArr(i) = Res
            Else
                solArr(i) = "Not found"
            End If

            i = i + 1

        End If
    Next c

    For i = LBound(solArr) To UBound(solArr)
        If solArr(i) <> 0 Then
            '----------------same values are counted
            For sID = 1 To 10
                'ReDim Preserve countArr(sID)
                countArr(sID) = CountArray(solArr, sID)
            Next sID


            '----------------result is set to ID with max value
            Kwrd = FindMax(countArr)


        End If
    Next i

End Function

That code uses Application.Vlookup and a variant variable, Res, to avoid the runtime error that could occur if the lookup failed.

 

[whitehawk81]

Thanks for the help! I also reinstated the Redim Preserve for the arrays and now it works fine. Now I only get a value error for this part:

Else                solArr(i) = "not found"
            End If

I also tried to set the solArr to Variant but it still gives a value error, even if I just set it to a number.

 

[Norie]

This is just an example, I don't know what you want to put in the array if the lookup fails.

solArr(i) = "not found"

Perhaps you just want a 0?

solArr(i) = 0

 

[whitehawk81]

Yes, I set it to 0 now and even changed the second for loop by checking for isempty:

For i = LBound(solArr) To UBound(solArr)        
If IsEmpty(solArr) Then
            KwSearch = "not found"
        Else
            '----------------same values are counted
            For sID = 1 To 10
                ReDim Preserve countArr(sID)
                countArr(sID) = CountArray(solArr, sID)
            Next sID

            '----------------result is set to ID with max value
            KwSearch = FindMax(countArr)
        End If
    Next i

but I still get value error, where the vlookup doesn't return a result.

 

[Norie]

Where are you getting the 'value' error and why are you using IsEmpty(solArr)?

 

[whitehawk81]

I only get the 'value' error when the function doesn't find any keywords in the selected range, therefore the vlookup fails.
I used IsEmpty(solArr) to see if the array captures any value after the vlookup, but in this case the code stops at the vlookup.

 

[Norie]

If you used what I suggested you would not get an error when the vlookup didn't find a key word.