VBA Question (rookie issue)

I thought I was finished with my project... and then my boss stepped in. One
more thing! :o)

Here is what I have:
Col A = Mark
Col B = Qty
Col C = Item Number
Col D = Description

In the sheet I am working with there is a item number column that contains
at least a part # and in some cases also contains some descriptive text. I
have a another column for description that in cases where the part # alone is
in the item number column a description does exist.

In the cases where the item number column contains both the part # and
descriptive text the description column is empty. In those case I want to
move the descriptive text from the item number column to the description
column. The part # is a continueous string, and the descriptive text begins
after a single space.

Need this in a macro please.

Below is an example.

What I have now:
Mark Qty Ordered Item Number Description 1
M2 1 AB43B Box
M2 1 AEF3422MB Interior
M2 1 AF43SDN Front
4 SG400R Circuit Breaker Enclosure 134
4 SGD32400WL Breaker Catalog Number
2 SKLB36BC1200 Spectra MCB (135S)
1 SRPK1200B1000 Spectra MCB (135S)
1 SRPK1200B1200 Spectra MCB (135S)
4 TGL1 Ground Kit

What I want to end up with:
Mark Qty Ordered Item Number Description 1
M2 1 AB43B Box
M2 1 AEF3422MB Interior
M2 1 AF43SDN Front
4 SG400R Circuit Breaker Enclosure 134
4 SGD32400WL Breaker Catalog Number
2 SKLB36BC1200 Spectra MCB (135S)
1 SRPK1200B1000 Spectra MCB (135S)
1 SRPK1200B1200 Spectra MCB (135S)
4 TGL1 Ground Kit


Thanks in advance!

Sub FixDescription()
Dim rng as Range, cell as Range, iloc as Long
set rng = Range(Cells(2,3),Cells(rows.count,3).End(xlup))
for each cell in Range
iloc = Instr(1,cell," ",vbTextCompare)
if iloc <> 0 then
cell.offset(0,1).Value = mid(cell.Value,iloc+1,255)
cell.Value = left(cell,1,iloc-1)
end if
Next
End Sub

Test it on a copy of your data.
--
Regards,
Tom Ogilvy


"Scott Wagner" <[email protected]> wrote in message
news:[email protected]...
> I thought I was finished with my project... and then my boss stepped in.
One
> more thing! :o)
>
> Here is what I have:
> Col A = Mark
> Col B = Qty
> Col C = Item Number
> Col D = Description
>
> In the sheet I am working with there is a item number column that contains
> at least a part # and in some cases also contains some descriptive text.
I
> have a another column for description that in cases where the part # alone
is
> in the item number column a description does exist.
>
> In the cases where the item number column contains both the part # and
> descriptive text the description column is empty. In those case I want to
> move the descriptive text from the item number column to the description
> column. The part # is a continueous string, and the descriptive text
begins
> after a single space.
>
> Need this in a macro please.
>
> Below is an example.
>
> What I have now:
> Mark Qty Ordered Item Number Description 1
> M2 1 AB43B Box
> M2 1 AEF3422MB Interior
> M2 1 AF43SDN Front
> 4 SG400R Circuit Breaker Enclosure 134
> 4 SGD32400WL Breaker Catalog Number
> 2 SKLB36BC1200 Spectra MCB (135S)
> 1 SRPK1200B1000 Spectra MCB (135S)
> 1 SRPK1200B1200 Spectra MCB (135S)
> 4 TGL1 Ground Kit
>
> What I want to end up with:
> Mark Qty Ordered Item Number Description 1
> M2 1 AB43B Box
> M2 1 AEF3422MB Interior
> M2 1 AF43SDN Front
> 4 SG400R Circuit Breaker Enclosure 134
> 4 SGD32400WL Breaker Catalog Number
> 2 SKLB36BC1200 Spectra MCB (135S)
> 1 SRPK1200B1000 Spectra MCB (135S)
> 1 SRPK1200B1200 Spectra MCB (135S)
> 4 TGL1 Ground Kit
>
>
> Thanks in advance!

Getting the following error:

Argument not optional (Error 449)

On this line:

for each cell in Range

> for each cell in Range

should be

for each cell in rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Scott Wagner" <[email protected]> wrote in
message
news:[email protected]...
> Getting the following error:
>
> Argument not optional (Error 449)
>
> On this line:
>
> for each cell in Range

Now I am getting this error:

Wrong number of arguments or invalid property assignment (Error 450)

On line: Left is highlighted

cell.Value = Left(cell, 1, iloc - 1)





"Chip Pearson" wrote:

> > for each cell in Range
>
> should be
>
> for each cell in rng
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Scott Wagner" <[email protected]> wrote in
> message
> news:[email protected]...
> > Getting the following error:
> >
> > Argument not optional (Error 449)
> >
> > On this line:
> >
> > for each cell in Range
>
>
>

Try:
cell.Value = Mid(cell, 1, iloc - 1)

Tom's code is picking up the original string from the first character to the
position of the first space character.

Alternatively:
cell.Value = left(cell, iloc - 1)

They should work the same way.





Scott Wagner wrote:
>
> Now I am getting this error:
>
> Wrong number of arguments or invalid property assignment (Error 450)
>
> On line: Left is highlighted
>
> cell.Value = Left(cell, 1, iloc - 1)
>
> "Chip Pearson" wrote:
>
> > > for each cell in Range
> >
> > should be
> >
> > for each cell in rng
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> >
> > "Scott Wagner" <[email protected]> wrote in
> > message
> > news:[email protected]...
> > > Getting the following error:
> > >
> > > Argument not optional (Error 449)
> > >
> > > On this line:
> > >
> > > for each cell in Range
> >
> >
> >

--

Dave Peterson

Tom's code is picking up the original string from the first character to the
position of the first space character (minus 1).

Dave Peterson wrote:
>
> Try:
> cell.Value = Mid(cell, 1, iloc - 1)
>
> Tom's code is picking up the original string from the first character to the
> position of the first space character.
>
> Alternatively:
> cell.Value = left(cell, iloc - 1)
>
> They should work the same way.
>
> Scott Wagner wrote:
> >
> > Now I am getting this error:
> >
> > Wrong number of arguments or invalid property assignment (Error 450)
> >
> > On line: Left is highlighted
> >
> > cell.Value = Left(cell, 1, iloc - 1)
> >
> > "Chip Pearson" wrote:
> >
> > > > for each cell in Range
> > >
> > > should be
> > >
> > > for each cell in rng
> > >
> > >
> > > --
> > > Cordially,
> > > Chip Pearson
> > > Microsoft MVP - Excel
> > > Pearson Software Consulting, LLC
> > > www.cpearson.com
> > >
> > >
> > >
> > > "Scott Wagner" <[email protected]> wrote in
> > > message
> > > news:[email protected]...
> > > > Getting the following error:
> > > >
> > > > Argument not optional (Error 449)
> > > >
> > > > On this line:
> > > >
> > > > for each cell in Range
> > >
> > >
> > >
>
> --
>
> Dave Peterson

--

Dave Peterson

That did it! Thanks to all for helping with this.

Here is the final macro:

Sub FixDescription()
Dim rng As Range, cell As Range, iloc As Long
Set rng = Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp))
For Each cell In rng
iloc = InStr(1, cell, " ", vbTextCompare)
If iloc <> 0 Then
cell.Offset(0, 1).Value = Mid(cell.Value, iloc + 1, 255)
cell.Value = Mid(cell, 1, iloc - 1)

End If
Next
End Sub