Find return value from two assigned value of a text string

[tiridako]

At the most basic, I am trying to find the return value of two subtracted numbers pulled from the assigned value of two different text strings.

Last week, I understood what Functions are, and that Subs do not return a value, and I am still learning how to use functions, and would appreciate your help.

I have rows of varying cells of 102 different categories, and I want to evaluate which categories are used and are given each an unique position number. For example, Child is always in position #3, and Moms are always in position #9. In the spreadsheet, they are in cells AG10 and AH10 respectively. I want to perform a math to do (moms - child) or (9 - 3) to get the return value of 6. I am trying to use this function's return value as a part of a loop in a Call Sub (example: Call tab(returnValue)).

I am not sure which one to use that will work the best: VLOOKUP or Array to assign value to each category. Here are the code I am trying to work with.

Sub valueX()
Dim last_col As String
 last_col = Range("A1").End(xlRight).Row 'Last column

Dim i As Integer
     Dim mov As variant
     Dim cur As variant
     
     mov = ActiveCell.Offset(0, 1).Select ' goes to next cell
     cur = ActiveCell
    
    Call getExcel
    mov
    
     For i = 0 To last_col
     
     If IsEmpty(cur) Then
        Call beep
        Exit Sub

        ElseIf c = "Adults" Then
           ' perform math here somewhere?
           Call dosomething
              Call tab(n)
                Call dosomething
                
        ElseIf c = "Child" Then 'children with disabilities
           ' perform math here somewhere?
           Call dosomething
              Call tab(n)
                Call dosomething
                
                ElseIf c = "moms" Then
           ' perform math here somewhere?
           Call dosomething
              Call tab(n)
                Call dosomething

   ' Do this for all 102 unique categories, unless there's a better way to loop all if--then statements to find out what contains in the cell?                        
     Next

End Sub

Function re(n As Integer) As Integer
    Dim c As Integer
    Dim previous As Integer
    Dim aNext As Integer
        Dim catArr As Variant
      
  catArr = vaLook.Range("B1:C105").Value
  
    previous = ActiveCell.Offset(0, -1).Select  'using previous and aNext to subtract two numbers of text string's assigned value
    aNext = ActiveCell.Offset(0, 1).Select
    
    '  Could I Dim adults = Application.WorksheetFunction.Vlookup("Adults",vaLook,2,0) to get value of 1, and
    ' then Dim Childs for value of 3, and subtract the two numbers to get value of 2?
    ' What else can I do in this code to get return value of n?
    
    n = re(previous - aNext)
      ReturnValue = n
    
End Function

 'Below is the array I set up in case it works better than VLOOKUP.

Sub lottaArray()

c("Adults with Disabilities") = 1
c("Children with Disabilities") = 2
c("Children / Youth") = 3
c("Dads") = 4
c("Grandfamilies") = 5
c("Grandparents") = 6
c("Low-income Families") = 7
c("Men") = 8
c("Moms") = 9

  ' .. other categories ..

   ' VLOOKUP also has it set up like this in two columns as well.

End Sub

Please let me know if you need me to clarify a point or two. The macro I'm developing is complicated, but I am essentially creating a macro to perform hundreds of computer shortcuts (CRTL+V; C; ALT+[Down arrow key], [Space bar], etc. Yes, this does mean I am using SendKeys, but as far as I am aware, the code works beautifully for what I need to do because all I am really doing is taking data from Excel into an intranet web form that will not take it in another way or we'd overwhelm the servers with multiple entries. The html form input type=".." does not work because the type is all shown as "hidden".

Thank you in advance!

 

[tiridako]

I've been working some more on this, and I think I'm getting closer.

To reiterate, I am looking for the ReturnValue of two assigned Integer value of (word)-text strings. Each category, like "mom", "dad", "child" are given their position number. These categories are in a set order that will never change. So, the "child" is given a position #3, and the "mom" is given a position of #9. In each row, user will type the categories that is unique to that organization. The organization probably serves only children, or only moms, or both moms and children but not dads.

Excel spreadsheet example

       ..     AG         AH        AI            AJ
     1       Moms    Seniors   Seniors      Veterans
     2       Child    Dads       
     3       Dads     Moms       Homeless    
     4       Child

Each row does not have a set number of categories as it varies from organization to organization. I have set up a VLOOKUP in a second tab, and Array in VBA, I'm not sure which one is better. I want to be able to give the assigned position number to each category.

Child = 3
Dads = 5
moms = 9
Seniors = 10
Homeless = 11

Then perform a function to subtract (moms - child) to get ReturnValue of 6. Then use 6 as a number of time to loop through another code.

      Sub doSomething()
      
        For i > 0 To (Returnvalue)
         .. do something
          
         Next
      End Sub

Here's my working code, similar to OP:

     Sub daTa()
     
     Dim last_col as Variant
     last_col = Range("A1").End(xlRight).Row ' last column of categories because each row changes the number of categories given
     
     Dim i as Integer
     Dim move as Variant
     Dim cur as Variant

     move = ActiveCell.Offset(0, 1).Select 
     cur = ActiveCell

     .. do something
     move

      For i = 0 To last_col
          If IsEmpty(cur) Then
               Call beep
               Exit Sub
          
          ElseIf cur = "dads" Then              
               ' perform math, child minus dads (5 - 3), get Returnvalue of 2, n = 2
               Call doSomething
             
          ElseIf cur = "Moms" Then
               ' perform math, moms minus dads (9 - 5), get Returnvalue of 4, n = 4
               Call doSomething
             
          ' other ElseIf statements for all 102 categories, is there a better way to loop this? 
         Next
         End Sub

The next bit is Function to find ReturnValue of n, and I have no idea how to pull and put together the math to do it.

       Function n(previous as Integer, aNext as Intger) As Integer      
       Dim c as Integer
       Dim catArr as Variant

      catArr = vaLook.Range("B2:C105").Value 
      
      previous = ActiveCell.offset(0, -1).select
      aNext = ActiveCell

      ' Here are also the arrays; do I use VLOOKUP or Array?    
            c("children") = 3
            c("dads") = 5
            c("moms") = 9   
       '   .. 102 other arrays ..
       
          '  Could I Dim adults = Application.WorksheetFunction.Vlookup("Adults",vaLook,2,0) to get value of 1, and
    ' then Dim Childs for value of 3, and subtract the two numbers to get value of 2?
    ' What else can I do in this code to get return value of n?
    
    ' somewhere around here, match c or VLOOKUP to previous and aNext?

    n = (previous - aNext)
      ReturnValue = n
     End Function

Edited for formatting

 

[tiridako]

I wanted to mention I have looked at the following thread which is present in my code: VBA Array/VLOOKUP question, and I am unclear on how to connect between VLOOKUP and getting the value of n by subtracting two evaluated VLOOKUPs.

       Function n(preVious as Integer, aNext as Intger) As Integer      

       Dim catArr as Integer

      catArr = vaLook.Range("A2:B105").Value 

      Dim aChild as Integer
        aChild = Application.Valook.Match("ChildrenD",vArr,0)  'this will return the position of "childrenD" within vArr 

      preVious = ActiveCell.offset(0, -1).select
      aNext = ActiveCell

    ' somewhere around here, match VLOOKUP to preVious and aNext with catArr?

    n = (preVious - aNext)
      ReturnValue = n
     End Function

However, doing the Dim [name of category] for all 102 entries which is not best practice as far as being able to loop things for repetitive tasks. Us there a way to evaluate/match for each category in the VLOOKUP depending on the other sheet in same workbook?