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
10
304 Views
Last Modified: 2012-06-27
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
Comment
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
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 14

Expert Comment

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

Expert Comment

by:svgmuc
ID: 34954148
Oh, did you say range?

You probably want to add ADDRESS then

=ADDRESS(OFFSET(A1,0,ROW(MATCH(v,G:G, 0))-1))
0
 
LVL 14

Expert Comment

by:svgmuc
ID: 34954174
or probably easier:

=ADDRESS(ROW(MATCH(v,G:G, 0)),1)
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:StevenPMoffat
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

by:svgmuc
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

by:StevenPMoffat
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
Set sin =ADDRESS(OFFSET(A1,0,ROW(MATCH(v,G:G, 0))-1))
0
 
LVL 14

Accepted Solution

by:
svgmuc earned 125 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

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

Author Comment

by:StevenPMoffat
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

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

696 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