Trying to loop IF(MID function in worksheet

Question

I have a sheet called Data that I copy and paste data from a fixed width .txt file. It's about 100,000+ rows of data that I need to loop through every row and pull data from and if it matches the criteria it shows the results on a sheet called AVS. I'm sure I'm missing something simple but for the life of me it will only give me the result from the first line only then stop.

Here's what I have so far:

Sub AVSRev()
    Dim ws As Worksheet, thisRng As Range, ws1 As Worksheet
    Dim lastrow As Long

    Set ws1 = ThisWorkbook.Sheets("Data")
    Set ws = ThisWorkbook.Sheets("AVS")
    Set thisRng = ws.Range("A1")

    Application.ScreenUpdating = False

    With ws
        lastrow = .Range("A" & .Rows.Count).End(xlUp).row

        If Mid(ws1.Range("A1:A" & lastrow).Value, 1, 3) = "AVS" Then
        thisRng = Mid(ws1.Range("A1:A" & lastrow).Text, 48, 4)

        End If

        On Error Resume Next
        Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
        Application.ScreenUpdating = True
    End With
End Sub

After a couple days of messing with this I've rewritten the code as below. I do not get any errors as I was before but it takes forever and when finished no data is listed.

Option Explicit
Sub test123()

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
Dim AVS As Range
Application.ScreenUpdating = False


Range("A" & lastRow).ClearContents

For myLoop = 1 To lastRow

On Error Resume Next
   AVS = MID(ws.Range("A1:A" & myloop).Value, 1, 3)
   If IsError(AVS.Value) Then
    If Err.Number <> 0 Then
            Err.Clear
            On Error GoTo 0
        End If


Else
   If AVS = "AVS" Then
      'If MID(ws.Range("A1:A" & lastRow).Value, 1, 3) = "AVS" Then
         newValue = MID(ws.Range("A" & myLoop).Value, 48, 4)

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

Next

 Application.ScreenUpdating = True
End Sub

I've also listed below a sample of the data I'm trying to retrieve from on the "Data" sheet. Sample Data

Thank you for all the help!

It's important to give the necessary context of the question in the body or, better yet, in the tags so people can find it and better know what you're asking about. That said, I believe `On Error Resume Next` will be swallowing any errors that might occur. — Lance U. Matthews, Mar 03 '20 at 21:48
Hi Bacon, my apologies it was my first post. I've commented out the On Error Resume Next but still the same result. — DJ Ragsdale, Mar 03 '20 at 21:53
I removed the VB.Net tag and replaced it with VBA. It's important to use the correct tags. — Chris Dunaway, Mar 04 '20 at 15:27
change all your MID references to be this `MID(ws.Range("A1:A" & myLoop).Value, 1, 3)` Otherwise, I don't understand what you are trying to accomplish by this statement `ws1.Range("A1:A" & lastrow).Value` in the context of `Mid`. If you can explain that I can probably help re-write the code to perform faster. — Scott Holtzman, Mar 05 '20 at 17:50
@ScottHoltzman thanks for the reply. I've changed the code so many times on this it seems added unnecessary data. I've removed the code: `If MID(ws.Range("A1:A" & lastRow).Value, 1, 3) = "AVS" ` as it wasn't needed. I've changed all the `lastrow` references to `myloop` as suggested too. it runs faster but still no results. — DJ Ragsdale, Mar 05 '20 at 18:04
@ScottHoltzman Shouldn't that be `MID(ws.Range("A" & myLoop).Value, 1, 3)` (instead of `"A1:A" & myLoop`)? — Gaffi, Mar 05 '20 at 18:15
@DJRagsdale - See @Gaffi's comment. Also change `AVS = MID(ws.Range("A1:A" & myloop).Value, 1, 3)` to `AVS = MID(ws.Range("A" & myloop).Value, 1, 3)`. Also, are you sure you need this: `Range("A" & lastRow).ClearContents`. That may be clearing your data before you even have a chance to check it. — Scott Holtzman, Mar 05 '20 at 18:39
@ScottHoltzman I have `Range("A" & lastRow).ClearContents` because the dataset will change frequently and sometimes it'll be shorter than others. Also, i apparently don't know how to use stackoverflow very well because I was only refreshing the page not realizing i needed to check the inbox for updates. I've updated the code since your last reply but having a different issue now.. — DJ Ragsdale, Mar 05 '20 at 21:39
@DJRagsdale - if your first question was answered, please restore the first question and mark as answered. If you have a new problem now with a new quesiton make a new question. Otherwise, its super duper hard to follow and no one will be that willing to help. Mainly for TL;DR — Scott Holtzman, Mar 06 '20 at 01:50
@scottholtzman My apologies, I guess I'm not familiar enough with the guidelines here. I'll go ahead and do that now. — DJ Ragsdale, Mar 06 '20 at 02:16

score 0 · Accepted Answer · answered Mar 06 '20 at 17:18

Thanks to @ScottHoltman and @Gaffi I managed to get my code to loop with the following:

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

It did raise another issue that I will resolve with another post. thanks.

Trying to loop IF(MID function in worksheet

1 Answers1