Tedious if statements

H

hallingh

Well-known Member
Joined
Sep 11, 2010
Messages
769
I have a problem that doesn't require difficult, but requires a whole lot of if statements and one while/for loop. I am having trouble getting it to run at all, which leads me to believe the if/end if and do while/loop are oriented incorrectly. There is a crap ton of code here, but I'm not asking to fix the actual code inside each if statement. I just would greatly appreciate some editing on how the whole sub is set up so I know whether I made an error when designing the if statement/while loop combo. Here is the code:

Code: 
Public Sub Thumbs()
Dim r As Long
r = 10

If Sheets("Optics Report").Range("J" & (r - 1)).Value <> "" Then
    
    
    
    
    
    
 Do While (r < 249)
    
    
    
    
    
    
    If Sheets("Optics Report").Range("Q" & (r + 5)).Value <> "" Then
        
    
         If Sheets("Optics Report").Range("T" & (r + 6)).Value <> "" Then
            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Sheets("Optics Report").Range("T" & (r + 6)).Value - Sheets("Optics Report").Range("M" & (r + 6)).Value)
            If Sheets("Optics Report").Range("T" & (r + 6)).Value <= Sheets("Optics Report").Range("M" & (r + 6)).Value Then
                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
            Else
                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
          End If
            
            ElseIf Sheets("Optics Report").Range("T" & (r + 5)).Value <> "" Then
                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r + 6)).Value)
                If Int(Now) <= Sheets("Optics Report").Range("M" & (r + 6)).Value Then
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                Else
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                End If
                    
                    ElseIf Sheets("Optics Report").Range("S" & (r + 6)).Value <> "" Then
                        Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r + 5)).Value)
                        If Int(Now) <= Sheets("Optics Report").Range("M" & (r + 5)).Value Then
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                        Else
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                        End If
                            
                            ElseIf Sheets("Optics Report").Range("S" & (r + 5)).Value <> "" Then
                                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r + 6)).Value)
                                    If Int(Now) <= Sheets("Optics Report").Range("L" & (r + 6)).Value Then
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                    Else
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                    End If
                                        
                                        ElseIf Sheets("Optics Report").Range("R" & (r + 6)).Value <> "" Then
                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r + 5)).Value)
                                            If Int(Now) <= Sheets("Optics Report").Range("L" & (r + 5)).Value Then
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                            Else
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                            End If
                                                
                                                ElseIf Sheets("Optics Report").Range("R" & (r + 5)).Value <> "" Then
                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r + 6)).Value)
                                                    If Int(Now) <= Sheets("Optics Report").Range("K" & (r + 6)).Value Then
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                    Else
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                    End If
                                                        
                                                        ElseIf Sheets("Optics Report").Range("Q" & (r + 6)).Value <> "" Then
                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r + 5)).Value)
                                                            If Int(Now) <= Sheets("Optics Report").Range("K" & (r + 5)).Value Then
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                            Else
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                            End If
                                                                
                                                                ElseIf Sheets("Optics Report").Range("Q" & (r + 5)).Value <> "" Then
                                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r + 6)).Value)
                                                                    If Int(Now) <= Sheets("Optics Report").Range("J" & (r + 6)).Value Then
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                    Else
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                    End If
                                                                    
                                                                        Else
                                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r + 5)).Value)
                                                                            If Int(Now) <= Sheets("Optics Report").Range("J" & (r + 5)).Value Then
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                            Else
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                            End If
    End If
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    ElseIf Sheets("Optics Report").Range("Q" & (r + 3)).Value <> "" Then
    
         If Sheets("Optics Report").Range("T" & (r + 4)).Value <> "" Then
            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Sheets("Optics Report").Range("T" & (r + 4)).Value - Sheets("Optics Report").Range("M" & (r + 4)).Value)
            If Sheets("Optics Report").Range("T" & (r + 4)).Value <= Sheets("Optics Report").Range("M" & (r + 4)).Value Then
                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
            Else
                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
          End If
            
            ElseIf Sheets("Optics Report").Range("T" & (r + 3)).Value <> "" Then
                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r + 4)).Value)
                If Int(Now) <= Sheets("Optics Report").Range("M" & (r + 4)).Value Then
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                Else
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                End If
                    
                    ElseIf Sheets("Optics Report").Range("S" & (r + 4)).Value <> "" Then
                        Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r + 3)).Value)
                        If Int(Now) <= Sheets("Optics Report").Range("M" & (r + 3)).Value Then
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                        Else
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                        End If
                            
                            ElseIf Sheets("Optics Report").Range("S" & (r + 3)).Value <> "" Then
                                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r + 4)).Value)
                                    If Int(Now) <= Sheets("Optics Report").Range("L" & (r + 4)).Value Then
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                    Else
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                    End If
                                        
                                        ElseIf Sheets("Optics Report").Range("R" & (r + 4)).Value <> "" Then
                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r + 3)).Value)
                                            If Int(Now) <= Sheets("Optics Report").Range("L" & (r + 3)).Value Then
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                            Else
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                            End If
                                                
                                                ElseIf Sheets("Optics Report").Range("R" & (r + 3)).Value <> "" Then
                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r + 4)).Value)
                                                    If Int(Now) <= Sheets("Optics Report").Range("K" & (r + 4)).Value Then
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                    Else
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                    End If
                                                        
                                                        ElseIf Sheets("Optics Report").Range("Q" & (r + 4)).Value <> "" Then
                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r + 3)).Value)
                                                            If Int(Now) <= Sheets("Optics Report").Range("K" & (r + 3)).Value Then
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                            Else
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                            End If
                                                                
                                                                ElseIf Sheets("Optics Report").Range("Q" & (r + 3)).Value <> "" Then
                                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r + 4)).Value)
                                                                    If Int(Now) <= Sheets("Optics Report").Range("J" & (r + 4)).Value Then
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                    Else
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                    End If
                                                                    
                                                                        Else
                                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r + 3)).Value)
                                                                            If Int(Now) <= Sheets("Optics Report").Range("J" & (r + 3)).Value Then
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                            Else
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                            End If
    End If
    
    
    
    
    
    
    
    
    
    
    
    ElseIf Sheets("Optics Report").Range("Q" & (r + 1)).Value <> "" Then
    
         If Sheets("Optics Report").Range("T" & (r + 2)).Value <> "" Then
            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Sheets("Optics Report").Range("T" & (r + 2)).Value - Sheets("Optics Report").Range("M" & (r + 2)).Value)
            If Sheets("Optics Report").Range("T" & (r + 2)).Value <= Sheets("Optics Report").Range("M" & (r + 2)).Value Then
                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
            Else
                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
          End If
            
            ElseIf Sheets("Optics Report").Range("T" & (r + 1)).Value <> "" Then
                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r + 2)).Value)
                If Int(Now) <= Sheets("Optics Report").Range("M" & (r + 2)).Value Then
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                Else
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                End If
                    
                    ElseIf Sheets("Optics Report").Range("S" & (r + 2)).Value <> "" Then
                        Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r + 1)).Value)
                        If Int(Now) <= Sheets("Optics Report").Range("M" & (r + 1)).Value Then
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                        Else
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                        End If
                            
                            ElseIf Sheets("Optics Report").Range("S" & (r + 1)).Value <> "" Then
                                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r + 2)).Value)
                                    If Int(Now) <= Sheets("Optics Report").Range("L" & (r + 2)).Value Then
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                    Else
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                    End If
                                        
                                        ElseIf Sheets("Optics Report").Range("R" & (r + 2)).Value <> "" Then
                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r + 1)).Value)
                                            If Int(Now) <= Sheets("Optics Report").Range("L" & (r + 1)).Value Then
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                            Else
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                            End If
                                                
                                                ElseIf Sheets("Optics Report").Range("R" & (r + 1)).Value <> "" Then
                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r + 2)).Value)
                                                    If Int(Now) <= Sheets("Optics Report").Range("K" & (r + 2)).Value Then
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                    Else
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                    End If
                                                        
                                                        ElseIf Sheets("Optics Report").Range("Q" & (r + 2)).Value <> "" Then
                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r + 1)).Value)
                                                            If Int(Now) <= Sheets("Optics Report").Range("K" & (r + 1)).Value Then
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                            Else
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                            End If
                                                                
                                                                ElseIf Sheets("Optics Report").Range("Q" & (r + 1)).Value <> "" Then
                                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r + 2)).Value)
                                                                    If Int(Now) <= Sheets("Optics Report").Range("J" & (r + 2)).Value Then
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                    Else
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                    End If
                                                                    
                                                                        Else
                                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r + 1)).Value)
                                                                            If Int(Now) <= Sheets("Optics Report").Range("J" & (r + 1)).Value Then
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                            Else
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                            End If
    End If
    
    
    
    
    
    
    
    
    Else
    
    If Sheets("Optics Report").Range("T" & r).Value <> "" Then
        Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Sheets("Optics Report").Range("T" & r).Value - Sheets("Optics Report").Range("M" & r).Value)
        If Sheets("Optics Report").Range("T" & r).Value <= Sheets("Optics Report").Range("M" & r).Value Then
            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
        Else
            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
        End If
            
            ElseIf Sheets("Optics Report").Range("T" & (r - 1)).Value <> "" Then
                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & r).Value)
                If Int(Now) <= Sheets("Optics Report").Range("M" & r).Value Then
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                Else
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                End If
                    
                    ElseIf Sheets("Optics Report").Range("S" & r).Value <> "" Then
                        Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r - 1)).Value)
                        If Int(Now) <= Sheets("Optics Report").Range("M" & (r - 1)).Value Then
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                        Else
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                        End If
                            
                            ElseIf Sheets("Optics Report").Range("S" & (r - 1)).Value <> "" Then
                                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & r).Value)
                                    If Int(Now) <= Sheets("Optics Report").Range("L" & r).Value Then
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                    Else
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                    End If
                                        
                                        ElseIf Sheets("Optics Report").Range("R" & r).Value <> "" Then
                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r - 1)).Value)
                                            If Int(Now) <= Sheets("Optics Report").Range("L" & (r - 1)).Value Then
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                            Else
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                            End If
                                                
                                                ElseIf Sheets("Optics Report").Range("R" & (r - 1)).Value <> "" Then
                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & r).Value)
                                                    If Int(Now) <= Sheets("Optics Report").Range("K" & r).Value Then
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                    Else
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                    End If
                                                        
                                                        ElseIf Sheets("Optics Report").Range("Q" & r).Value <> "" Then
                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r - 1)).Value)
                                                            If Int(Now) <= Sheets("Optics Report").Range("K" & (r - 1)).Value Then
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                            Else
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                            End If
                                                                
                                                                ElseIf Sheets("Optics Report").Range("Q" & (r - 1)).Value <> "" Then
                                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & r).Value)
                                                                    If Int(Now) <= Sheets("Optics Report").Range("J" & r).Value Then
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                    Else
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                    End If
                                                                    
                                                                        Else
                                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r - 1)).Value)
                                                                            If Int(Now) <= Sheets("Optics Report").Range("J" & (r - 1)).Value Then
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                            Else
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                            End If
                                                                            
    End If
    
    
    End If
    
    r = r + 8
    Loop
 
