Super slow VBA code on Intel Core i7 with Max RAM

1. Reduce the number of times VBA reads from or writes to excel.

2. Reduce the number of actions in a loop

eg:

            If c < 77 Then
            
                If ActiveCell.Offset(0, c).Value = 5 Then ActiveCell.Offset(0, c).Value = "Great"
                If ActiveCell.Offset(0, c).Value = 4 Then ActiveCell.Offset(0, c).Value = "Good"
                If ActiveCell.Offset(0, c).Value = 3 Then ActiveCell.Offset(0, c).Value = "OK"
                If ActiveCell.Offset(0, c).Value = 2 Then ActiveCell.Offset(0, c).Value = "Poor"
                If ActiveCell.Offset(0, c).Value = 1 Then ActiveCell.Offset(0, c).Value = "Bad"
            
            End If

In this scenario If your cell contains 1, you go through 5 if statements to return "Bad"

Try This
Enter this line outside your loop

AStatus =   Array("", "Bad", "Poor", "OK","Good", "Great")

Inside your loop enter

ActiveCell.Offset(0, c).Value = AStatus(ActiveCell.Offset(0, c).Value)

3. Use Excel to do what excel does best and VBA to do what it does best.

In your code you do something like this twice:-

        For y = 1 To h_rec_count
        
            teststr = WorksheetFunction.Index(Range("heard_array"), y, 1)
            pos = InStr(h_array_vals, teststr)
            
            If pos > 0 Then
                ActiveCell.Offset(0, WorksheetFunction.Index(Range("heard_array"), y, 2) - 1).Value = teststr
            End If
        
        Next y

If h_rec_count = 1000

Then you are reading 1000 excel cells into VBA one at a time

Looking for some text in each cell

If you find the text you are modifying a cell in excel.

It is a lot faster to use a helper column.

a. Insert a formula in an empty column to do the hard work.
b. Copy Paste your Values from your helper column into your Ammended Column
c. Clear your Helper column.

So three lines of code and no loop as opposed to your 1000 loops and six lines of code.