Simplify long process

[Greg H.]

I have pasted the below code in the hopes that someone could show me how to
simplify all the If statmets into a loop or something. Its the same steps
for each variable but if i need to make a change to the if statments it is a
lot of work to update all of them. Any help would be appreciated. Thanks

Private Sub Check_files()

Dim center1 As Range
Dim center2 As Range
Dim center3 As Range
Dim center4 As Range
Dim center5 As Range
Dim center6 As Range
Dim center7 As Range
Dim center8 As Range
Dim center9 As Range
Dim center10 As Range
Dim center11 As Range

Set center1 = Range("A28")
Set center2 = Range("A29")
Set center3 = Range("A30")
Set center4 = Range("A31")
Set center5 = Range("A32")
Set center6 = Range("A33")
Set center7 = Range("A34")
Set center8 = Range("A35")
Set center9 = Range("A36")
Set center10 = Range("A37")
Set center11 = Range("A38")

If center1 = "PATH DIR" Then
Range("F28").FormulaR1C1 = ""
Else
If Len(Dir(center1)) = 0 Then
Range("F28").FormulaR1C1 = "File Missing"
Else
If DateValue(FileDateTime(center1)) = DateValue(Now()) Then
Range("F28").FormulaR1C1 = "Current Report"
Else
Range("F28").FormulaR1C1 = "Old Report"
End If
End If
End If

If center2 = "PATH DIR" Then
Range("F29").FormulaR1C1 = ""
Else
If Len(Dir(center2)) = 0 Then
Range("F29").FormulaR1C1 = "File Missing"
Else
If DateValue(FileDateTime(center2)) = DateValue(Now()) Then
Range("F29").FormulaR1C1 = "Current Report"
Else
Range("F29").FormulaR1C1 = "Old Report"
End If
End If
End If

If center3 = "PATH DIR" Then
Range("F30").FormulaR1C1 = ""
Else
If Len(Dir(center3)) = 0 Then
Range("F30").FormulaR1C1 = "File Missing"
Else
If DateValue(FileDateTime(center3)) = DateValue(Now()) Then
Range("F30").FormulaR1C1 = "Current Report"
Else
Range("F30").FormulaR1C1 = "Old Report"
End If
End If
End If

If center4 = "PATH DIR" Then
Range("F31").FormulaR1C1 = ""
Else
If Len(Dir(center4)) = 0 Then
Range("F31").FormulaR1C1 = "File Missing"
Else
If DateValue(FileDateTime(center4)) = DateValue(Now()) Then
Range("F31").FormulaR1C1 = "Current Report"
Else
Range("F31").FormulaR1C1 = "Old Report"
End If
End If
End If

If center5 = "PATH DIR" Then
Range("F32").FormulaR1C1 = ""
Else
If Len(Dir(center5)) = 0 Then
Range("F32").FormulaR1C1 = "File Missing"
Else
If DateValue(FileDateTime(center5)) = DateValue(Now()) Then
Range("F32").FormulaR1C1 = "Current Report"
Else
Range("F32").FormulaR1C1 = "Old Report"
End If
End If
End If

If center6 = "PATH DIR" Then
Range("F33").FormulaR1C1 = ""
Else
If Len(Dir(center6)) = 0 Then
Range("F33").FormulaR1C1 = "File Missing"
Else
If DateValue(FileDateTime(center6)) = DateValue(Now()) Then
Range("F33").FormulaR1C1 = "Current Report"
Else
Range("F33").FormulaR1C1 = "Old Report"
End If
End If
End If

If center7 = "PATH DIR" Then
Range("F34").FormulaR1C1 = ""
Else
If Len(Dir(center7)) = 0 Then
Range("F34").FormulaR1C1 = "File Missing"
Else
If DateValue(FileDateTime(center7)) = DateValue(Now()) Then
Range("F34").FormulaR1C1 = "Current Report"
Else
Range("F34").FormulaR1C1 = "Old Report"
End If
End If
End If

If center8 = "PATH DIR" Then
Range("F35").FormulaR1C1 = ""
Else
If Len(Dir(center8)) = 0 Then
Range("F35").FormulaR1C1 = "File Missing"
Else
If DateValue(FileDateTime(center8)) = DateValue(Now()) Then
Range("F35").FormulaR1C1 = "Current Report"
Else
Range("F35").FormulaR1C1 = "Old Report"
End If
End If
End If

If center9 = "PATH DIR" Then
Range("F36").FormulaR1C1 = ""
Else
If Len(Dir(center9)) = 0 Then
Range("F36").FormulaR1C1 = "File Missing"
Else
If DateValue(FileDateTime(center9)) = DateValue(Now()) Then
Range("F36").FormulaR1C1 = "Current Report"
Else
Range("F36").FormulaR1C1 = "Old Report"
End If
End If
End If