End If
End Sub

Like I said, I know this is a ton of code, but it's actually not all that bad. There are three major if statements. One of them is at the very beginning of the sub:

Code: 
If Sheets("Optics Report").Range("J" & (r - 1)).Value <> "" Then

The second is almost immediately after it:

Code: 
If Sheets("Optics Report").Range("Q" & (r + 5)).Value <> "" Then

which is supposed to have two elseif statements and an else. I think this is written correctly, but I could have missed something.

The third if statement:

Code: 
If Sheets("Optics Report").Range("T" & (r + 6)).Value <> "" Then

is inside the second if, and is repeated in both of the elseifs and the else, just with a different value in the cell reference.



I'm sure this is asking a lot to sift through this, but I've been working on this for almost a full week and still haven't gotten it to work. I'm all tapped out of ideas, and would appreciate a new set of eyes to look over it sooo much.

FYI, when I run the code as is it simply does absolutely nothing to the sheet.

Thanks for the looks everyone. Igreatly appreciate the help.

Hank
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
hallingh said:
I have a problem that doesn't require difficult, but requires a whole lot of if statements and one while/for loop. I am having trouble getting it to run at all, which leads me to believe the if/end if and do while/loop are oriented incorrectly. There is a crap ton of code here, but I'm not asking to fix the actual code inside each if statement. I just would greatly appreciate some editing on how the whole sub is set up so I know whether I made an error when designing the if statement/while loop combo. Here is the code:

