Copying files error
I am trying to scan through some data and copy certain lines over to a new work book. I got it working but it doesn't seem to be working correctly 100%
It is suppose to scan through column 1 and recognize a ";" and the number "25" as the first character(s), and if it finds one, copy each row containing those characters to a seperate workbook.
It does just that, but it seems to copy the same row every time it finds a semicolon or a 25.
here is the code i have:
Sub PnP_Column1_FindSemiColon()
Dim oWB1 As Workbook ' input worksheet
Dim oWB2 As Workbook ' output worksheet
Dim bRet As Boolean
Dim cRet As Boolean
Set oWB1 = ActiveWorkbook
Set oWB2 = Workbooks.Add
Dim i1 As Long
Dim i2 As Long
Dim arSemiColonAdd() As String
Dim arFindNo25Add() As String
Dim iRow
bRet = FindAll(";", oWB1.Sheets(1), "A:A", arSemiColonAdd)
cRet = FindAll("25", oWB1.Sheets(1), "A:A", arFindNo25Add)
i2 = 1
If bRet = True Then
For i1 = 1 To UBound(arSemiColonAdd)
i2 = i2 + 1
iRow = Right(arSemiColonAdd(i1), Len(arSemiColonAdd(i1)) - InStrRev(arSemiColonAdd(i1), "$"))
oWB1.Sheets(1).Rows(iRow).EntireRow.Copy Destination:=oWB2.Sheets(1).Range("A" & i2)
Next i1
End If
If cRet = True Then
For il = 1 To UBound(arFindNo25Add)
i2 = i2 + 1
iRow = Right(arFindNo25Add(i1), Len(arFindNo25Add(i1)) - InStrRev(arFindNo25Add(i1), "$"))
oWB1.Sheets(1).Rows(iRow).EntireRow.Copy Destination:=oWB2.Sheets(1).Range("A" & i2)
Next il
End If
End Sub
The FindAll function is a function of a member here on the boards, and it seems to be doing its job, just it just seems to duplicate the rows it finds when they should all be somewhat different.
as in:
25 1 270 0.45 1.527 J1 WM7676
25 1 90 1.8 0.974 J2 WM7676
25 1 270 2.7 1.527 J3 WM7676
25 1 90 4.05 0.974 J4 WM7676
25 1 270 4.95 1.527 J5 WM7676
25 1 90 6.3 0.974 J6 WM7676
25 1 270 7.2 1.527 J7 WM7676
25 1 90 8.55 0.974 J8 WM7676 <--copies this row only for some reason
but instead i get:
25 1 90 8.55 0.974 J8 WM7676
25 1 90 8.55 0.974 J8 WM7676
25 1 90 8.55 0.974 J8 WM7676
25 1 90 8.55 0.974 J8 WM7676
25 1 90 8.55 0.974 J8 WM7676
25 1 90 8.55 0.974 J8 WM7676
25 1 90 8.55 0.974 J8 WM7676
25 1 90 8.55 0.974 J8 WM7676
25 1 90 8.55 0.974 J8 WM7676
can anyone see the problem??
Thanks
|
