excelvbaexcel-automation

Excel IF/MID loop result showing additional data

I'm working with a fixed format text file of 100,000+ rows that I copy and paste into a sheet called DATA. All the rows in this sheet start with either AVS or AVD. I have the below code that loops through the DATA sheet with a IF MID function. I only want it to pull the data if the first 3 characters of the row start with AVS. When it runs it retrieves the data from the AVS row correctly, but then the subsequent rows show the same data even though they start with AVD. I've listed a sample below. I'm thinking it might have to do with error handling but I don't know. I don't get any errors, I just get additional data that's not requested. Any help you guys can give would be amazing!

Sub AVS()

Dim ws As Worksheet
Dim ws1 As Worksheet

Set ws = ThisWorkbook.Worksheets("DATA")
Set ws1 = ThisWorkbook.Worksheets("AVS")
Dim lastRow, myLoop, newValue
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).row
Application.ScreenUpdating = False


'Range("A" & lastRow).ClearContents

For myLoop = 1 To lastRow

   If MID(ws.Range("A" & myLoop).Value, 1, 3) = "AVS" Then
      newValue = MID(ws.Range("A" & myLoop).Value, 48, 4)
End If

        ws1.Range("A" & myLoop).Value = newValue

Next

 Application.ScreenUpdating = True
End Sub

Sample from DATA sheet:

AVS00001              Y6678                NN  B5UP 
AVD00001               6678                N INQN B5UP6678
AVS00001              Y6678                NN  0067
AVD00001               6678                N INQN 00676678
AVS00001              G6678                NN  1381
AVD00001               6678                N INQN 13816678
AVS00001              Y6678                NN  1922
AVD00001               6678                N INQN 192210-61096-58
AVD00001               6678                N INQN 19226678 
AVD00001               6678                N INQN 19226678 
AVS00001              Y6678                NN  5018

Current Results

B5UP (CORRECT)
B5UP (WRONG, ROW STARTS WITH AVD)
0067 (CORRECT)
0067 (WRONG, ROW STARTS WITH AVD)
1381 (CORRECT)
1381 (WRONG, ROW STARTS WITH AVD)
1922 (CORRECT)
1922 (WRONG, ROW STARTS WITH AVD)
1922 (WRONG, ROW STARTS WITH AVD)
1922 (WRONG, ROW STARTS WITH AVD)
5018 (CORRECT)

Cu

Solution

  • If I understand things correctly from the comments, this should work:

    Dim RowCounter as long
RowCounter = 1 

For myLoop = 1 To lastRow
    If MID(ws.Range("A" & myLoop).Value, 1, 3) = "AVS" Then
        newValue = MID(ws.Range("A" & myLoop).Value, 48, 4)
        ws1.Range("A" & RowCounter).Value = newValue
        RowCounter = RowCounter + 1
    End If

Next