Create UDF to pass Return Value to iterate in For Loop

[tiridako]

Create UDF to pass Return Value to iterate in For Loop
I would like to use VLOOKUP or Array to perform math based on two cells, and use this return value as the number of time to iterate the For Loop. Do I send the two string value to the Function first, or in Sub sending to the Function?

I have two sheets, one for Entries (information on organizations) and the other for VLOOKUP titled valook. In valook, I have 98 unique categories that describe the organizations, such as they provide food, housing, veteran services, etc. Each service are assigned a number. Say, housing is number 34, and veteran service is number 63. I want to subtract 63 – 34 to get 29, and use 29 to iterate For Loop.

I have studied VLOOKUP and how to use it in VBA, however, I have a hard time understanding the syntax in threads here and on other forums like StackOverflow. I also have a difficult time understanding how to create Function as I have never made one that worked.

Here are some of the codes that I’ve tried to create with little success.

Function mathie()
    Dim Cat As String, Dog As String, horse As String
    
    Dog = 2
    horse = 3
‘ the following code is my educated guess on how to do a vlookup but there is a mismatch error in VBE.
Cat = WorksheetFunction.Match(Sheets("Entries").Cells("Cat", 0), Sheets("valook").Cells("Cat", 2).Value)

   Dim n As Variant
   n = ReturnValue
   aCur = ActiveCell ‘ to match VLOOKUP here?
   preVious = ActiveCell.Offset(0, -1).Select ‘ to match VLOOKUP here?
n = aCur – preVious 
End Function

Sub loopie(n)
   Dim last_col As Long
 last_col = Range("A1").End(xlToRight).row
 Call getIE
     For i = 1 To n
          ActiveCell.Offset(5, 0).Select
          
    Next
    Call waitCheck
End Sub

Sub routine()
    Call loopie(mathie)
End Sub

Here are some of the threads I’ve researched/studied that got me closer to figuring this out, with many other threads and resources over the past few weeks. Putting them all together is what is stumping me because I don’t know what keywords to use to find the one that speaks to what I am trying to do. I have also posted threads on the subject with little help, the last link being the most recent.

I am hoping this post is simple enough to give you an idea of what I’m trying to do without overwhelming the volunteers with long posts. Please let me know your thoughts, thank you.

 

[tiridako]

I managed to figured everything out except object error in VBE! If someone could assist me with understanding why For i to 1 To looking does not work? Here's the actual code I have so far:

Function looking()

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

Set rng = Sheets("valook").Range("A:B")

aCur = ActiveCell
preV = ActiveCell.Offset(0, -1).Select

clm = 2

n1 = Application.WorksheetFunction.VLookup(aCur, rng, clm, True)
n2 = Application.WorksheetFunction.VLookup(preV, rng, clm, True)


it = n1 - n2

ReturnValue = it

End Function

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

For i = 1 To Sheets("Entries").Range("A1").End(xlToRight).row
 
Call getExcel
    Call tabnRight(1)
    For j = 1 To looking
    Call getIE
           Call tabs
    Next
    Call waitCheck
Next
End Sub

I run the Sub, but it is doing absolutely nothing. Any reason why? I tried to look up Function and using function as part of For loop iteration and found nothing on the topic.

 

[JoeMo]

Your function "looking" doesn't return anything. There needs to be a line in that function that provides the return value:

looking = ???

should that be "it"?

 

[tiridako]

So, the ReturnValue should be pointed to "looking" and not "it"?

...
looking = n1 - n2

ReturnValue = looking

End Function

Tested it, didn't work

 

[JoeMo]

So, the ReturnValue should be pointed to "looking" and not "it"?

...
looking = n1 - n2

ReturnValue = looking

End Function

Tested it, didn't work

ReturnValue has no meaning.
looking = n1 - n2

 

[tiridako]

Thank you! Now there are other things that I will need to address as it is still not working. However, the iteration seems to work, I think--it's still not iterating for some reason but it should be. I'll work on it tomorrow to see what else needs addressing. Again, thank you Joe!