none
Loop code through an unknown amount of columns and rows RRS feed

  • Question

  • Hello,

    I have the following code working for a single row and set of data.  It probably isn't the cleanest, but it is doing what I need it to do.

    Sub TACOCompare()

    Dim Limits As Range
    Dim LimitCell As Range
    Dim Results As Range
    Dim ResultCell As Range
    Dim CompoundName As Range
    Dim TempText As String

    Set Results = Range("I7", "CD7") 'Sample result range
    Set Limits = Range("D7", "G7") ' Regulatory Limit range
    Set CompoundName = Range("C7") ' Compound name range

    Dim LowLimit As Integer ' Holds the lowest limit value of the reg limits
    LowLimit = 2000

        For Each LimitCell In Limits
        
        'Loops through Reg Limits to determine the lowest value for comparison
            If LimitCell.Value < LowLimit Then
                LowLimit = LimitCell.Value 'Sets LowLimit to the lowest value in the set
            End If

        Next LimitCell

        'Loops through results and compares them to the lowest Reg limit
        For Each ResultCell In Results
        
            If InStr(1, ResultCell.Text, "<") Then ' Searches for < in cell
                TempText = Right(ResultCell.Text, Len(ResultCell.Text) - 1) 'Removes < from cell for evaluation
                
                If LowLimit < (TempText * 1) Then 'Compares LowLimit to cell value.  Added *1 so that Excel sees TempText as a number
                    ResultCell.Interior.Color = RGB(192, 192, 192) ' Sets cell background to grey
                    ResultCell.HorizontalAlignment = xlRight ' Aligns non-detects right
                End If
                
            Else
                ResultCell.HorizontalAlignment = xlLeft 'Aligns detections left
                
                If ResultCell.Value > LowLimit Then
                    ResultCell.Interior.Color = RGB(255, 105, 180) ' Sets cell color to pink
                    CompoundName.Font.Bold = True ' Bolds the compound name if thre is a detection
                    
                    For Each LimitCell In Limits ' Loops though the reg limits and highlights any cells below the sample result
                        
                        If ResultCell.Value > LimitCell.Value Then 'Compares result to the individual limits
                            LimitCell.Interior.Color = RGB(255, 105, 180) ' Sets limit background color to pink
                        End If
                    
                    Next LimitCell
                End If
            End If
        Next ResultCell

    End Sub

    The Limits range will always be from columns D-G, The results will always start in column I, and the CompoundName will always be in column C.  The issue is I will have an unknown amount of columns after I that I will need to compare to the Limits and I will have an unknown amount of rows that will each need the logic above to be applied on a row by row basis.  Any help would be appreciated.

    Monday, August 13, 2018 2:37 PM

Answers

  • Thanks for your replying. Please test this code:

    Sub TACOCompare5()
    
    Dim Limits As Range
    Dim LimitCell As Range
    Dim Results As Range
    Dim ResultCell As Range
    Dim CompoundName As Range
    Dim TempText As String
    Dim ColumnCount As Integer
    Dim RowCount As Integer
    Dim LowLimit As Integer ' Holds the lowest limit value of the reg limits
    Dim resultRow As Range, limitRow As Range
    
    ColumnCount = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
    'RowCount = ActiveSheet.Cells(ActiveSheet.UsedRange.Rows.Count, 3).End(xlUp).Row
    RowCount = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
    LowLimit = 2000
    
    'Set Results = Range("I7", "CD7") 'Sample result range
    Set Results = Range("I7", Chr(Cells([I65536].End(xlUp).Row, "IV").End(1).Column + 64) & [I65536].End(xlUp).Row) ' Sample result range
    'Set Limits = Range("D7", "G7") ' Regulatory Limit range
    Set Limits = Range("D7", "G" & RowCount) ' Regulatory Limit range
    'Set CompoundName = Range("C7") ' Compound name range
    Set CompoundName = Range("C7", "C" & RowCount) ' Compound name range
    MsgBox RowCount
    
        'Loops through results and compares them to the lowest Reg limit
        For i = 7 To RowCount
            
            Set resultRow = Range("I" & i, Chr(Cells([I65536].End(xlUp).Row, "IV").End(1).Column + 64) & i)
            Set limitRow = Range("D" & i, "G" & i)
            For Each LimitCell In limitRow
        
        'Loops through Reg Limits to determine the lowest value for comparison
            If LimitCell.Value < LowLimit Then
                LowLimit = LimitCell.Value 'Sets LowLimit to the lowest value in the set
            End If
        
        Next LimitCell
        
               For Each ResultCell In resultRow
                    If InStr(1, ResultCell.Text, "<") Then ' Searches for < in cell
                        TempText = Right(ResultCell.Text, Len(ResultCell.Text) - 1) 'Removes < from cell for evaluation
                        
                        If LowLimit < (TempText * 1) Then 'Compares LowLimit to cell value.  Added *1 so that Excel sees TempText as a number
                            ResultCell.Interior.Color = RGB(192, 192, 192) ' Sets cell background to grey
                            ResultCell.HorizontalAlignment = xlRight ' Aligns non-detects right
                        End If
                        
                    Else
                
                      ResultCell.HorizontalAlignment = xlLeft 'Aligns detections left
                    
                        If ResultCell.Value > LowLimit Then
                            ResultCell.Interior.Color = RGB(255, 105, 180) ' Sets cell color to pink
                            CompoundName.Font.Bold = True ' Bolds the compound name if thre is a detection
                            
                            For Each LimitCell In limitRow ' Loops though the reg limits and highlights any cells below the sample result
                                
                                If ResultCell.Value > LimitCell.Value Then 'Compares result to the individual limits
                                    LimitCell.Interior.Color = RGB(255, 105, 180) ' Sets limit background color to pink
                                End If
                            
                            Next LimitCell
                        End If
                End If
                Next ResultCell
                LowLimit = 2000
        Next i
    
    End Sub 
     

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Friday, August 24, 2018 2:33 AM
    Moderator