Code: 
Public Sub Thumbs()
Dim r As Long
r = 10
 
If Sheets("Optics Report").Range("J" & (r - 1)).Value <> "" Then
 
 
 
 
 
 
 Do While (r < 249)
 
 
 
 
 
 
    If Sheets("Optics Report").Range("Q" & (r + 5)).Value <> "" Then
 
 
         If Sheets("Optics Report").Range("T" & (r + 6)).Value <> "" Then
            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Sheets("Optics Report").Range("T" & (r + 6)).Value - Sheets("Optics Report").Range("M" & (r + 6)).Value)
            If Sheets("Optics Report").Range("T" & (r + 6)).Value <= Sheets("Optics Report").Range("M" & (r + 6)).Value Then
                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
            Else
                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
          End If
 
            ElseIf Sheets("Optics Report").Range("T" & (r + 5)).Value <> "" Then
                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r + 6)).Value)
                If Int(Now) <= Sheets("Optics Report").Range("M" & (r + 6)).Value Then
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                Else
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                End If
 
                    ElseIf Sheets("Optics Report").Range("S" & (r + 6)).Value <> "" Then
                        Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r + 5)).Value)
                        If Int(Now) <= Sheets("Optics Report").Range("M" & (r + 5)).Value Then
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                        Else
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                        End If
 
                            ElseIf Sheets("Optics Report").Range("S" & (r + 5)).Value <> "" Then
                                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r + 6)).Value)
                                    If Int(Now) <= Sheets("Optics Report").Range("L" & (r + 6)).Value Then
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                    Else
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                    End If
 
                                        ElseIf Sheets("Optics Report").Range("R" & (r + 6)).Value <> "" Then
                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r + 5)).Value)
                                            If Int(Now) <= Sheets("Optics Report").Range("L" & (r + 5)).Value Then
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                            Else
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                            End If
 
                                                ElseIf Sheets("Optics Report").Range("R" & (r + 5)).Value <> "" Then
                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r + 6)).Value)
                                                    If Int(Now) <= Sheets("Optics Report").Range("K" & (r + 6)).Value Then
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                    Else
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                    End If
 
                                                        ElseIf Sheets("Optics Report").Range("Q" & (r + 6)).Value <> "" Then
                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r + 5)).Value)
                                                            If Int(Now) <= Sheets("Optics Report").Range("K" & (r + 5)).Value Then
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                            Else
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                            End If
 
                                                                ElseIf Sheets("Optics Report").Range("Q" & (r + 5)).Value <> "" Then
                                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r + 6)).Value)
                                                                    If Int(Now) <= Sheets("Optics Report").Range("J" & (r + 6)).Value Then
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                    Else
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                    End If
 
                                                                        Else
                                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r + 5)).Value)
                                                                            If Int(Now) <= Sheets("Optics Report").Range("J" & (r + 5)).Value Then
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                            Else
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                            End If
    End If
 
 
 
 
 
 
 
 
 
 
 
 
 
 
    ElseIf Sheets("Optics Report").Range("Q" & (r + 3)).Value <> "" Then
 
         If Sheets("Optics Report").Range("T" & (r + 4)).Value <> "" Then
            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Sheets("Optics Report").Range("T" & (r + 4)).Value - Sheets("Optics Report").Range("M" & (r + 4)).Value)
            If Sheets("Optics Report").Range("T" & (r + 4)).Value <= Sheets("Optics Report").Range("M" & (r + 4)).Value Then
                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
            Else
                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
          End If
 
            ElseIf Sheets("Optics Report").Range("T" & (r + 3)).Value <> "" Then
                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r + 4)).Value)
                If Int(Now) <= Sheets("Optics Report").Range("M" & (r + 4)).Value Then
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                Else
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                End If
 
                    ElseIf Sheets("Optics Report").Range("S" & (r + 4)).Value <> "" Then
                        Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r + 3)).Value)
                        If Int(Now) <= Sheets("Optics Report").Range("M" & (r + 3)).Value Then
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                        Else
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                        End If
 
                            ElseIf Sheets("Optics Report").Range("S" & (r + 3)).Value <> "" Then
                                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r + 4)).Value)
                                    If Int(Now) <= Sheets("Optics Report").Range("L" & (r + 4)).Value Then
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                    Else
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                    End If
 
                                        ElseIf Sheets("Optics Report").Range("R" & (r + 4)).Value <> "" Then
                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r + 3)).Value)
                                            If Int(Now) <= Sheets("Optics Report").Range("L" & (r + 3)).Value Then
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                            Else
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                            End If
 
                                                ElseIf Sheets("Optics Report").Range("R" & (r + 3)).Value <> "" Then
                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r + 4)).Value)
                                                    If Int(Now) <= Sheets("Optics Report").Range("K" & (r + 4)).Value Then
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                    Else
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                    End If
 
                                                        ElseIf Sheets("Optics Report").Range("Q" & (r + 4)).Value <> "" Then
                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r + 3)).Value)
                                                            If Int(Now) <= Sheets("Optics Report").Range("K" & (r + 3)).Value Then
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                            Else
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                            End If
 
                                                                ElseIf Sheets("Optics Report").Range("Q" & (r + 3)).Value <> "" Then
                                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r + 4)).Value)
                                                                    If Int(Now) <= Sheets("Optics Report").Range("J" & (r + 4)).Value Then
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                    Else
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                    End If
 
                                                                        Else
                                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r + 3)).Value)
                                                                            If Int(Now) <= Sheets("Optics Report").Range("J" & (r + 3)).Value Then
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                            Else
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                            End If
    End If
 
 
 
 
 
 
 
 
 
 
 
    ElseIf Sheets("Optics Report").Range("Q" & (r + 1)).Value <> "" Then
 
         If Sheets("Optics Report").Range("T" & (r + 2)).Value <> "" Then
            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Sheets("Optics Report").Range("T" & (r + 2)).Value - Sheets("Optics Report").Range("M" & (r + 2)).Value)
            If Sheets("Optics Report").Range("T" & (r + 2)).Value <= Sheets("Optics Report").Range("M" & (r + 2)).Value Then
                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
            Else
                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
          End If
 
            ElseIf Sheets("Optics Report").Range("T" & (r + 1)).Value <> "" Then
                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r + 2)).Value)
                If Int(Now) <= Sheets("Optics Report").Range("M" & (r + 2)).Value Then
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                Else
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                End If
 
                    ElseIf Sheets("Optics Report").Range("S" & (r + 2)).Value <> "" Then
                        Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r + 1)).Value)
                        If Int(Now) <= Sheets("Optics Report").Range("M" & (r + 1)).Value Then
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                        Else
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                        End If
 
                            ElseIf Sheets("Optics Report").Range("S" & (r + 1)).Value <> "" Then
                                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r + 2)).Value)
                                    If Int(Now) <= Sheets("Optics Report").Range("L" & (r + 2)).Value Then
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                    Else
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                    End If
 
                                        ElseIf Sheets("Optics Report").Range("R" & (r + 2)).Value <> "" Then
                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r + 1)).Value)
                                            If Int(Now) <= Sheets("Optics Report").Range("L" & (r + 1)).Value Then
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                            Else
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                            End If
 
                                                ElseIf Sheets("Optics Report").Range("R" & (r + 1)).Value <> "" Then
                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r + 2)).Value)
                                                    If Int(Now) <= Sheets("Optics Report").Range("K" & (r + 2)).Value Then
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                    Else
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                    End If
 
                                                        ElseIf Sheets("Optics Report").Range("Q" & (r + 2)).Value <> "" Then
                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r + 1)).Value)
                                                            If Int(Now) <= Sheets("Optics Report").Range("K" & (r + 1)).Value Then
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                            Else
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                            End If
 
                                                                ElseIf Sheets("Optics Report").Range("Q" & (r + 1)).Value <> "" Then
                                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r + 2)).Value)
                                                                    If Int(Now) <= Sheets("Optics Report").Range("J" & (r + 2)).Value Then
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                    Else
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                    End If
 
                                                                        Else
                                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r + 1)).Value)
                                                                            If Int(Now) <= Sheets("Optics Report").Range("J" & (r + 1)).Value Then
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                            Else
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                            End If
    End If
 
 
 
 
 
 
 
 
    Else
 
    If Sheets("Optics Report").Range("T" & r).Value <> "" Then
        Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Sheets("Optics Report").Range("T" & r).Value - Sheets("Optics Report").Range("M" & r).Value)
        If Sheets("Optics Report").Range("T" & r).Value <= Sheets("Optics Report").Range("M" & r).Value Then
            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
        Else
            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
        End If
 
            ElseIf Sheets("Optics Report").Range("T" & (r - 1)).Value <> "" Then
                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & r).Value)
                If Int(Now) <= Sheets("Optics Report").Range("M" & r).Value Then
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                Else
                    Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                End If
 
                    ElseIf Sheets("Optics Report").Range("S" & r).Value <> "" Then
                        Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("M" & (r - 1)).Value)
                        If Int(Now) <= Sheets("Optics Report").Range("M" & (r - 1)).Value Then
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                        Else
                            Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                        End If
 
                            ElseIf Sheets("Optics Report").Range("S" & (r - 1)).Value <> "" Then
                                Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & r).Value)
                                    If Int(Now) <= Sheets("Optics Report").Range("L" & r).Value Then
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                    Else
                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                    End If
 
                                        ElseIf Sheets("Optics Report").Range("R" & r).Value <> "" Then
                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("L" & (r - 1)).Value)
                                            If Int(Now) <= Sheets("Optics Report").Range("L" & (r - 1)).Value Then
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                            Else
                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                            End If
 
                                                ElseIf Sheets("Optics Report").Range("R" & (r - 1)).Value <> "" Then
                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & r).Value)
                                                    If Int(Now) <= Sheets("Optics Report").Range("K" & r).Value Then
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                    Else
                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                    End If
 
                                                        ElseIf Sheets("Optics Report").Range("Q" & r).Value <> "" Then
                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("K" & (r - 1)).Value)
                                                            If Int(Now) <= Sheets("Optics Report").Range("K" & (r - 1)).Value Then
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                            Else
                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                            End If
 
                                                                ElseIf Sheets("Optics Report").Range("Q" & (r - 1)).Value <> "" Then
                                                                    Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & r).Value)
                                                                    If Int(Now) <= Sheets("Optics Report").Range("J" & r).Value Then
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                    Else
                                                                        Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                    End If
 
                                                                        Else
                                                                            Sheets("Optics Report").Range("AD" & (r - 1) & ":AD" & (r + 6)).Value = Abs(Int(Now) - Sheets("Optics Report").Range("J" & (r - 1)).Value)
                                                                            If Int(Now) <= Sheets("Optics Report").Range("J" & (r - 1)).Value Then
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "<"
                                                                            Else
                                                                                Sheets("Optics Report").Range("AC" & (r - 1) & ":AC" & (r + 6)).Value = "="
                                                                            End If
 
    End If
 
 
    End If
 
    r = r + 8
    Loop
 
