Sending a value from a called macro back to a Cellref-CodePudding

This should be super simple but I cannot figure it out. What I am trying to do is have a loop run through 20 cells (F9:F29) and for each one that is populated, it is sending the value to another application to scan for data and determine if that value (an account number) is eligible to participate in a program. Thus, I need G9:G29 to return from the macro "Eligible" or "Not Eligible"

Public Sub Test() 

    Dim myRange As Range 
    Dim Counter As Integer  
    For Counter = 1 To 20         
        If Range("F8").Offset(Counter, 0) > 0 Then         
            Range("F8").Offset(Counter, 0).Select                  
            Call AccountEligible                  
        End If  
    Next Counter   
End Sub


Sub AccountEligible(Cellref as Range)
    
    Dim LastPageCheck As String
    Dim AccountCheck As String
    Dim AcctNumber As String
    Dim iRow As Integer
    Dim AcctType As String
    Dim FromDate As String
    Dim ToDate As String
    Dim Cusip As String
    
    Sheets("Starting Page").Activate
    FromDate = Range("I16").Value       'This assigns the from date in the search filter
    ToDate = Range("I17").Value
    Cusip = Range("I14").Value
    Account = Range(Cellref).Value
    
    
    ' ******** Check for Valid Class Action Data **********
    
    Sess0.ReadScreen NoDataChk, 1, iRow, 4          'check for no data in at all
    If NoDataChk = " " Then
        CellRef.Offset(, 1).Value = "Not Eligible"
        Exit Sub
    Else: CellRef.Offset(, 1).Value = "Eligible"
    End If
End Sub

Am I anywhere close on this? I cut out the middle code interacting with the program and just included the parts where I thought it would make a difference.

The code calling this macro is:

Public Sub Test() 
Dim myRange As Range 
Dim Counter As Integer  
For Counter = 1 To 20         

If Range("F8").Offset(Counter, 0) > 0 Then
Range("F8").Offset(Counter, 0).Select                  
Call AccountEligible                  
End If  

Next Counter            
End Sub

CodePudding user response：

I'd make the check code into a function and just have it return the result:

Public Sub Test()
    Dim c As Range, ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Inputs") 'for example: I don't know what different sheets you're working with
    For Each c In ws.Range("F9:F29").Cells 'use an explcit sheet reference
        If c.Value > 0 Then
            c.Offset(0, 1).Value = AccountEligible(c.Value) 'call the function
        End If
    Next c
End Sub

'make this a Function
Function AccountEligible(Account) As String
    Dim ws As Worksheet
    Dim LastPageCheck As String
    Dim AccountCheck As String
    Dim AcctNumber As String
    Dim iRow As Integer
    Dim AcctType As String
    Dim FromDate As String
    Dim ToDate As String
    Dim Cusip As String, NoDataChk
    
    Set ws = ThisWorkbook.Sheets("Starting Page") 'use an explicit worksheet reference
    FromDate = ws.Range("I16").Value
    ToDate = ws.Range("I17").Value
    Cusip = ws.Range("I14").Value
    '  `Account` was passed in, so use that directly.... 
    
    
    ' ******** Check for Valid Class Action Data **********
    Sess0.ReadScreen NoDataChk, 1, iRow, 4          'check for no data in at all
    
    'return the result
    AccountEligible = IIf(NoDataChk = " ", "Not Eligible", "Eligible")
    
End Function