Pass value from function to be used as the number of time to iterate For loop

[tiridako]

The iteration's value is the number of times I want to loop an action. This value is sent from a function, and for some reason, it is not looping as it should. Here's my working code.

Function looking()

Dim it As Integer
Dim n1 As String, n2 As String, aCur As String, preV As String, rng As Range, clm As Integer
Dim n4 As Variant, n3 As Variant

Set rng = Sheets("valook").Range("A:C")
clm = 3
n1 = ActiveCell
n3 = Application.WorksheetFunction.VLookup(n1, rng, clm, False)

n2 = ActiveCell.Offset(0, -1).Select

If ActiveCell = ("empty") Then
ActiveCell.Offset(0, 1).Select
    looking = n3
          Else
          n4 = Application.WorksheetFunction.VLookup(n2, rng, clm, False)

The last line of code above gives me issues, returns as "Unable to get the VLookup property of the WorksheetFunction class, any idea what's going on? The n3 has no issue finding the property.

            looking = n3 - n4
End If

End Function

Sub tabn(looking)
     Call getIE
    Call tabs
End Sub

Sub itera()
Dim i As Integer, j As Integer, n As Integer

i = 1
i = 0
n = i + 1

' For n = 1 To '
Do While Not IsEmpty(Range("A1").Offset(0, 1))
Call getExcel
                   ' For j = 1 To looking(n)  ' ' I was going to use Function looking here to loop, but would it perhaps work better as a call?
                    '
                              Call tabn(looking)
            ' Next
    Call waitCheck
    Call getExcel
    Call tabnRight(2)
    Loop
End Sub

I looked for the solutions online, and came across the fact that I can call a variable like "Call tabn(looking) and it seems to almost want to work but not quite. Let me know your thoughts. Thank you!

 

[Worf]

Hi
You will get an error if the lookup can't find a match. Try this:

Function looking()
Dim it%, n1$, n2 As String, aCur$, preV$, rng As Range, clm%, n4, n3
On Error GoTo 0
Set rng = Sheets("valook").Range("A:C")
clm = 3
n1 = ActiveCell
n3 = Application.WorksheetFunction.VLookup(n1, rng, clm, False)
ActiveCell.Offset(0, -1).Activate
n2 = ActiveCell
If ActiveCell = "empty" Then
    ActiveCell.Offset(0, 1).Activate
    looking = n3
Else
    On Error Resume Next
    n4 = Application.WorksheetFunction.VLookup(n2, rng, clm, False)
    If Err.Number <> 0 Then
        MsgBox "Could not find " & n2
        looking = 1E-44         ' arbitrary value
        Exit Function
    End If
    looking = n3 - n4
End If
End Function


Sub call_function()
MsgBox looking
End Sub

 

[Norie]

Where are you passing the variable?

 

[Worf]

Hi Norie
I was solving the execution error issue first. Now, passing it:

Sub call_function()
    tabn looking
End Sub


Sub tabn(lk)
    MsgBox lk
End Sub