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" <(E-Mail Removed)> wrote in message
news:58F87189-4F14-47B6-ADBB-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:58F87189-4F14-47B6-ADBB-(E-Mail Removed)...
> >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
(E-Mail Removed)
"Derek Johansen" <(E-Mail Removed)> wrote in message
news:58F87189-4F14-47B6-ADBB-(E-Mail Removed)...
>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
> (E-Mail Removed)
> "Derek Johansen" <(E-Mail Removed)> wrote in message
> news:58F87189-4F14-47B6-ADBB-(E-Mail Removed)...
> >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
(E-Mail Removed)
"Derek Johansen" <(E-Mail Removed)> wrote in message
news:6E1B9992-F472-4086-868D-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>> "Derek Johansen" <(E-Mail Removed)> wrote in
>> message
>> news:58F87189-4F14-47B6-ADBB-(E-Mail Removed)...
>> >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
> (E-Mail Removed)
> "Derek Johansen" <(E-Mail Removed)> wrote in message
> news:6E1B9992-F472-4086-868D-(E-Mail Removed)...
> > 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
> >> (E-Mail Removed)
> >> "Derek Johansen" <(E-Mail Removed)> wrote in
> >> message
> >> news:58F87189-4F14-47B6-ADBB-(E-Mail Removed)...
> >> >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").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" <(E-Mail Removed)> wrote in message
news:256CE11C-4074-4840-BA82-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>> "Derek Johansen" <(E-Mail Removed)> wrote in
>> message
>> news:6E1B9992-F472-4086-868D-(E-Mail Removed)...
>> > 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
>> >> (E-Mail Removed)
>> >> "Derek Johansen" <(E-Mail Removed)> wrote in
>> >> message
>> >> news:58F87189-4F14-47B6-ADBB-(E-Mail Removed)...
>> >> >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").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" <(E-Mail Removed)> wrote in message
> news:256CE11C-4074-4840-BA82-(E-Mail Removed)...
> > 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
> >> (E-Mail Removed)
> >> "Derek Johansen" <(E-Mail Removed)> wrote in
> >> message
> >> news:6E1B9992-F472-4086-868D-(E-Mail Removed)...
> >> > 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
> >> >> (E-Mail Removed)
> >> >> "Derek Johansen" <(E-Mail Removed)> wrote in
> >> >> message
> >> >> news:58F87189-4F14-47B6-ADBB-(E-Mail Removed)...
> >> >> >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
<DerekJohan...@discussions.microsoft.com> 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").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" <DerekJohan...@discussions.microsoft.com> wrote in message
> >news:256CE11C-4074-4840-BA82-(E-Mail Removed)...
> > > 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
> > >> dguille...@austin.rr.com
> > >> "Derek Johansen" <DerekJohan...@discussions.microsoft.com> wrote in
> > >> message
> > >>news:6E1B9992-F472-4086-868D-(E-Mail Removed)...
> > >> > 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
> > >> >> dguille...@austin.rr.com
> > >> >> "Derek Johansen" <DerekJohan...@discussions.microsoft.com> wrote in
> > >> >> message
> > >> >>news:58F87189-4F14-47B6-ADBB-(E-Mail Removed)...
> > >> >> >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