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
305 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 Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Independent Software Vendors: 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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

717 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