?
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
Medium Priority
?
307 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
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.

 

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 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

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

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

770 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