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

x
Solved

# Want the range of a single cell in a range(column 7) where the value v has been found in the first column.

Posted on 2011-02-22
Medium Priority
309 Views
Given that I have a string v and a range rng, I want to lookup  the value v in the range rng and return the range of the 7th column on the row the value v was found.  I was able to use Applciation.VLOOKUP to return the value, but I need the range of the single cell, not the value.

The search column of the range rng for the value v will be the first column if that makes a difference.
0
Question by:StevenPMoffat
[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
• 5
• 5

LVL 14

Expert Comment

ID: 34954139
=OFFSET(A1,0,ROW(MATCH(v,G:G, 0))-1)
0

LVL 14

Expert Comment

ID: 34954148
Oh, did you say range?

0

LVL 14

Expert Comment

ID: 34954174
or probably easier:

0

Author Comment

ID: 34954437
OK.  But I don't see where I use the orginating range "rng" in this example.  I see the reference for v and column 7 (G).  I think ADDRESS somehow refers to RNG but don't I need to indicate that somehow?
0

LVL 14

Expert Comment

ID: 34954541
The formula finds the cell with the matching value v in column 7 (G), then takes the row component and column 1 (you said first column) and creates an address reference.
0

Author Comment

ID: 34954689
Understand that.   But I am somewhat of a newbie.  I am trying to do this in VBA and the MATCH function needs to operate on the range "rng" but I don't know how to do that within VBA?   I thought maybe if I activate range rng first.
Here is a sample of what I am trying to do.   I get an compile error on the G:G with this
Dim rng As Range
Dim sin As Range
'v has been set to the search value
Set rng = Range("WBSa")
rng.Activate
0

LVL 14

Accepted Solution

svgmuc earned 500 total points
ID: 34955110
dim =rng, sin as range
set range = range("WBSa")
set sin = activesheet.cells(Application.WorksheetFunction.Match(v, rng, 0), 1)
0

Author Comment

ID: 34955222
I get an "unable to get the Match property of the worksheetfunction class"  ?????
0

Author Comment

ID: 34955764
I understand that I can find the row number I need in the range "rng" with the match function if I can get the applciation.worksheetfunction.match to work.  But whatever combination of stuff I try I keep getting the same error message listed above.  What if I wanted to just search a single column in rng?

I tried
dim row as integer
row = Application.WorksheetFunction.Match(v, rng, 0)
to see if I could get the row number, but that throughs the same error.

Note that rng is NOT a single column, but I want the match to apply  to the 1 column in rng.
0

Author Closing Comment

ID: 34963411
This works IF the range rng is a single column.
0

## Featured Post

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦
###### Suggested Courses
Course of the Month13 days, 14 hours left to enroll