VBA Cleaner Looping

[drcline]

I have a pre-written code that relies first on the user inputting a numeric code in an inputbox which is stored as the variable FACCODE. The FACCODEs are non-sequential. The user then puts in a year and a quarter and the code grabs the data of the corresponding FACCODE from the table of the appropriate year and puts it into an excel report showing Q1,2,3,4 and a sum Q5. The excel file is then automatically saved (file name based on FACCODE) and the code closes the workbook. This can only be done for one FACCODE at a time.

I have 112 FACCODEs to input. I have hardcoded the year and quarter in the code. I currently have:
[opening part of code]
iam = 1
Do Until iam = 113
If iam = 1 Then
m_FacCode = 10204
ElseIf iam = 2 Then
m_FacCode = 10709
ElseIf iam = 3 Then
m_FacCode = 10905
(etc.)
... [normal code here]
iam = iam + 1
Loop

Is there a cleaner way to do this to reduce the line count of a loop, rather then listing out all 112 values in ElseIf statements?

[SamT]

where is the list of 112 faccodes?

Assume the list is in the first column on a sheet and the first faccode is in the second Row. Further assume that we don't know how many faccodes there are


Dim LastRow as Long
Dim r as long

LastRow = Cells(Rows.Count, "A").Row '<-- remember this

For r = 2 to LastRow
m_FACCODE = Cells(1, "A")
'
'Blah blah code here
'
Next r

[drcline]

That's true. I never though of putting the FacCodes in a table and drawing from there. Since the FacCodes may change from year to year, I could probably make this master FAC table sort based on year and only draw the FACCODEs from the year endered by the user. Now to look up how to reference a table and value in Access. This is what I love about programming, there are 5 different ways to do the same thing.

[SamT]

First name the Table "FACCODES16." That way you can refer to it in code by

FACCODETableName = "FACCODES" & Format(Date, "yy")