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

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

Post by acctem » Fri, 17 Nov 2006 03:23:40


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!
 
 
 

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

Post by Don Guille » Fri, 17 Nov 2006 03:28:05

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

--
Don Guillett
SalesAid Software
XXXX@XXXXX.COM

 
 
 

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

Post by acctem » Fri, 17 Nov 2006 05:26:44

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?
 
 
 

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

Post by Don Guille » Fri, 17 Nov 2006 06:14:02

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
XXXX@XXXXX.COM