VBA - Use a macro to change value in formulas

mattstopel

New Member
Joined
Jun 27, 2018
Messages
10
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.

Code:
=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.


Code:
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top