All replies

  • usually, we get rows count and columns count of a worksheet used range using code like
    ColumnCount = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
    RowCount = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    Tuesday, August 14, 2018 9:49 AM
  • Set Results = Range("I7", Chr(Cells([I65536].End(xlUp).Row, "IV").End(1).Column + 64) & [I65536].End(xlUp).Row) 

    You can change the IV value. Hope it helps you.

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Wednesday, August 15, 2018 6:49 AM
    Moderator
  • Hi Tim,

    Did you resolved your issue? Hope you updates for this.

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, August 17, 2018 2:40 AM
    Moderator
  • I have some blank rows in between rows that are populated so they are being cut off in the RowCount equation above.
    Monday, August 20, 2018 6:43 PM
  • This answer got me pretty close.  The Results range selects all the cells I need it to.  The left and right text align work with my code as is.  The issue I am currently running into is that I need the row to be independent of each other when comparing the Limits against the Results.  I need row 7 results to only compare against row 7 limits.

    Below is the code I currently have.  I commented out some of my previous code in case I needed to go back to it.

    Sub TACOCompare()

    Dim Limits As Range
    Dim LimitCell As Range
    Dim Results As Range
    Dim ResultCell As Range
    Dim CompoundName As Range
    Dim TempText As String
    Dim ColumnCount As Integer
    Dim RowCount As Integer
    Dim LowLimit As Integer ' Holds the lowest limit value of the reg limits

    ColumnCount = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
    'RowCount = ActiveSheet.Cells(ActiveSheet.UsedRange.Rows.Count, 3).End(xlUp).Row
    RowCount = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
    LowLimit = 2000

    'Set Results = Range("I7", "CD7") 'Sample result range
    Set Results = Range("I7", Chr(Cells([I65536].End(xlUp).Row, "IV").End(1).Column + 64) & [I65536].End(xlUp).Row) ' Sample result range
    'Set Limits = Range("D7", "G7") ' Regulatory Limit range
    Set Limits = Range("D7", "G" & RowCount) ' Regulatory Limit range
    'Set CompoundName = Range("C7") ' Compound name range
    Set CompoundName = Range("C7", "C" & RowCount) ' Compound name range


        For Each LimitCell In Limits
        
        'Loops through Reg Limits to determine the lowest value for comparison
            If LimitCell.Value < LowLimit Then
                LowLimit = LimitCell.Value 'Sets LowLimit to the lowest value in the set
            End If

        Next LimitCell

        'Loops through results and compares them to the lowest Reg limit
        For Each ResultCell In Results
        
            If InStr(1, ResultCell.Text, "<") Then ' Searches for < in cell
                TempText = Right(ResultCell.Text, Len(ResultCell.Text) - 1) 'Removes < from cell for evaluation
                
                If LowLimit < (TempText * 1) Then 'Compares LowLimit to cell value.  Added *1 so that Excel sees TempText as a number
                    ResultCell.Interior.Color = RGB(192, 192, 192) ' Sets cell background to grey
                    ResultCell.HorizontalAlignment = xlRight ' Aligns non-detects right
                End If
                
            Else
                ResultCell.HorizontalAlignment = xlLeft 'Aligns detections left
                
                If ResultCell.Value > LowLimit Then
                    ResultCell.Interior.Color = RGB(255, 105, 180) ' Sets cell color to pink
                    CompoundName.Font.Bold = True ' Bolds the compound name if thre is a detection
                    
                    For Each LimitCell In Limits ' Loops though the reg limits and highlights any cells below the sample result
                        
                        If ResultCell.Value > LimitCell.Value Then 'Compares result to the individual limits
                            LimitCell.Interior.Color = RGB(255, 105, 180) ' Sets limit background color to pink
                        End If
                    
                    Next LimitCell
                End If
            End If
        Next ResultCell

    End Sub

    I am pretty sure I need another loop and possibly an additional range to go row by row and update the LowLimit for each row and compare it to the Results range in the same row.  I'm just not sure where it should be added and what might be affected. 

    Just so you know how the code is currently performing, it is taking the lowest value in any row from columns C-G as my LowLimit, comparing all cells in Results to it, and modifying the cells accordingly. 

    Monday, August 20, 2018 7:14 PM
  • I am testing for this. I will get back to you if i found related solution. Thanks for your understanding.

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, August 21, 2018 9:49 AM
    Moderator
  • Please try this code:

    Sub TACOCompare2()
    
    Dim Limits As Range
    Dim LimitCell As Range
    Dim Results As Range
    Dim ResultCell As Range
    Dim CompoundName As Range
    Dim TempText As String
    Dim ColumnCount As Integer
    Dim RowCount As Integer
    Dim LowLimit As Integer ' Holds the lowest limit value of the reg limits
    Dim resultRow As Range, limitRow As Range
    
    ColumnCount = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
    'RowCount = ActiveSheet.Cells(ActiveSheet.UsedRange.Rows.Count, 3).End(xlUp).Row
    RowCount = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
    LowLimit = 2000
    
    'Set Results = Range("I7", "CD7") 'Sample result range
    Set Results = Range("I7", Chr(Cells([I65536].End(xlUp).Row, "IV").End(1).Column + 64) & [I65536].End(xlUp).Row) ' Sample result range
    'Set Limits = Range("D7", "G7") ' Regulatory Limit range
    Set Limits = Range("D7", "G" & RowCount) ' Regulatory Limit range
    'Set CompoundName = Range("C7") ' Compound name range
    Set CompoundName = Range("C7", "C" & RowCount) ' Compound name range
    MsgBox RowCount
    
        For Each LimitCell In Limits
        
        'Loops through Reg Limits to determine the lowest value for comparison
            If LimitCell.Value < LowLimit Then
                LowLimit = LimitCell.Value 'Sets LowLimit to the lowest value in the set
            End If
    
        Next LimitCell
        MsgBox LowLimit
        'Loops through results and compares them to the lowest Reg limit
        For i = 7 To RowCount
            
            Set resultRow = Range("I" & i, Chr(Cells([I65536].End(xlUp).Row, "IV").End(1).Column + 64) & i)
            Set limitRow = Range("D" & i, "G" & i)
               For Each ResultCell In resultRow
                    If InStr(1, ResultCell.Text, "<") Then ' Searches for < in cell
                        TempText = Right(ResultCell.Text, Len(ResultCell.Text) - 1) 'Removes < from cell for evaluation
                        
                        If LowLimit < (TempText * 1) Then 'Compares LowLimit to cell value.  Added *1 so that Excel sees TempText as a number
                            ResultCell.Interior.Color = RGB(192, 192, 192) ' Sets cell background to grey
                            ResultCell.HorizontalAlignment = xlRight ' Aligns non-detects right
                        End If
                        
                    Else
                
                      ResultCell.HorizontalAlignment = xlLeft 'Aligns detections left
                    
                        If ResultCell.Value > LowLimit Then
                            ResultCell.Interior.Color = RGB(255, 105, 180) ' Sets cell color to pink
                            CompoundName.Font.Bold = True ' Bolds the compound name if thre is a detection
                            
                            For Each LimitCell In limitRow ' Loops though the reg limits and highlights any cells below the sample result
                                
                                If ResultCell.Value > LimitCell.Value Then 'Compares result to the individual limits
                                    LimitCell.Interior.Color = RGB(255, 105, 180) ' Sets limit background color to pink
                                End If
                            
                            Next LimitCell
                        End If
                End If
                Next ResultCell
        Next i
    
    End Sub 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Wednesday, August 22, 2018 5:20 AM
    Moderator
  • That gets me a little closer.  I believe the last issue is that the LowLimit is outside of the loop so it is not updated on a row by row basis like it needs to be.  Based on the other loops, I am not sure there is a good spot to insert it for direct comparison.  Would having the LowLimit be an array that could hold the lowest value of the Limit range for each row be a possible solution?
    Thursday, August 23, 2018 3:51 PM
  • Thanks for your replying. Please test this code:

    Sub TACOCompare5()
    
    Dim Limits As Range
    Dim LimitCell As Range
    Dim Results As Range
    Dim ResultCell As Range
    Dim CompoundName As Range
    Dim TempText As String
    Dim ColumnCount As Integer
    Dim RowCount As Integer
    Dim LowLimit As Integer ' Holds the lowest limit value of the reg limits
    Dim resultRow As Range, limitRow As Range
    
    ColumnCount = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
    'RowCount = ActiveSheet.Cells(ActiveSheet.UsedRange.Rows.Count, 3).End(xlUp).Row
    RowCount = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
    LowLimit = 2000
    
    'Set Results = Range("I7", "CD7") 'Sample result range
    Set Results = Range("I7", Chr(Cells([I65536].End(xlUp).Row, "IV").End(1).Column + 64) & [I65536].End(xlUp).Row) ' Sample result range
    'Set Limits = Range("D7", "G7") ' Regulatory Limit range
    Set Limits = Range("D7", "G" & RowCount) ' Regulatory Limit range
    'Set CompoundName = Range("C7") ' Compound name range
    Set CompoundName = Range("C7", "C" & RowCount) ' Compound name range
    MsgBox RowCount
    
        'Loops through results and compares them to the lowest Reg limit
        For i = 7 To RowCount
            
            Set resultRow = Range("I" & i, Chr(Cells([I65536].End(xlUp).Row, "IV").End(1).Column + 64) & i)
            Set limitRow = Range("D" & i, "G" & i)
            For Each LimitCell In limitRow
        
        'Loops through Reg Limits to determine the lowest value for comparison
            If LimitCell.Value < LowLimit Then
                LowLimit = LimitCell.Value 'Sets LowLimit to the lowest value in the set
            End If
        
        Next LimitCell
        
               For Each ResultCell In resultRow
                    If InStr(1, ResultCell.Text, "<") Then ' Searches for < in cell
                        TempText = Right(ResultCell.Text, Len(ResultCell.Text) - 1) 'Removes < from cell for evaluation
                        
                        If LowLimit < (TempText * 1) Then 'Compares LowLimit to cell value.  Added *1 so that Excel sees TempText as a number
                            ResultCell.Interior.Color = RGB(192, 192, 192) ' Sets cell background to grey
                            ResultCell.HorizontalAlignment = xlRight ' Aligns non-detects right
                        End If
                        
                    Else
                
                      ResultCell.HorizontalAlignment = xlLeft 'Aligns detections left
                    
                        If ResultCell.Value > LowLimit Then
                            ResultCell.Interior.Color = RGB(255, 105, 180) ' Sets cell color to pink
                            CompoundName.Font.Bold = True ' Bolds the compound name if thre is a detection
                            
                            For Each LimitCell In limitRow ' Loops though the reg limits and highlights any cells below the sample result
                                
                                If ResultCell.Value > LimitCell.Value Then 'Compares result to the individual limits
                                    LimitCell.Interior.Color = RGB(255, 105, 180) ' Sets limit background color to pink
                                End If
                            
                            Next LimitCell
                        End If
                End If
                Next ResultCell
                LowLimit = 2000
        Next i
    
    End Sub 
     

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Friday, August 24, 2018 2:33 AM
    Moderator
  • That did it.  Thank you for all your help.
    Friday, August 24, 2018 7:52 PM