For loop to capture values in different named ranges

Question

I have been looking through multiple posts on looping through multiple named ranges and returning values to another cell. Unfortunately, I am getting stuck on how to loop through two named ranges to return the value from one named range if a cell in another named range is "X".

Below are images of the named ranges with values and the intended result in cell I46. Please note there is no formula in I46.

Named Ranges:
Range1 Range2

Desired result from macro aftter loop is complete:

Code:

    For Each Cell In wspGen.Range("Ineligible")
    If Cell.Value = vbNullString Then
        LP.zPledge.Value = "Y"
        wspGen.Range("A46") = "-"
        wspGen.Range("AG55").Value = "X"
    Else
        If Cell.Value = "X" Then
            wspGen.Range("AG55").Value = vbNullString
            wspGen.Range("A45").Value = "N"
            LP.zPledge.Value = "N"
            'Copies the corresponding value from range ("IneligibilityCode")
            'if there is an "X" in any of the cells in range ("Ineligible")
            'to I46.  This could be multiple combinations of values in range ("IneligibilityCode")
        End If
    End If
Next Cell

Thank you all for your assistance.

Do you need the same row value from the other named range as where you find the X in the first? If so you can use the .Row where found. If relative positioning then index perhaps. — QHarr
Correct. If "X" is a cell in the the first named range then i would need the corresponding value from the other named range (from image 1). Im still a novice when it comes to VBA, so would you be able to provide a code example for me please? — Zack E

QHarr QHarr · Accepted Answer · 2018-10-03T15:36:01

This is a simple example using a counter variable assuming both ranges are single column and aligned. It counts how many cells down the first named range (set in variable a) it is before x is found and retrieves the value at the same position in the named range b. Note I am using implicit active sheet references and you should specify the sheet name before the named ranges.

Option Explicit
Public Sub test()
    Dim a As Range, b As Range, rng As Range, counter As Long
    Set a = Range("range1"): Set b = Range("range2")
    For Each rng In a
        counter = counter + 1
        If rng = "x" Then
            Range("I46") = b.Cells(counter)
            Exit For
        End If
    Next
End Sub

Space separated list of all matches:

Option Explicit
Public Sub test()
    Dim a As Range, b As Range, rng As Range, counter As Long, outputString As String
    Set a = Range("range1"): Set b = Range("range2")
    For Each rng In a
        counter = counter + 1
        If rng = "x" Then
            outputString = outputString & Chr$(32) & b.Cells(counter)
        End If
    Next
    wspGen.Range("I46")  = Trim$(outputString) ' wspGen.Range("I46")  is defined in your code. This is illustrative.
End Sub

For loop to capture values in different named ranges

1 Answers