String search
 

I've got a workbook with 2 worksheets, Inventory and Sheet1
Column B on Sheet "Inventory" has 2500 lines filled with strings (one to a
cell)
I need to select each cell and have it's content "Searched" on "Sheet1".
If something is found, I need to offset 2 columns and copy that cell and
then paste it back in Sheet "Inventory"
I don't think I can use Vlookup cause what I'm looking for is part of a
string.

Below is what I've got so far...stop laughing, I know it doesn't work...
After you look at my code, I won't have to tell you that I don't know much !

Thanks for the help!
Marc

Sub Macro1()
Dim counter As Variant
Dim myCell As Range
Dim c As Variant


For counter = 1 To 2500

counter = counter + 1

Set myCell = Sheets("Inventory").Cells(counter, 2)

With Worksheets("Sheet1").Range("E2:E13000")

Set c = .Find(What:=myCell.Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End With

If Not c Is Nothing Then
c.Offset(0, -2).Copy

End If


Sheets("Inventory").Select
myCell.Offset(0, 1).Paste

Next

End Sub

String search
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marc Gendron" wrote in message
...

I've got a workbook with 2 worksheets, Inventory and Sheet1
Column B on Sheet "Inventory" has 2500 lines filled with strings (one to a
cell)
I need to select each cell and have it's content "Searched" on "Sheet1".
If something is found, I need to offset 2 columns and copy that cell and
then paste it back in Sheet "Inventory"
I don't think I can use Vlookup cause what I'm looking for is part of a
string.

Below is what I've got so far...stop laughing, I know it doesn't work...
After you look at my code, I won't have to tell you that I don't know much
!

Thanks for the help!
Marc

Sub Macro1()
Dim counter As Variant
Dim myCell As Range
Dim c As Variant


For counter = 1 To 2500

counter = counter + 1

Set myCell = Sheets("Inventory").Cells(counter, 2)

With Worksheets("Sheet1").Range("E2:E13000")

Set c = .Find(What:=myCell.Value, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End With

If Not c Is Nothing Then
c.Offset(0, -2).Copy

End If


Sheets("Inventory").Select
myCell.Offset(0, 1).Paste

Next

End Sub