Find a textstring in a "Range" - and then return a value if textstring is found.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

HH - 28 Jan 2008 14:24 GMT

Hi'

Hope you can help.

I need to lookup a collection of textstrings in a specifik range - and
then react if the textstring is found:

Sheet1 contains a number of cells (B2:B5000) each containing a
textstring.
Sheet2 also contains a number of cells ((D2:D999) each containing a
textstring AND a value in range E2:E10.

Now I need to compare (using InString) the cells in Sheet2 with Sheet1
- and if there is a match I need to return the value.

I planned on making a collection of the 999 values I search for, but
how do I match these with the code?
If I find the text in D10 I need to use the value in E10.

Thanks.

Reply to this Message

Bernie Deitrick - 28 Jan 2008 14:57 GMT

HH,

You can use a VLOOKUP function, like

=VLOOKUP(B2,Sheet2!$D$2:$E$999,2,False)

Then copy down to row 5000.

HTH,
Bernie
MS Excel MVP

> Hi'
>
[quoted text clipped - 16 lines]
>
> Thanks.

Reply to this Message

Smallweed - 28 Jan 2008 15:03 GMT

This should do it - I was a bit unsure why the E range finished at row 10
while the D range went on to 999. I'm assuming the E range is the same:

Sub DoIt()
Dim rng1 As Range
Dim rng2 As Range
Dim lngCtr As Long
For Each rng2 In Worksheets("Sheet2").Range("D2:D999").Cells
For Each rng1 In Worksheets("Sheet1").Range("B2:B5000").Cells
If InStr(rng1.Value, rng2.Value)<>0 Then 'found
lngCtr=lngCtr+1

Worksheets("Sheet3").Cells(lngCtr,1).Value=rng2.Offset(0,1).Value
End If
Next rng1
Next rng2
End Sub

> Hi'
>
[quoted text clipped - 16 lines]
>
> Thanks.

Reply to this Message

HH - 29 Jan 2008 08:56 GMT

On 28 Jan., 17:03, Smallweed <Smallw...@discussions.microsoft.com>
wrote:

> This should do it - I was a bit unsure why the E range finished at row 10
> while the D range went on to 999. I'm assuming the E range is the same:
[quoted text clipped - 13 lines]
> Next rng2
> End Sub

This is just Perfect!
Now I feel inspired ;-)

1)
What if I want the search to begin at a certain row? I know how to
find the row number, but how do I use it in the range?

2)
The advanced and perfect solution would be to offset the sheet1 value
to another column
I can make the value returned from sheet 2 (column E) to be the number
of columns that the value should be offset - is there a way of making
this happen?

Thanks !!!

Reply to this Message

Smallweed - 29 Jan 2008 11:54 GMT

> On 28 Jan., 17:03, Smallweed <Smallw...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 22 lines]
> What if I want the search to begin at a certain row? I know how to
> find the row number, but how do I use it in the range?

If x is the row to start in, the following amendment looks at every cell in
the range between Dx (e.g. D5 if x=5) and D999 (Cells(x,y) is row x, column
y).

For Each rng2 In Worksheets("Sheet2").Range(Cells(x, 4), Cells(999, 4).Cells

> 2)
> The advanced and perfect solution would be to offset the sheet1 value
> to another column
> I can make the value returned from sheet 2 (column E) to be the number
> of columns that the value should be offset - is there a way of making
> this happen?

I returned the values to Sheet3, offset down a cell each time so you get a
column of results. Offset(row, col) is useful. E.g., instead of:
Worksheets("Sheet3").Cells(lngCtr,1).Value=rng2.Offset(0,1).Value in the
above you could do:
rng1.Offset(0, rng2.Value).Value=whatever

> Thanks !!!

Reply to this Message

HH - 29 Jan 2008 13:47 GMT

> If x is the row to start in, the following amendment looks at every cell in
> the range between Dx (e.g. D5 if x=5) and D999 (Cells(x,y) is row x, column
[quoted text clipped - 16 lines]
>
> > Thanks !!!

Seems to be working - wishes my original example had been closer to my
tasks... :-)

If I have hidden columns - do they count when using offset?
e.g. Offset from A to C when B is hidden.

Thanks...

Reply to this Message

Smallweed - 29 Jan 2008 17:05 GMT

yes they do (there's a range property "visible" you could use to adapt your
code)

> > If x is the row to start in, the following amendment looks at every cell in
> > the range between Dx (e.g. D5 if x=5) and D999 (Cells(x,y) is row x, column
[quoted text clipped - 24 lines]
>
> Thanks...

Reply to this Message