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!

VBA Question (rookie issue)
 

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

VBA Question (rookie issue)
 

Getting the following error:

Argument not optional (Error 449)

On this line:

for each cell in Range

VBA Question (rookie issue)
 

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" wrote in
message
...
Getting the following error:

Argument not optional (Error 449)

On this line:

for each cell in Range

VBA Question (rookie issue)
 

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" wrote in
message
...
Getting the following error:

Argument not optional (Error 449)

On this line:

for each cell in Range

VBA Question (rookie issue)
 

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" wrote in
message
...
Getting the following error:

Argument not optional (Error 449)

On this line:

for each cell in Range




--

Dave Peterson

VBA Question (rookie issue)
 

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" wrote in
message
...
Getting the following error:

Argument not optional (Error 449)

On this line:

for each cell in Range




--

Dave Peterson

--

Dave Peterson

VBA Question (rookie issue)
 

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