Macro based on patern matching for copying the data from one column to different column

Hey guys...

Need a macro to copy the selected data from column A to other column's(B to N) once the pattern matches

This is my data in column 'A' starting from row-1

000XX WWWWWW 03052555AB
010XX ABCD QI 00000002+0000000008640112827.08QQQQ_MNO
02003052010SIP 000000000000120+0000000002197663524.08AXX ABCD QI
03001996 1003319 0143209 0000000 00000 +0000000000000000000.01
03001996 1803229 0176000 0000000 00000 +0000000000000000000.01
03001996 9803432 0103567 0000000 00000 +0000000000010000000.00
03001996 1603212 1000000 0000000 00000 +0000000000010000000.00
03001996 1603453 0903789 0000000 00000 -0000000000000009189.38



if there are n rows and first 3 rows in column 'A' are header.....hence the no of rows in o/p columns (B to N) will be 'n-2'

Need o/p in different column's as per below rule starting from row #2.....

COLUMN B ===> start="4" length="5" where Record Type = 000
COLUMN C ===> start="20" length="8" format="MMddyyyy" where Record Type = 000
COLUMN D ===> start="28" length="3" where Record Type = 000
COLUMN E ===> start="4" length="25" where Record Type = 010
COLUMN F ===> start="60" length="20" where Record Type = 010
COLUMN G ===> start="12" length="15" where Record Type = 020
COLUMN H ===> start="65" length="1" where Record Type = 020
COLUMN I ===> start="66" length="25" where Record Type = 020
COLUMN J ===> start="4" length="30" where Record Type = 030
COLUMN K ===> start="34" length="30" where Record Type = 030
COLUMN L ===> start="64" length="30" where Record Type = 030
COLUMN M ===> start="94" length="30" where Record Type = 030
COLUMN N ===> start="154" length="23" where Record Type = 030

need o/p as below

B C D E F G H I J K
XX 03052555 AB XX ABCD QI QQQQ_MNO SIP A XX ABCD QI 1996 1003319
XX 03052555 AB XX ABCD QI QQQQ_MNO SIP A XX ABCD QI 1996 1803229
XX 03052555 AB XX ABCD QI QQQQ_MNO SIP A XX ABCD QI 1996 9803432
XX 03052555 AB XX ABCD QI QQQQ_MNO SIP A XX ABCD QI 1996 1603212
XX 03052555 AB XX ABCD QI QQQQ_MNO SIP A XX ABCD QI 1996 1603453


L M N
143209 0000000 +0000000000000000000.01
176000 0000000 +0000000000000000000.01
103567 0000000 +0000000000010000000.00
1000000 0000000 +0000000000010000000.00
903789 0000000 -0000000000000009189.38

i have come up with the if clause as below....

=IF(LEFT($A$1,3)="000",MID($A$1,4,5),0)

but need a macro for it.....Can anyone help ??

did any one need more information on this......i have come up with a small code...but it takes a long time for just to fetch 4 columns....can anyone help me to optimize it and replicate for other columns...


Sub MoveData()
Dim myrange, cell As Range
Dim i As Integer, count As Integer, data As Variant



count = ActiveSheet.Range("A1").End(xlDown).Row

Set myrange = ActiveSheet.Range("A:A", Range("A:A").End(xlDown))
'MsgBox myrange
For i = 1 To count - 3
For Each cell In myrange
If Left(cell.Value, 3) = "000" Then
data = Mid(cell.Value, 4, 5)
cell.Offset(i, 1).Value = data

cell.Offset(i, 2).Value = Mid(cell.Value, 20, 8)
cell.Offset(i, 3).Value = Mid(cell.Value, 28, 3)
ElseIf Left(cell.Value, 3) = "010" Then
cell.Offset(i, 4).Value = Mid(cell.Value, 4, 25)

End If
Next cell
Next i

Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select

Columns("C:C").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveSheet.Range("A1").Select


End Sub

vik66,
Please use code tags with your code as per forum's rule.
Why do you need a loop twice?

the first loop i need to copy the data for the number of rows i have in column A.....i am sorry i didn't elaborate it much in my first post...

the data in column A looks as below....and the first 3 rows are kind of header.......and the data from 4th row is my total no of rows....in below example...i have 5 rows....and i need the in columns B to K for 5 times only and not 8 times.......so i used in my code "For i = 1 To count - 3"

000XX WWWWWW 03052555AB
010XX ABCD QI 00000002+0000000008640112827.08QQQQ_MNO
02003052010SIP 000000000000120+0000000002197663524.08AXX ABCD QI
03001996 1003319 0143209 0000000 00000 +0000000000000000000.01
03001996 1803229 0176000 0000000 00000 +0000000000000000000.01
03001996 9803432 0103567 0000000 00000 +0000000000010000000.00
03001996 1603212 1000000 0000000 00000 +0000000000010000000.00
03001996 1603453 0903789 0000000 00000 -0000000000000009189.38


I am sorry for not using the code tags....will keep that in mind when i post the next code.