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!

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

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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!

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?

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
--
Regards,
Tom Ogilvy


" wrote:

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?

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)


--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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?