Solved: Import TXT file info - multiple records - read a line

[tonyrosen]

First, the code I'm using:


For i = 1 To totalrows
If InStr(Range("E" & i), "APPL") Then
writarray = writarray & ";" & i
Else
writarray = writarray
End If
Next i
lwritarray = Len(writarray)
writarray = Right(writarray, (lwritarray - 1))
writarray = Split(writarray, ";")


Now, my problem.

I have a previous array of "start rows" [myrows] (i.e., "1;96;120" --- which would represent three records in the txt file). However, my "writarray" may only have 2 records as "APPL" may not appear in one of the records.

I need to be able to tie the "APPL" row to a record. And, it's proving something beyond me.

Help? Ideas?

[geekgirlau]

Tony, I'm not clear on what you're trying to accomplish here - can you post a scaled-down version of your spreadsheet (remove any sensitive data first) and a more details explanation of what you're trying to do?

[tonyrosen]

Here's all the code that I'm using for this.
First things first ... getting the rows that start each record. Each record will have "PLAINTIFF" on the first line.

For i = 1 To totalrows
If InStr(Range("A" & i), "PLAINTIFF") Then
' we have a correct row to work with
' let us start our array
iMyRow = iMyRow & ";" & i
Else
' we do nothing
iMyRow = iMyRow
End If
Next i
If Left(iMyRow, 1) = ";" Then
leng = Len(iMyRow)
iMyRow = Right(iMyRow, (leng - 1))
End If
myrows = Split(iMyRow, ";")



Now, somewhere in there, I need to pull out line numbers that have "APPL" in range("E" & whatever) ..


For i = 1 To totalrows
If InStr(Range("E" & i), "APPL") Then
writarray = writarray & ";" & i
Else
writarray = writarray & ";*" & i
End If
Next i
lwritarray = Len(writarray)
writarray = Right(writarray, (lwritarray - 1))
writarray = Split(writarray, ";")



Now, I need to compare "writarray" with "myrows" and determine where the row with "APPL" falls in ..

For example, let's assume that:
myrows(whatever) = "1;50;75;150"
writarray(0) = 31

I need a way to determine where in "myrows()" does "writarray(0)" fall.

The best I've come up with so far is:


For i = 1 To totalrows
For a = 0 To UBound(writarray)
If IsNumeric(writarray(a)) Then
For x = 0 To UBound(myrows)
xa = myrows(x)
If x = xmax Then
xy = xmax
Else
xy = x + 1
End If
xb = myrows(xy)
If writarray(a) > xa And writarray(a) < xb Then
Sheets(2).Activate
Range("K" & (x + 1)).Value = "*"
Sheets(1).Activate
Range("A" & (25 + x)).Value = writarray(a)
Sheets(3).Activate
End If
Next x
Else

End If
Next a
Next i


However, it's accurate only about 30% of the time.

[tonyrosen]

Disregard .. solved it myself with a LOT less code - once I started thinking it through.