VBA - Use a macro to change value in formulas

[mattstopel]

Hi All,

First time poster so go easy on me....

I have been learning VBA and applying it as i go to improve the function of the workbook.

I have been working on a spreadsheet that records and reports on requests made. The data has been recorded for less than 6 months and is at approx 2000 requests, with each request contains 14 inputs (columns)

This worksheet looks up each type of job which is contained in D3

The below formula is used to pull all jobs based on D3 value.

=IFERROR(INDEX(Entry!$S:$S,LARGE(IF(Entry!$G:$G=$D$3,ROW(Entry!$S:$S)),ROW(1:1))),"")

This pulls the unique ID for that Job so the other fields can be pulled using INDEX Match function.

I have recorded the following macro to drag the formulas down to the appropriate countif so it shows all requests. My issue is that the calculation is slow, taking 24 seconds for the largest category (264 rows of data and 11 columns of index matches) and the data set will only continue to grow.

What I would like to do is have only 25 rows of data visible and two buttons so the user can:
1. show the next 25 requests
2. show the previous 25 requests

My logic is that changing the Row value for the above formula by adding 25 to it will work but do not know how to do this in VBA, or if that is the best option?


This will keep them to one A4 page in which they are currently formatted for.

FYI my calculations for the specified sheet are turned to manual so that it doesn't calculate every time the user selects from the data validation box.

Sub FillDownFullTable()
'
' FillDownFullTable Macro
    Range("O2").Value = Now()




Dim countjobtype As Integer


    Range("A7:L7").Select
    ' Selection.AutoFill Destination:=countjobtype, Type:=xlFillDefault
    
        ' declares variable and counts the number of cells that match the job type
    
        countjobtype = Application.WorksheetFunction.countif(DataEntry.Range("G:G"), Range("d3")) + 6


        'Fill the formula down to based on the number of rows containing data
    Selection.AutoFill Destination:=Range(Cells(7, 1), Cells(countjobtype, 12)), Type:=xlFillDefault
'Cells(1,1) = cell A1, Cells(2,1) = cell A2 etc.


    ActiveSheet.Calculate
    Range("O3").Value = Now()
End Sub

Any advice is greatly appreciated. Or just tell me if I am kidding myself.