Need to find particular string within cell text, if found a value is copied

[acctemp]

The text is in cell ranges B9:B200, I'm looking for any cell in that
range that contains the words "Savings Bonds". This string may be
within a larger text like "Savings Bonds Fee Adjustment".

If a cell is identified as having that string then the value of the
cell to the right of it is copied to cell G9, G10, G11, etc. Example:

Search for Text Copy Value
Destination

B12: Savings Bonds C12: 2,050.00 G9:
2,050.00
B39: Savings Bonds Redeemed C39: 200.00 G10:
200.00
B182: Savings Bonds Adjustment C182: 3.80 G11:
3.80

The purpose of this is to quickly identify all Savings Bonds related
transactions and have them summarized (where they can be totalled) in a
section of the worksheet.

Thanks!

 

[Don Guillett]

There is an excellent example in the vba help index for FINDNEXT. Be sure
you use lookat:=xlpart

 

[acctemp]

Hi,

This is what I found:

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

I'm not sure how to modify this for my needs, if I read this correctly
it looks for the value "2" then changes the cell that has that value to
xlPatternGray50?

 

[Tom Ogilvy]

Insert a module in the VBE. Select the sheet you want to process and go to
Tools=>Macro=>Macros, select ABC and click run. Obviously test this on a
copy of your data.

Sub ABC()
Dim i As Long, rng As Range
Dim c As Range, firstAddress As String
i = 0
Set rng = ActiveSheet.Range("G9")
With ActiveSheet.Columns(2)
Set c = .Find("Savings Bonds", _
LookIn:=xlValues, Lookat:=xlPart, _
MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
rng.Offset(i, 0).Value = c.Offset(0, 1).Value
i = i + 1
Set c = .FindNext(c)
Loop While c.Address <> firstAddress
End If
End With
End Sub

 

[Don Guillett]

Have it look for your string instead. When found have it copy the value to
col g
Set c = .Find("Savings Bonds", lookin:=xlValues, lookat:=xlpart)

c.offset(,5)=c.offset(,1)