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

  • Thread starter Thread starter HH
  • Start date Start date
H

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 ((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.
 
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
 
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
 
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
Click to expand...

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 !!!
 
HH said:
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?
Click to expand...

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?
Click to expand...

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
 
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
Click to expand...

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