ranges
 

I am working with a range of names, I want to take that range and have a
macro take the first 8 characters of each cell in that range and paste them
into another range. Then i want the macro to go back throught the initail
range and take the next 8 characters and paste that to a second new range
right next to the one with the first 8 characters. If this can be done?
does anyone know how?

ranges
 

Hi
try something like the following:

Sub move_characters()
Dim lastrow As Long
Dim row_index As Long
Dim col_index As Integer
Dim char_count As Integer
Dim source_wks As Worksheet
Dim target_wks As Worksheet
Dim sub_str As String

Set source_wks = Worksheets("Tabelle1")
Set target_wks = Worksheets("Tabelle2")
char_count = 8
lastrow = source_wks.Cells(Rows.Count, "A").End(xlUp).Row

For col_index = 1 To 2
For row_index = 1 To lastrow
If Len(source_wks.Cells(row_index, "A").Value) _
(col_index - 1) * char_count Then
sub_str = Mid(source_wks.Cells(row_index, "A").Value, _
(col_index - 1) * 8 + 1, char_count)
target_wks.Cells(row_index, col_index).Value = sub_str
End If
Next
Next
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"Rich Cooper" schrieb im Newsbeitrag
...
I am working with a range of names, I want to take that range and
have a
macro take the first 8 characters of each cell in that range and
paste them
into another range. Then i want the macro to go back throught the
initail
range and take the next 8 characters and paste that to a second new
range
right next to the one with the first 8 characters. If this can be
done?
does anyone know how?

ranges
 

A simple one if all are at least 16

Sub getstrs()
For Each c In Selection
c.Offset(, 1) = Left(c, 8)
c.Offset(, 2) = Mid(c, 9, 8)
Next
End Sub

--
Don Guillett
SalesAid Software

"Rich Cooper" wrote in message
...
I am working with a range of names, I want to take that range and have a
macro take the first 8 characters of each cell in that range and paste
them
into another range. Then i want the macro to go back throught the initail
range and take the next 8 characters and paste that to a second new range
right next to the one with the first 8 characters. If this can be done?
does anyone know how?

ranges
 

Just worksheet functions

B1: =MID(A1,1,8)
C1: =MID(A1,9,8)

etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
try something like the following:

Sub move_characters()
Dim lastrow As Long
Dim row_index As Long
Dim col_index As Integer
Dim char_count As Integer
Dim source_wks As Worksheet
Dim target_wks As Worksheet
Dim sub_str As String

Set source_wks = Worksheets("Tabelle1")
Set target_wks = Worksheets("Tabelle2")
char_count = 8
lastrow = source_wks.Cells(Rows.Count, "A").End(xlUp).Row

For col_index = 1 To 2
For row_index = 1 To lastrow
If Len(source_wks.Cells(row_index, "A").Value) _
(col_index - 1) * char_count Then
sub_str = Mid(source_wks.Cells(row_index, "A").Value, _
(col_index - 1) * 8 + 1, char_count)
target_wks.Cells(row_index, col_index).Value = sub_str
End If
Next
Next
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"Rich Cooper" schrieb im Newsbeitrag
...
I am working with a range of names, I want to take that range and
have a
macro take the first 8 characters of each cell in that range and
paste them
into another range. Then i want the macro to go back throught the
initail
range and take the next 8 characters and paste that to a second new
range
right next to the one with the first 8 characters. If this can be
done?
does anyone know how?

ranges
 

I don't think it matters Don. The MID(c,9,8) will return 4 if there are just
12 say, and even returns blank if there are 8 or less,

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Don Guillett" wrote in message
...
A simple one if all are at least 16

Sub getstrs()
For Each c In Selection
c.Offset(, 1) = Left(c, 8)
c.Offset(, 2) = Mid(c, 9, 8)
Next
End Sub

--
Don Guillett
SalesAid Software

"Rich Cooper" wrote in message
...
I am working with a range of names, I want to take that range and have
a
macro take the first 8 characters of each cell in that range and paste
them
into another range. Then i want the macro to go back throught the
initail
range and take the next 8 characters and paste that to a second new
range
right next to the one with the first 8 characters. If this can be done?
does anyone know how?

ranges
 

Thanks, Just tested with only 2 and it worked just fine

--
Don Guillett
SalesAid Software

"Bob Phillips" wrote in message
...
I don't think it matters Don. The MID(c,9,8) will return 4 if there are
just
12 say, and even returns blank if there are 8 or less,

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Don Guillett" wrote in message
...
A simple one if all are at least 16

Sub getstrs()
For Each c In Selection
c.Offset(, 1) = Left(c, 8)
c.Offset(, 2) = Mid(c, 9, 8)
Next
End Sub

--
Don Guillett
SalesAid Software

"Rich Cooper" wrote in message
...
I am working with a range of names, I want to take that range and
have
a
macro take the first 8 characters of each cell in that range and paste
them
into another range. Then i want the macro to go back throught the
initail
range and take the next 8 characters and paste that to a second new
range
right next to the one with the first 8 characters. If this can be
done?
does anyone know how?

ranges
 

if the names are in a single column, look at Text to Columns under the Data
menu. Used a fixed width.

--
Regards,
Tom Ogilvy

"Rich Cooper" wrote in message
...
I am working with a range of names, I want to take that range and have a
macro take the first 8 characters of each cell in that range and paste
them
into another range. Then i want the macro to go back throught the initail
range and take the next 8 characters and paste that to a second new range
right next to the one with the first 8 characters. If this can be done?
does anyone know how?