Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Simplify long process
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default Simplify long process
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default Simplify long process
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
--
HTH...

Jim Thomlinson


"Greg H." wrote:

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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Simplify long process
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

"Greg H." wrote:

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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Simplify long process
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




"Greg H." wrote:

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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Simplify long process
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 wrote:

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

"Greg H." wrote:

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


--

Dave Peterson
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Simplify long process
Another senior moment! Wasn't even thinking about the variable thing.

"Dave Peterson" wrote:

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 wrote:

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

"Greg H." wrote:

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


--

Dave Peterson

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 947
Default Simplify long process

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


--
HTH
Dana DeLouis



"Greg H." wrote in message
...
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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Wordwrapping a long sentence using F2, how to end the process? jbclem3 Excel Discussion (Misc queries) 5 March 29th 10 02:03 PM
How to count process running time ( process not finished) miao jie Excel Programming 0 January 13th 05 09:23 AM
How to count process running time ( process not finished) miao jie Excel Programming 2 January 12th 05 06:01 AM
how to simplify this process? KiriumF1 Excel Programming 3 December 6th 04 05:25 PM
Simplify Process with Excel 2003 Zraxius New Users to Excel 1 December 5th 04 01:55 PM


All times are GMT +1. The time now is 02:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement