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

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.
StevenPMoffatAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
svgmucConnect With a Mentor Commented:
dim =rng, sin as range
set range = range("WBSa")
set sin = activesheet.cells(Application.WorksheetFunction.Match(v, rng, 0), 1)
0
 
svgmucCommented:
=OFFSET(A1,0,ROW(MATCH(v,G:G, 0))-1)
0
 
svgmucCommented:
Oh, did you say range?

You probably want to add ADDRESS then

=ADDRESS(OFFSET(A1,0,ROW(MATCH(v,G:G, 0))-1))
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
svgmucCommented:
or probably easier:

=ADDRESS(ROW(MATCH(v,G:G, 0)),1)
0
 
StevenPMoffatAuthor Commented:
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
 
svgmucCommented:
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
 
StevenPMoffatAuthor Commented:
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
 
StevenPMoffatAuthor Commented:
I get an "unable to get the Match property of the worksheetfunction class"  ?????
0
 
StevenPMoffatAuthor Commented:
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
 
StevenPMoffatAuthor Commented:
This works IF the range rng is a single column.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.