Solved

Restrict next record to a specific column for a named range

Posted on 2014-03-09
3
159 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
3 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 400 total points
Comment Utility
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 45

Assisted Solution

by:aikimark
aikimark earned 100 total points
Comment Utility
are you trying to replicate a VLookup() function?
0
 

Author Closing Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now