Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how copy formula that contains ranges so ranges do not overlap | Excel Worksheet Functions | |||
Ranges | Excel Worksheet Functions | |||
Sum Ranges | Excel Worksheet Functions | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
Max Value from 2 different Ranges | Excel Programming |