ranges

R

Rich Cooper

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?
 
F

Frank Kabel

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
 
D

Don Guillett

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
 
B

Bob Phillips

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)
 
B

Bob Phillips

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)
 
T

Tom Ogilvy

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