End If
End Sub

Like I said, I know this is a ton of code, but it's actually not all that bad. There are three major if statements. One of them is at the very beginning of the sub:

Code: 
If Sheets("Optics Report").Range("J" & (r - 1)).Value <> "" Then

The second is almost immediately after it:

Code: 
If Sheets("Optics Report").Range("Q" & (r + 5)).Value <> "" Then

which is supposed to have two elseif statements and an else. I think this is written correctly, but I could have missed something.

The third if statement:

Code: 
If Sheets("Optics Report").Range("T" & (r + 6)).Value <> "" Then

is inside the second if, and is repeated in both of the elseifs and the else, just with a different value in the cell reference.



I'm sure this is asking a lot to sift through this, but I've been working on this for almost a full week and still haven't gotten it to work. I'm all tapped out of ideas, and would appreciate a new set of eyes to look over it sooo much.

FYI, when I run the code as is it simply does absolutely nothing to the sheet.

Thanks for the looks everyone. Igreatly appreciate the help.

Hank
Click to expand...

Ok, this is alot of IFs. A better way would be a SELECT/CASE formula.

e.g

say im testing cell a1 or number values
Code: 
SELECT range("a1")
CASE >1
do this
CASE > 2 
do this
CASE > 3
do this
ELSE
do whatever else
END SELECT
 
