• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

Restrict next record to a specific column for a named range

If have a named range and want to loop thru a specific column of the named range is this possible.  Note my defuned name range spans from A1:C300.  I want to loop thru Column A and if it matches then take a value found in the same row from column B.  Am I just better off not trying to leverage off my Defined Name and instead set the range (Col A) from within the code ?

Dim MyRng as Range

For each MyRng in Range("Produce")
  If "Orange" = MyRng THEN
     msgBox = MyRng.Offset(0,1)
 end if
2 Solutions
NorieVBA ExpertCommented:
If Produce is your named range you can loop through column A like this.
For each MyRng in Range("Produce").Columns(1).Cells
    If MyRng.Value = "Orange" THEN
        MsgBox MyRng.Offset(0,1)
   End If
Next MyRng

Open in new window

are you trying to replicate a VLookup() function?
upobDaPlayaAuthor Commented:
aikimark..i am just trying to learn more about applying named ranges within VBA..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now