VBA Question (rookie issue)

[Guest]

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

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!

 

[Tom Ogilvy]

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.

 

[Guest]

Getting the following error:

Argument not optional (Error 449)

On this line:

for each cell in Range

 

[Chip Pearson]

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



message
news:[email protected]...

 

[Guest]

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)

 

[Dave Peterson]

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.

 

[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).

 

[Guest]

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