Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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.
0
StevenPMoffat
Asked:
StevenPMoffat
  • 5
  • 5
1 Solution
 
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
 
svgmucCommented:
or probably easier:

=ADDRESS(ROW(MATCH(v,G:G, 0)),1)
0
Technology Partners: 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!

 
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
 
svgmucCommented:
dim =rng, sin as range
set range = range("WBSa")
set sin = activesheet.cells(Application.WorksheetFunction.Match(v, rng, 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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now