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

[HH]

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 ((D2999) 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.

 

[Bernie Deitrick]

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

 

[Smallweed]

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("D2999").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

 

[HH]

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("D2999").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

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

 

[Smallweed]

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?

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

 

[HH]

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




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

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

 

[Smallweed]

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