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!

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" 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!

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 = ... ]

"JLGWhiz" wrote:

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" 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!

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!

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!

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Derek Johansen" wrote in message
...
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!

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?

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Derek Johansen" wrote in message
...
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!

Derek,

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

Worksheets("Sheet1").Columns("D:D").ColumnWidth =
Worksheets("Sheet2").Columns("D: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" wrote in message
...
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?

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Derek Johansen" wrote in
message
...
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!

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)

"Bernie Deitrick" wrote:

Derek,

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

Worksheets("Sheet1").Columns("D:D").ColumnWidth =
Worksheets("Sheet2").Columns("D: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" wrote in message
...
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?

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Derek Johansen" wrote in
message
...
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!

On May 27, 8:21*pm, Derek Johansen
wrote:
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)



"Bernie Deitrick" wrote:
Derek,

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

Worksheets("Sheet1").Columns("D:D").ColumnWidth =
Worksheets("Sheet2").Columns("D: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" wrote in message
...
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?

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Derek Johansen" wrote in
message
...
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!- Hide quoted text -

- 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

Matt,

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

Thanks again, bye for now,

Derek

" wrote:

On May 27, 8:21 pm, Derek Johansen
wrote:
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)



"Bernie Deitrick" wrote:
Derek,

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

Worksheets("Sheet1").Columns("D:D").ColumnWidth =
Worksheets("Sheet2").Columns("D: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" wrote in message
...
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?

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Derek Johansen" wrote in
message
...
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!- Hide quoted text -

- 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