Just trying to capture data in a loop

hitbid

Board Regular
Joined
Jan 21, 2016
Messages
114
I am doing an exercise where I am searching countless data entries in a bad format and finding what I need. Below is very sample data. I can find the midway point all the time fine. But it is getting the data between MidwayPoint and NewStart.
I tried IF statements but keep getting locked up, and the fact that the data here isn't always stacked neatly. Sometimes spaces jump in like you see below.
How Can I best loop to always capture all the data between MP and NS?

Start
data
data
data
data
data
Midway Point
data
data
data
NewStart
data
data
data

<tbody>
</tbody>

Code:
    indi = 1
For i = 1 To lastrow
    If (InStr(1, inarr(i, 1), "Invoice # S", vbTextCompare)) > 0 Then
        If (InStr(1, inarr(i + 7, 1), "Shipping Instr :", vbTextCompare)) > 0 Then
                    If WorksheetFunction.CountA(inarr(i + 8, 1)) > 0 Then
                    Num8 = inarr(i + 8, 1)
                    ElseIf WorksheetFunction.CountA(inarr(i + 8, 1)) = 0 Then
                    Num8 = ""
                    End If
                    If (InStr(1, inarr(i + 7, 1), "Shipping Instr :", vbTextCompare)) = 0 Then
                    Num8 = ""
                    End If
        Num7 = Trim(Split(inarr(i + 7, 1), "Shipping Instr :")(1))
        Else: Num7 = ""
        End If
    outarr(indi, 6) = Num7 & Num8
    indi = indi + 1
    End If
Next i

<tbody> </tbody>
 
Last edited:

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Watch MrExcel Video

Forum statistics

Threads
1,109,072
Messages
5,526,619
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top