How can I reduce/compact this repetitive VBA code?

Question

I am trying to learn how to reduce the repetitive nature of my code. I thought I could get away with it for now and learn later, but I've received the "procedure too large" error. I know that I can divide into separate subs then write a sub that calls each one, but I'd rather fix the root of the problem, which I know is unnecessary repetition.

I understand the basics of loops, but I don't understand how to apply it to my code. This may be because I don't understand the advanced logic or because I'm having a hard time spotting the patterns in my code that would help me figure out how to write the loop.

Here are two examples from a part of a very, very long code).

Example 1: Uses COUNTIFS from a workbook (datafile17) to populate values into cells in another workbook/worksheet (Raw17).

Dim datafile17 As Workbook
Set datafile17 = Workbooks("WorkbookName")

'First Set
 Dim EU3 As Range, EU4 As Range, EU5 As Range, EU6 As Range, EU7 As Range, EU8 As Range
    Set EU3 = Raw17.Range("F2")
    Set EU4 = Raw17.Range("F3")
    Set EU5 = Raw17.Range("F4")
    Set EU6 = Raw17.Range("F5")
    Set EU7 = Raw17.Range("F6")
    Set EU8 = Raw17.Range("F7")

    datafile17.Activate
       EU3 = Application.WorksheetFunction.CountIfs(Range("X:X"), "U", Range("C:C"), school, Range("J:J"), 3)
       EU4 = Application.WorksheetFunction.CountIfs(Range("X:X"), "U", Range("C:C"), school, Range("J:J"), 4)
       EU5 = Application.WorksheetFunction.CountIfs(Range("X:X"), "U", Range("C:C"), school, Range("J:J"), 5)
       EU6 = Application.WorksheetFunction.CountIfs(Range("X:X"), "U", Range("C:C"), school, Range("J:J"), 6)
       EU7 = Application.WorksheetFunction.CountIfs(Range("X:X"), "U", Range("C:C"), school, Range("J:J"), 7)
       EU8 = Application.WorksheetFunction.CountIfs(Range("X:X"), "U", Range("C:C"), school, Range("J:J"), 8)

'Second Set
 Dim EAB3 As Range, EAB4 As Range, EAB5 As Range, EAB6 As Range, EAB7 As Range, EAB8 As Range
    Set EAB3 = Raw17.Range("E2")
    Set EAB4 = Raw17.Range("E3")
    Set EAB5 = Raw17.Range("E4")
    Set EAB6 = Raw17.Range("E5")
    Set EAB7 = Raw17.Range("E6")
    Set EAB8 = Raw17.Range("E7")

     datafile17.Activate
       EAB3 = Application.WorksheetFunction.CountIfs(Range("X:X"), "AB", Range("C:C"), school, Range("J:J"), 3)
       EAB4 = Application.WorksheetFunction.CountIfs(Range("X:X"), "AB", Range("C:C"), school, Range("J:J"), 4)
       EAB5 = Application.WorksheetFunction.CountIfs(Range("X:X"), "AB", Range("C:C"), school, Range("J:J"), 5)
       EAB6 = Application.WorksheetFunction.CountIfs(Range("X:X"), "AB", Range("C:C"), school, Range("J:J"), 6)
       EAB7 = Application.WorksheetFunction.CountIfs(Range("X:X"), "AB", Range("C:C"), school, Range("J:J"), 7)
       EAB8 = Application.WorksheetFunction.CountIfs(Range("X:X"), "AB", Range("C:C"), school, Range("J:J"), 8)

'I have 18 more of these sets. Then I repeat the whole thing twice more with datafile18/Raw18 and datafile19/Raw19 respectively.

Example 2: Each chunk cycles through tabs on another workbook(data17), pasting the value on a certain cell in a different workbook/worksheet(Overview).

data17.Activate
'For Column D(4)
Overview.Range("B3") = Application.VLookup(fullschcode, G3.Range("A1:AA150"), 4, False)
Overview.Range("B7") = Application.VLookup(fullschcode, G4.Range("A1:AA150"), 4, False)
Overview.Range("B11") = Application.VLookup(fullschcode, G5.Range("A1:AA150"), 4, False)
Overview.Range("B15") = Application.VLookup(fullschcode, G6.Range("A1:AA150"), 4, False)
Overview.Range("B19") = Application.VLookup(fullschcode, G7.Range("A1:AA150"), 4, False)
Overview.Range("B23") = Application.VLookup(fullschcode, G8.Range("A1:AA150"), 4, False)


