UsedRange/Copy Columns question

[Stuart]

Sub GetItemRef(ws As Worksheet)
Dim C As Range, sStr As String, x As Variant
'here we combine the values in cols A and B and place them in K.
'then strip out the item reference (A, B etc), and delete the rest.
'place the identifier in col A, then delete col B.
With ws
For Each C In .UsedRange.Columns("B")
'copy the combined A and B values to K
C.Offset(0, 9).Value = C.Offset(0, -1).Value
C.Offset(0, 10) = C.Value
' take care of the double space, if it's there
sStr = Application.Trim(C.Value)
x = (Split(sStr, "\"))
.Range(C).Offset(0, -1).Value = UBound(x)
Next
'remove the redundant column
.Columns("B").Delete
'expand 'new' col B to a width of 55
.Columns("B").ColumnWidth = 55
End With
End Sub

i) At the moment it copies col A values to K and col B values to L
rather than combine them in K, ideally separated by "\", ie
C.Offset(0, 9).Value = C.Offset(0, -1).Value & "\" & _
C.Value

ii) The code appears to operate on the UsedRange in one hit,
so why am I using a Loop?

iii) A typical set of data would be:
A B
7 004\002\D
where I'm looking to split the B value and end with D in Col A.

Any help with any of these issues would be much appreciated.

Regards.

 

[Anders S]

Stuart,

i) Try the following code, not sure if it does what you want though.

ii) Yes, you have to loop through the range cell by cell

----

Sub GetItemRef(ws As Worksheet)
Dim C As Range, sStr As String, x As Variant
'here we combine the values in cols A and B and place them in K.
'then strip out the item reference (A, B etc), and delete the rest.
'place the identifier in col A, then delete col B.
With ws
For Each C In .UsedRange.Columns("B").Cells
'copy the combined A and B values to K
C.Offset(0, 9).Value = C.Offset(0, -1).Value _
& "\" & C.Value

' take care of the double space, if it's there
sStr = Application.Trim(C.Value)
x = (Split(sStr, "\"))
C.Offset(0, -1).Value = x(UBound(x))
Next
'remove the redundant column
.Columns("B").Delete
'expand 'new' col B to a width of 55
.Columns("B").ColumnWidth = 55
End With
End Sub

 

[Stuart]

Yes, in principle it does....
However the data in cols A & B is not as consistent as I first
thought, so am modifying your code, accordingly.

Will post back if needed, if I may.

Many thanks for the help.

Regards.

Stuart,

i) Try the following code, not sure if it does what you want though.

ii) Yes, you have to loop through the range cell by cell

----

Sub GetItemRef(ws As Worksheet)
Dim C As Range, sStr As String, x As Variant
'here we combine the values in cols A and B and place them in K.
'then strip out the item reference (A, B etc), and delete the rest.
'place the identifier in col A, then delete col B.
With ws
For Each C In .UsedRange.Columns("B").Cells
'copy the combined A and B values to K
C.Offset(0, 9).Value = C.Offset(0, -1).Value _
& "\" & C.Value

' take care of the double space, if it's there
sStr = Application.Trim(C.Value)
x = (Split(sStr, "\"))
C.Offset(0, -1).Value = x(UBound(x))
Next
'remove the redundant column
.Columns("B").Delete
'expand 'new' col B to a width of 55
.Columns("B").ColumnWidth = 55
End With
End Sub