[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Restrict next record to a specific column for a named range

Posted on 2014-03-09
3
Medium Priority
?
183 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 35

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’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

656 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