If center10 = "PATH DIR" Then
Range("F37").FormulaR1C1 = ""
Else
If Len(Dir(center10)) = 0 Then
Range("F37").FormulaR1C1 = "File Missing"
Else
If DateValue(FileDateTime(center10)) = DateValue(Now()) Then
Range("F37").FormulaR1C1 = "Current Report"
Else
Range("F37").FormulaR1C1 = "Old Report"
End If
End If
End If

If center11 = "PATH DIR" Then
Range("F38").FormulaR1C1 = ""
Else
If Len(Dir(center11)) = 0 Then
Range("F38").FormulaR1C1 = "File Missing"
Else
If DateValue(FileDateTime(center11)) = DateValue(Now()) Then
Range("F38").FormulaR1C1 = "Current Report"
Else
Range("F38").FormulaR1C1 = "Old Report"
End If
End If
End If

End Sub

 

[Jim Thomlinson]

Perhaps something more like this...

Sub MainStuff()
Call OtherStuff(Range("A28"))
Call OtherStuff(Range("A29"))
Call OtherStuff(Range("A30"))
Call OtherStuff(Range("A31"))
Call OtherStuff(Range("A32"))
Call OtherStuff(Range("A33"))
Call OtherStuff(Range("A34"))
Call OtherStuff(Range("A35"))
Call OtherStuff(Range("A36"))
Call OtherStuff(Range("A37"))

End Sub

Sub OtherStuff(ByVal Target As Range)
Dim rng As Range

Set rng = Target.Offset(0, 5)
If Target = "PATH DIR" Then
rng.Value = ""
Else
If Len(Dir(Target)) = 0 Then
rng.Value = "File Missing"
Else
If DateValue(FileDateTime(Target)) = DateValue(Now()) Then
rng.Value = "Current Report"
Else
rng.Value = "Old Report"
End If
End If
End If

End Sub

 

[JLGWhiz]

Here is another way:

Private Sub Check_files()
Dim center1 As Range
Dim center2 As Range
Dim center3 As Range
Dim center4 As Range
Dim center5 As Range
Dim center6 As Range
Dim center7 As Range
Dim center8 As Range
Dim center9 As Range
Dim center10 As Range
Dim center11 As Range

Set center1 = Range("A28")
Set center2 = Range("A29")
Set center3 = Range("A30")
Set center4 = Range("A31")
Set center5 = Range("A32")
Set center6 = Range("A33")
Set center7 = Range("A34")
Set center8 = Range("A35")
Set center9 = Range("A36")
Set center10 = Range("A37")
Set center11 = Range("A38")
For i = 1 To 11
If center & i = "PATH DIR" Then
Range("F" & i + 27) = ""
Else
If Len(Dir(center & i)) = 0 Then
Range("F & i + 27) = "File Missing"
Else
If DateValue(FileDateTime(center & i) = DateValue(Now()) Then
Range("F" & i + 27) = "Current Report"
Else
Range("F" & i + 27) = "Old Report"
End If
End If
End If
Next
End Sub

 

[JLGWhiz]

Noticed a typo, use this one:

Private Sub Check_files()
Dim center1 As Range
Dim center2 As Range
Dim center3 As Range
Dim center4 As Range
Dim center5 As Range
Dim center6 As Range
Dim center7 As Range
Dim center8 As Range
Dim center9 As Range
Dim center10 As Range
Dim center11 As Range

Set center1 = Range("A28")
Set center2 = Range("A29")
Set center3 = Range("A30")
Set center4 = Range("A31")
Set center5 = Range("A32")
Set center6 = Range("A33")
Set center7 = Range("A34")
Set center8 = Range("A35")
Set center9 = Range("A36")
Set center10 = Range("A37")
Set center11 = Range("A38")
For i = 1 To 11
If center & i = "PATH DIR" Then
Range("F" & i + 27) = ""
Else
If Len(Dir(center & i)) = 0 Then
Range("F" & i + 27) = "File Missing"
Else
If DateValue(FileDateTime(center & i) = DateValue(Now()) Then
Range("F" & i + 27) = "Current Report"
Else
Range("F" & i + 27) = "Old Report"
End If
End If
End If
Next
End Sub

 

[Dave Peterson]

You have a couple of typos in your code (missing a double quote and missing a
closing paren). But even worse, VBA doesn't work this way with variables.

Center & i (when i = 3) won't be the center3 variable.

 

[JLGWhiz]

Another senior moment! Wasn't even thinking about the variable thing.

 

[Dana DeLouis]

I don't think I've got my IF's correct, but this should give you some ideas:

Private Sub Check_Files()
Dim x As Range
Dim y As Range

For Each x In [A28:A38].Cells
Set y = x.Offset(0, 5) 'Column F

If x = "PATH DIR" Then
y.ClearContents
ElseIf Len(Dir(x)) = 0 Then
y = "File Missing"
ElseIf DateValue(FileDateTime(x)) = DateValue(Now()) Then
y = "Current Report"
Else
y = "Old Report"
End If
Next x
End Sub