Mr. Guillett:

Thank you very much for your help, that works as desired, now the only thing
I would like to change, is instead of using column "b" i would like to use a
variable. Because the spreadsheet is not always formatted as desired, I have
my macro check the headings. when it finds the column headed "Name" (USUALLY
B, but not always) it sets a variable "name_column." I would like to use
this variable instead of the letter B if at all possible. Here is the code i
use to establish the variable:

Rows(1).Select
Selection.Find(What:="Name", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
name_column = ActiveCell.Column


"Don Guillett" wrote:

I would do it this way (If Not c Is Nothing Then )to avoid looking at all
rows and avoid your stated problem

Sub betterfincbci()
With Range("b1:b" & Cells(Rows.Count, "b").End(xlUp).Row)

Set c = .Find(What:="BCI", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, 1).Value = "SOEWP"
c.Offset(, 8).Value = "EM"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If

End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Derek Johansen" wrote in message
...
I am using the following code to search through column B for all cells
containing "BCI" and when it finds them it replaces columns C and J with
certain things. This works fine, until BCI does not appear anywhere in
column B. At this point I get a run-time error. What I would like to do
is
say:
If "BCI" is part of cell in column B then fill columns c and j with
specific
criteria.

Here is the code I'm working with:

Columns("B").Select
For k = 2 To Cells(Rows.Count, "b").End(xlUp).Row
Selection.Find(What:="BCI", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Value = "SOEWP"
ActiveCell.Offset(0, 8).Value = "EM"
Next

any help will be MUCH appreciated!