Conditional Selection.Find

[Derek Johansen]

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!

 

[JLGWhiz]

Try this version:

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

 

[Derek Johansen]

I get the same obnoxious error when BCI is not present: object variable or
with block variable not set on line [c = .... ]

And now when BCI is present, I get a mismatch error on line [c = ... ]

 

[Don Guillett]

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

 

[Derek Johansen]

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]

Sub findColumnName()
Dim mc As Long
Dim lr As Long
Dim c As Range
Dim firstaddress
mc = Rows(1).Find(What:="Name", LookIn:=xlValues, _
LookAt:=xlwhole, SearchOrder:=xlBycolumns, _
SearchDirection:=xlNext, MatchCase:=False).Column
'MsgBox mc
lr = Cells(Rows.Count, mc).End(xlUp).Row

With Range(Cells(1, mc), Cells(lr, mc))
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

 

[Derek Johansen]

You sir, are the man! Thank you very much! Since you seem to know your
stuff, I have one more question while I have your attention:

I am using the following commands to paste data into a new worksheet:
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlFormats

But my new worksheet is loosing the column widths. I would have thought
that would have been taken care of with "xlFormats" but apparently that is
incorrect. Any way to past columns keeping their same width, or would i have
to set each column individually?

 

[Bernie Deitrick]

Derek,

Column width is a property of a column rather than a range, so you can set
it explicitly:

Worksheets("Sheet1").Columns("D").ColumnWidth =
Worksheets("Sheet2").Columns("D").ColumnWidth

Help beyond that would depend on what your code actually is....


Width is also part of the formatting of the entire column, so if you copy
the entire column / paste entire column you will get the width.

HTH,
Bernie
MS Excel MVP

 

[Derek Johansen]

Bernie,

What I am doing is breaking apart an imported spreadsheet based on the
contents of a certain column. I'm creating a new workbook for each different
ID in the column, and using a loop then going through and copying, pasting,
and deleting specific rows into the new workbooks. Before I do the copy any
pasting however, I am pasting the original Column Headers from the first
workbook into the new workbooks. I don't have the code at home, but I can
post it tomorrow for you.

Essentially, the spreadsheet I get raw automatically adjusts column widths
on import, and I would like to be able to transfer these widths into the new
workbook i create. Is this possible without having to specify the width of
each indivudual column? (Code to come tomorrow if needed)

 

[meh2030]

Bernie,

What I am doing is breaking apart an imported spreadsheet based on the
contents of a certain column.  I'm creating a new workbook for each different
ID in the column, and using a loop then going through and copying, pasting,
and deleting specific rows into the new workbooks.  Before I do the copy any
pasting however, I am pasting the original Column Headers from the first
workbook into the new workbooks.  I don't have the code at home, but I can
post it tomorrow for you.  

Essentially, the spreadsheet I get raw automatically adjusts column widths
on import, and I would like to be able to transfer these widths into the new
workbook i create.  Is this possible without having to specify the width of
each indivudual column? (Code to come tomorrow if needed)











- Show quoted text -

Derek,

..PasteSpecial has a "Column Widths" option (Excel 2003: Copy the cells
and then execute Alt + e + s + w; Excel 2007: Copy the cells and then
execute Alt + h + v + s + w -- as a side note, Excel 2003 hotkeys will
also work for this operation in Excel 2007).

Selection.PasteSpecial Paste:=xlPasteColumnWidths

Best,

Matthew Herbert

 

[Derek Johansen]

Matt,

Thanks a lot! That's exactly what I needed!

Thanks again, bye for now,

Derek