'For column J (10)
Overview.Range("E3") = Application.VLookup(fullschcode, G3.Range("A1:AA150"), 10, False)
Overview.Range("E7") = Application.VLookup(fullschcode, G4.Range("A1:AA150"), 10, False)
Overview.Range("E11") = Application.VLookup(fullschcode, G5.Range("A1:AA150"), 10, False)
Overview.Range("E15") = Application.VLookup(fullschcode, G6.Range("A1:AA150"), 10, False)
Overview.Range("E19") = Application.VLookup(fullschcode, G7.Range("A1:AA150"), 10, False)
Overview.Range("E23") = Application.VLookup(fullschcode, G8.Range("A1:AA150"), 10, False)

'This one has way less repetition than my first example, but I still have 8 total sets of this type.

I have found some other answers, including the top answer from this helpful post, but I am still stuck on how I can apply it to my situation. Again, it may be because I don't have a grasp on the advanced logic of loops, but I'm trying.

Other notes:

"fullschcode" and "school" are variables that I assign earlier in the code using an input box depending on the school for which I need data.
I've noticed through forum stalking that many of you may think that using COUNTIFS and VLOOKUP are poor ways to accomplish some tasks. While I'm open to suggestions for improving these functions, my main concern for now is the repetition. I went with COUNTIFS and VLOOKUP functions because I understand them. Someone previously suggested to me that I run all of this data through Access, and while I appreciate the suggestion and want to learn, I have to weigh the learning curve against when I need to have the data available. My department was previously doing all of this by hand using printed pivot tables and manually typing the numbers in. Comparatively, I'm doing okay in trying to make these processes more efficient.

score 3 · Accepted Answer · answered Jul 06 '19 at 19:14

3

Here's one way for your first set:

Given these declarations:

Dim datafile17 As Workbook, datafile17WS As Worksheet
Dim myWB As Workbook, myWS As Worksheet
Dim I As Long

Set datafile17 = Workbooks("workbookName")
Set datafile17WS = datafile17.Worksheets("worksheetname")
Set myWB = ThisWorkbook
Set myWS = myWB.Worksheets("Raw17")

You can reduce your first set to a loop.

'First Set
With datafile17WS
    For I = 2 To 7
        myWS.Cells(I, 6) = Application.WorksheetFunction.CountIfs(.Range("X:X"), "U", .Range("C:C"), school, .Range("J:J"), I + 1)
    Next I
End With

Note that, as has been mentioned numerous times, there is no need for Activate method.

answered Jul 06 '19 at 19:14

Ron Rosenfeld

53,870
7
28
60

I feel stupid and mathematically inferior. It never occurred to me that the value of i is just a number, and can therefore be present as needed to cycle through two sets of integers in Workbooks, even if they have different starting points or patterns. So, then, is there a logical way to cycle through non-consecutive numbers? When I look at other loop examples, I get overwhelmed by the i's, but maybe I'll have a better grasp now. As far as `Activate`, I saw that on a post and thought it applied to me. Is it always preferable to use With instead? – Kat Jul 07 '19 at 22:11
Oh, and a slightly unrelated question that may need to be deleted, but I thought I would try to ask. Why is it that in your example (and most others) you declare both the Workbook and the Worksheet instead of just declaring the worksheet? For example, if the Workbook only has the one sheet, why not just declare the Worksheet? – Kat Jul 07 '19 at 22:15
@Kat Search for the SO article on avoiding the use of `Select` and `Activate`. In my opinion, `Workbook` declaration is optional. But if you might be referring to multiple workbooks, it can be handy. You can certainly define a worksheet as `ThisWorkbook.Worksheets("sheetName")` which includes the workbook reference. Again, if you might be referring to another worksheet (perhaps as project expands), it may be handy to have the workbook declared. – Ron Rosenfeld Jul 08 '19 at 10:46
1

@Kat for non-consecutive numbers, there are many ways depending on the nature of the `non-consecutive` values and exactly what you are trying to do. Look at the `Step` keyword for one use. For totally random values, you can always store them in an array and then step through the array. – Ron Rosenfeld Jul 08 '19 at 10:49

How can I reduce/compact this repetitive VBA code?

1 Answers1