?
Solved

Restrict next record to a specific column for a named range

Posted on 2014-03-09
3
Medium Priority
?
176 Views
Last Modified: 2014-03-12
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)
else
 end if
0
Comment
Question by:upobDaPlaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 34

Accepted Solution

by:
Norie earned 1600 total points
ID: 39916255
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

0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 400 total points
ID: 39917237
are you trying to replicate a VLookup() function?
0
 

Author Closing Comment

by:upobDaPlaya
ID: 39925543
aikimark..i am just trying to learn more about applying named ranges within VBA..
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question