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

tiridako

New Member
Joined
Feb 2, 2015
Messages
9
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.

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

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,139
Hi
You will get an error if the lookup can't find a match. Try this:

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Where are you passing the variable?
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,139
Hi Norie
I was solving the execution error issue first. Now, passing it:

Code:
Sub call_function()
    tabn looking
End Sub


Sub tabn(lk)
    MsgBox lk
End Sub
 

Forum statistics

Threads
1,136,863
Messages
5,678,205
Members
419,750
Latest member
crp

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