Upvote 0
Aha, I knew there must be a beter way to do this. I have been pretty much banging my head on my desk for the last week over all these if statements. I will look into this and put something together as best I can. Thanks for teh idea.

Hank
 
Upvote 0
Could I write it like this?

Code: 
If Range1 <> "" Then
Select Range1
 
Case >1
Do This
 
Case >2
Do This
 
Case >3
Do This
 
Elseif Range2 <> ""
 
Case >1
Do This
 
Case >2
Do This
 
etc.

I need to first find out which range to use, and there are four options. Can I use my If/Elseifs to figure out which range to use first and then use select/case?

Thanks again.

Hank
 
Upvote 0
Can select/case only apply to a single string or vaue? My if statements are going in order to figure out what the first blank cell in a range is, so i have them going in backwards order with this if statement:

Code: 
If Cell8 <> "" Then
Do This
Elseif Cell7 <>"" Then
Do This
Elseif Cell6 <> "" Then
Do This
 
etc.

Is this possible with Select/case? Could I do:

Code: 
Select Range("A1:D2")
Case D2 <> ""
Do This
Case D1 <> ""
Do This
Case C2 <> "" 
Do This etc.

Can Select/Case be applied to this type of problem?

Hank
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
2
Replies
10
Views
162
Alex Blakenburg
A
E
  • Locked
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
Replies
2
Views
95
Fluff
Fluff
montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
134
montecarlo2012
montecarlo2012
I
  • Solved
Textbox on userform not updating when navigating through records
2
Replies
10
Views
519
ipbr21054
I
dreid1011
  • Question Question
Recursive Sub Calls and Stack Overflow
2 3
Replies
21
Views
273
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,224,524
Messages
6,179,310
Members
452,906
Latest member
phanmemchatdakenhupviral

Share this page

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